Re: Problem with reading data from standby server ?

On Fri, Apr 20, 2012 at 3:39 AM, Condor <condor(at)stz-bg(dot)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