Horton

Thread

  1. Problem with reading data from standby server ?

    Condor <condor@stz-bg.com> — 2012-04-20T08:39:25Z

    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

  2. Re: Problem with reading data from standby server ?

    Richard Huxton <dev@archonet.com> — 2012-04-20T08:48:55Z

    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

  3. Re: Problem with reading data from standby server ?

    Merlin Moncure <mmoncure@gmail.com> — 2012-04-20T19:01:52Z

    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

  4. Re: Problem with reading data from standby server ?

    Condor <condor@stz-bg.com> — 2012-04-21T05:28:59Z

    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

  5. Re: Problem with reading data from standby server ?

    Andres Freund <andres@anarazel.de> — 2012-04-21T09:59:07Z

    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

  6. Re: Problem with reading data from standby server ?

    Condor <condor@stz-bg.com> — 2012-04-21T15:38:57Z

    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