Thread: Question concerning replicated server using streaming replication used as a read-only reporting server
Question concerning replicated server using streaming replication used as a read-only reporting server
I have 2 servers which are using streaming replication (pg 9.0.4).
The secondary server is there primarily as a disaster recovery server, but we are also using it for reporting, so as not to place undue load on the primary server.
As I review the logs on the secondary server, I frequently see the following:
2013-01-17 06:05:47 MST [local]ERROR: canceling statement due to conflict with recovery
2013-01-17 06:05:47 MST [local]DETAIL: User query might have needed to see row versions that must be removed.
2013-01-17 06:05:47 MST [local]STATEMENT: Select statement goes here
2013-01-17 06:05:47 MST [local]FATAL: terminating connection due to conflict with recovery
2013-01-17 06:05:47 MST [local]DETAIL: User query might have needed to see row versions that must be removed.
2013-01-17 06:05:47 MST [local]HINT: In a moment you should be able to reconnect to the database and repeat your command.
Is there anything that can be done to mitigate this situation?
Re: Question concerning replicated server using streaming replication used as a read-only reporting server
thx Sent from my Verizon Wireless 4G LTE smartphone Kevin Grittner <kgrittn@mail.com> wrote: Benjamin Krajmalnik wrote: > It is ok if I am a little bit behind. What setting do > I need to tweak to allow it to get further behind? The relevant settings are described here: http://www.postgresql.org/docs/9.0/interactive/runtime-config-wal.html#RUNTIME-CONFIG-REPLICATION http://www.postgresql.org/docs/9.0/interactive/runtime-config-wal.html#RUNTIME-CONFIG-STANDBY The ones that you might want to look at are: vacuum_defer_cleanup_age max_standby_archive_delay max_standby_streaming_delay Leaving some gaps for "catch up time" between long-running requests can help prevent cancelations, since the lag can otherwise accumulate. -Kevin