Question concerning replicated server using streaming replication used as a read-only reporting server

[Benjamin Krajmalnik]

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?

[Kevin Grittner]

Post by Benjamin Krajmalnik
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
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

[Benjamin Krajmalnik]

It is ok if I am a little bit behind. What setting do I need to tweak to allow it to get further behind?


-----Original Message-----
From: Kevin Grittner [mailto:***@mail.com]
Sent: Thursday, January 17, 2013 4:32 PM
To: Benjamin Krajmalnik; pgsql-***@postgresql.org
Subject: Re: [ADMIN] Question concerning replicated server using streaming replication used as a read-only reporting server

Post by Benjamin Krajmalnik
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
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

[Kevin Grittner]

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

[Benjamin Krajmalnik]

thx

Sent from my Verizon Wireless 4G LTE smartphone

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