Thread: Problem with reading data from standby server?
Hello, when I read binary replication tutorial (http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial) I see on Hot Standby: Hot Standby is identical to Warm Standby, except that the Standby is available to run read-only queries. I setup hot standby server described in tutorial and it's working fine, no problem with that. I have a problem when I try to start a script that should read whole table, error message from php is: PHP Warning: pg_query(): Query failed: ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. in dump.php on line 68 PHP Warning: pg_fetch_array() expects parameter 1 to be resource, boolean given in dump.php on line 69 PHP Warning: pg_query(): Query failed: ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. in dump.php on line 235 PHP Warning: pg_fetch_array() expects parameter 1 to be resource, boolean given in dump.php on line 236 Script actually start sql query: SELECT abs.id, array_accumulate(abs.status) AS status, array_accumulate(abs.service) AS service, stb.model FROM statuses abs, stb_tbl stb WHERE abs.id = stb.ser AND abs.service != 8 AND abs.id LIKE '432%' GROUP BY abs.id, stb.model ORDER BY abs.id array_accumulate aggregate is: CREATE AGGREGATE array_accumulate ( sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}' ); When data is fetched it's saved into a file after some modifications. This script is work a 30-40 min until all data is parsed. Well, I think problem is started when master server send new wal file to slave, but how I can resolve that problem ? Any solutions or some one to can point me how I can resolve this problem ? Regards, Condor
On 20/04/12 09:39, Condor wrote: > Hello, > > when I read binary replication tutorial > (http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial) I see on > Hot Standby: Hot Standby is identical to Warm Standby, except that the > Standby is available to run read-only queries. > I setup hot standby server described in tutorial and it's working fine, > no problem with that. > I have a problem when I try to start a script that > should read whole table, error message from php is: > PHP Warning: pg_query(): Query failed: ERROR: canceling statement due to > conflict with recovery > When data is fetched it's saved into a file after some modifications. > This script is work a 30-40 min until all data is parsed. Well, I think > problem is started when master server send new wal file to slave, but > how I can resolve that problem ? Your master database is being updated all the time and your slave is supposed to be a perfect copy, including deleted/updated rows being no longer visible. So - when you run a query it might need to do one of two things: 1. Pause replication 2. Cancel the query At some point PostgreSQL switches from doing #1 to doing #2 (otherwise you could get so far behind the replica could never catch up). You can control how long before it switches: http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-CONFLICT -- Richard Huxton Archonet Ltd
On Fri, Apr 20, 2012 at 3:39 AM, Condor <condor@stz-bg.com> wrote: > Hello, > > when I read binary replication tutorial > (http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial) I see on Hot > Standby: Hot Standby is identical to Warm Standby, except that the Standby > is available to run read-only queries. > I setup hot standby server described in tutorial and it's working fine, no > problem with that. I have a problem when I try to start a script that should > read whole table, error message from php is: > > PHP Warning: pg_query(): Query failed: ERROR: canceling statement due to > conflict with recovery > DETAIL: User query might have needed to see row versions that must be > removed. in dump.php on line 68 > PHP Warning: pg_fetch_array() expects parameter 1 to be resource, boolean > given in dump.php on line 69 > PHP Warning: pg_query(): Query failed: ERROR: canceling statement due to > conflict with recovery > DETAIL: User query might have needed to see row versions that must be > removed. in dump.php on line 235 > PHP Warning: pg_fetch_array() expects parameter 1 to be resource, boolean > given in dump.php on line 236 > > > Script actually start sql query: > > SELECT abs.id, array_accumulate(abs.status) AS status, > array_accumulate(abs.service) AS service, stb.model FROM statuses abs, > stb_tbl stb WHERE abs.id = stb.ser > AND abs.service != 8 AND abs.id LIKE '432%' GROUP BY abs.id, stb.model > ORDER BY abs.id > > > array_accumulate aggregate is: > > > CREATE AGGREGATE array_accumulate ( > sfunc = array_append, > basetype = anyelement, > stype = anyarray, > initcond = '{}' > ); > > > When data is fetched it's saved into a file after some modifications. This > script is work a 30-40 min until all data is parsed. Well, I think problem > is started when master server send new wal file to slave, but how I can > resolve that problem ? > > > > Any solutions or some one to can point me how I can resolve this problem ? The big trade-off with HS/SR is that you have to choose between the standby being up to date and being able to service long running queries. The timeouts (expressed via max_standby_archive_delay and max_standby_streaming_delay) are the main knob to control which way you want the replica to behave. Basically, if your read only data touched a page that is holding back replication for longer than $timeout, the query gets bounced. If your replica is mainly going to serve big reporting queries and/or dumps, you'll need to significantly relax the timeout or disable it completely -- just understand that this can cause your replica to be significantly behind the master. merlin
On 20.04.2012 22:01, Merlin Moncure wrote: > On Fri, Apr 20, 2012 at 3:39 AM, Condor <condor@stz-bg.com> wrote: >> Hello, >> >> when I read binary replication tutorial >> (http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial) I see >> on Hot >> Standby: Hot Standby is identical to Warm Standby, except that the >> Standby >> is available to run read-only queries. >> I setup hot standby server described in tutorial and it's working >> fine, no >> problem with that. I have a problem when I try to start a script >> that should >> read whole table, error message from php is: >> >> PHP Warning: pg_query(): Query failed: ERROR: canceling statement >> due to >> conflict with recovery >> DETAIL: User query might have needed to see row versions that must >> be >> removed. in dump.php on line 68 >> PHP Warning: pg_fetch_array() expects parameter 1 to be resource, >> boolean >> given in dump.php on line 69 >> PHP Warning: pg_query(): Query failed: ERROR: canceling statement >> due to >> conflict with recovery >> DETAIL: User query might have needed to see row versions that must >> be >> removed. in dump.php on line 235 >> PHP Warning: pg_fetch_array() expects parameter 1 to be resource, >> boolean >> given in dump.php on line 236 >> >> >> Script actually start sql query: >> >> SELECT abs.id, array_accumulate(abs.status) AS status, >> array_accumulate(abs.service) AS service, stb.model FROM statuses >> abs, >> stb_tbl stb WHERE abs.id = stb.ser >> AND abs.service != 8 AND abs.id LIKE '432%' GROUP BY abs.id, >> stb.model >> ORDER BY abs.id >> >> >> array_accumulate aggregate is: >> >> >> CREATE AGGREGATE array_accumulate ( >> sfunc = array_append, >> basetype = anyelement, >> stype = anyarray, >> initcond = '{}' >> ); >> >> >> When data is fetched it's saved into a file after some >> modifications. This >> script is work a 30-40 min until all data is parsed. Well, I think >> problem >> is started when master server send new wal file to slave, but how I >> can >> resolve that problem ? >> >> >> >> Any solutions or some one to can point me how I can resolve this >> problem ? > > The big trade-off with HS/SR is that you have to choose between the > standby being up to date and being able to service long running > queries. The timeouts (expressed via max_standby_archive_delay and > max_standby_streaming_delay) are the main knob to control which way > you want the replica to behave. Basically, if your read only data > touched a page that is holding back replication for longer than > $timeout, the query gets bounced. > > If your replica is mainly going to serve big reporting queries and/or > dumps, you'll need to significantly relax the timeout or disable it > completely -- just understand that this can cause your replica to be > significantly behind the master. > > merlin Mhm, it's seems my logic was wrong: I think when I have replica, my replica server has all the wal files sent from master and also have permanent connection to master server. Slave can check if master is down (something like select ping; reply pong;) and if no response given, slave server should terminate all query's and apply wal files. condor
On Friday, April 20, 2012 10:39:25 AM Condor wrote:
> array_accumulate aggregate is:
>
>
> CREATE AGGREGATE array_accumulate (
> sfunc = array_append,
> basetype = anyelement,
> stype = anyarray,
> initcond = '{}'
> );
Btw, if you replace that by array_agg which is builtin in any version support
HS/SR the whole query might finish faster and thus is less likely to cause
conflicts.
If youre already on 9.1 you might also want to look into hot_standby_feedback
that can also reduce the likelihood of conflicts by informing the master what
is going on on the standby.
Greetings,
Andres
On 21.04.2012 12:59, Andres Freund wrote:
> On Friday, April 20, 2012 10:39:25 AM Condor wrote:
>> array_accumulate aggregate is:
>>
>>
>> CREATE AGGREGATE array_accumulate (
>> sfunc = array_append,
>> basetype = anyelement,
>> stype = anyarray,
>> initcond = '{}'
>> );
> Btw, if you replace that by array_agg which is builtin in any version
> support
> HS/SR the whole query might finish faster and thus is less likely to
> cause
> conflicts.
No, the speed is the same, I just check with explain but yes, I will
switch to use array_agg
>
> If youre already on 9.1 you might also want to look into
> hot_standby_feedback
> that can also reduce the likelihood of conflicts by informing the
> master what
> is going on on the standby.
>
This option hot_standby_feedback look interesting, but when I read what
do more questions coming like:
This can cause database bloat on the primary for some workloads. Well
if I run one query every hour and take
30 min to finish the job, with how much my database on primary will be
increase and when I run vaccumdb on master do this
blob will gone. Well I watch one treat here, one guy with 540 gb
database with huge blob and Im little scared do I
will have the same result. Also if something happened on the master
server and it's going down do I will have all
wal files in archive directory on standby server and did postgres will
apply them auto after finish the query or
I need to do this manually.
> Greetings,
>
> Andres
Greetings,
Condor