Re: Problem with reading data from standby server? - Mailing list pgsql-general

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