PostgreSQL ERROR: Cancellation Due to Recovery Conflict

Question

I get the following error when starting a query on db PostgreSQL in standby mode. The request that causes the error works fine for 1 month, but when you request more than 1 month, an error occurs.

ERROR: canceling statement due to conflict with recovery Detail: User query might have needed to see row versions that must be removed 

Any suggestions for troubleshooting? Thanks

Answer 1

Running queries on a hot standby server is a bit more complicated - it may fail, because during the request some necessary lines can be updated or deleted on the primary one. As the primary one does not know that the request is being run a second time, he thinks that he can clear (vacuum) the old versions of his lines. Then the secondary must repeat this cleanup and is forced to cancel all requests that can use these lines.

Longer requests will be canceled more often.

You can get around this by starting a recurring read transaction on a primary basis that executes a dummy request and then sits idle while the real request runs on the secondary. His presence will prevent the vacuum cleaners of older versions of strings on the primary.

For more information on this topic and other workarounds, see the "Hot Standby - Processing Request Conflicts" section of the documentation.

Answer 2

There is no need to run idle transactions on the master. In postgresql-9.1, the most direct way to solve this problem is to install

 hot_standby_feedback = on 

This will allow the wizard to learn about long queries. From docs :

The first option is to set the hot_standby_feedback parameter, which prevents VACUUM from deleting recently frozen rows and there are no cleaning conflicts.

Why is this not the default? This parameter was added after the initial one and this is the only way that the standby mode can affect the wizard.

Answer 3

As indicated here about hot_standby_feedback = on :

Well, the disadvantage of this is that in standby mode a master can inflate, which can be amazing for some people, too

And here :

What is the max_standby_streaming_delay setting? I would be better off by default - -1 than default hot_standby_feedback. So that you are in standby only affect standby

So I added

 max_standby_streaming_delay = -1 

And no more pg_dump error for us, and master-bloat :)

Answer 4

The table data on the slave slave hot standby server changes when a long query is executed. The solution (PostgreSQL 9.1+) to make sure the table data is not changed is to suspend replication and resume after the query:

 select pg_xlog_replay_pause(); -- suspend select * from foo; -- your query select pg_xlog_replay_resume(); --resume 

Answer 5

No need to touch hot_standby_feedback . As others have noted, setting on can swing the wizard. Imagine that you open a transaction on a slave device and do not close it.

Instead, set max_standby_archive_delay and max_standby_streaming_delay to some normal value:

 # /etc/postgresql/10/main/postgresql.conf on a slave max_standby_archive_delay = 900s max_standby_streaming_delay = 900s 

Thus, requests for slaves with a duration of less than 900 seconds will not be canceled. If your workload requires longer queries, just set these options to a higher value.