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
