Search Postgresql Archives

Re: Problem with reading data from standby server ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 20.04.2012 22:01, Merlin Moncure wrote:
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


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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux