Postgresql – ERROR: canceling statement due to conflict with recovery

postgresqlreplication

I got the error below:

ERROR: canceling statement due to conflict with recovery.
Detail: User query might have needed to see row versions that must be removed.;
nested exception in org.postgresql.util.PSQLException:ERROR:canceling statement due to conflict with recovery.
Detail: User query might have needed to see row versions that must be removed.

This error occurs on the standby, when we use long query.
We use PostgreSQL version 12.0. One master and two standby servers.

Could you please give us the solution?

Best Answer

Some old row versions were removed by VACUUM on the primary server, but these row versions could still be required by the query on the standby. So there is a conflict between the running query and the startup process applying changes that come from the primary.

PostgreSQL waits and delays replay for max_standby_streaming_delay, then cancels the query. So to avoid that problem, set max_standby_streaming_delay to -1 on the standby.

You can read more in my blog.

Related Question