Problem with reading data from standby server ?

From: Condor <condor(at)stz-bg(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Problem with reading data from standby server ?
Date: 2012-04-20 08:39:25
Message-ID: 64a7cd1ed8d1056980ab47f69d24c6f7@stz-bg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

Regards,
Condor

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2012-04-20 08:48:55 Re: Problem with reading data from standby server ?
Previous Message Thom Brown 2012-04-20 08:15:53 Unaccent characters