On Fri, Apr 20, 2012 at 3:39 AM, Condor <condor@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
