This article should help address error messages such as "PSQLException-ERROR: canceling statement due to conflict with recovery" or "FATAL: terminating connection due to conflict with recovery Detail: User query might have needed to see row versions that must be removed. Hint: In a moment you should be able to reconnect to the database and repeat your command. "
This error does not originate in Data Virtuality, rather it has to do with the PostgreSQL replication configuration. Usually, that means that you are accessing a slave replica which is configured as a Hot Standby replica.
In such configuration, the default behavior of PostgreSQL is that any new changes coming from the master server are killing all other queries which use the same data after waiting a very short amount of time, which is 30 seconds per default.
Here is some further information on the subject:
http://www.postgresql.org/docs/9.0/interactive/hot-standby.html#HOT-STANDBY-CONFLICT
The way to solve this is for older PostgreSQL versions to increase both PostgreSQL parameters discussed here:
http://www.postgresql.org/docs/9.0/interactive/runtime-config-wal.html#RUNTIME-CONFIG-STANDBY
A more elegant way to solve this in the newer versions of PostgreSQL is by enabling the parameter hot_standby_feedback, as described here
https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
If none of the above is possible for some reason, for example, if the PostgreSQL configuration cannot be changed - in many cases it can be helpful to set retry count on the affected replication job to >1 with a delay of several seconds. In this case, the job will retry the operation, and there is a possibility that on one of the retries no conflict with the master server will occur.
Comments
0 comments
Please sign in to leave a comment.