Benjamin Krajmalnik wrote:
> 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?
You need to decide how "stale" you're willing to let the hot
standby get. To preserve an image of the database which can allow
the query to keep running, the standby server might need to pause
replay of transactions. To allow long transactions, you need to
allow it to pause the transaction stream for a long time, but that
means that it's getting out of date for disaster recovery purposes.
It might be worthwhile to keep two standby clusters, one that is
aggressive about applying the latest transactions, and another
which allows long-running queries.
-Kevin