Fix: terminating connection due to conflict with recovery

This error means a PostgreSQL hot standby cancelled your connection because replaying write ahead log from the primary conflicted with a query you were running on the replica. A standby exists to apply the primary’s changes continuously. When that replay needs to clean up or overwrite rows that a running read query still depends on, the two are in conflict. The standby will not let a query stall replication forever, so once a configurable delay passes it cancels the query and, for some conflict types, terminates the connection.

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.

What this error means

On a hot standby, WAL replay and read queries compete for the same rows. If the primary vacuums away a dead row, the WAL record for that cleanup arrives at the standby, but a long read query there may still need the old version for its snapshot. Replay cannot proceed without invalidating the query. PostgreSQL resolves this with max_standby_streaming_delay: replay waits up to that long, and if the query has not finished, the query is cancelled so replay can continue. Similar conflicts arise from lock contention, dropped relations, and buffer pin waits. The reconnect hint is accurate: the connection is gone, but the database is healthy and a retry usually succeeds.

Common causes

• Long-running analytic or reporting queries on the replica while the primary is actively vacuuming.
• Heavy write and vacuum activity on the primary generating conflict inducing WAL faster than the replica can accommodate.
• max_standby_streaming_delay left low, so queries are cancelled quickly under any replay pressure.
• hot_standby_feedback off, so the primary vacuums rows without regard for what replica queries still need.
• A relation altered or dropped on the primary while a replica query references it.
• Batch jobs pointed at the replica that run far longer than the standby delay allows.

How to fix it

STABILIZE first. If a critical read is being cancelled right now, get it to complete, then choose the durable tradeoff.

1. STABILIZE: retry the query, or temporarily raise the delay so the current read can finish before replay cancels it. This is set on the standby and can be reloaded.

ALTER SYSTEM SET max_standby_streaming_delay = '300s';
SELECT pg_reload_conf();

Understand the cost: while a query holds replay off, the replica falls behind the primary by up to that delay.

2. DIAGNOSE: confirm the conflicts and their type from the standby’s statistics.

SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock, confl_tablespace
FROM pg_stat_database_conflicts
ORDER BY confl_snapshot DESC;

A high confl_snapshot count points at the classic vacuum versus long read conflict.

3. FIX with feedback when replica reads matter more than primary bloat. Turn on hot_standby_feedback so the primary holds off vacuuming rows the replica still needs. Set it on the standby.

ALTER SYSTEM SET hot_standby_feedback = on;
SELECT pg_reload_conf();

The tradeoff: dead rows linger on the primary, so watch for table and index bloat if replica queries run long.

4. FIX with delay when some lag is acceptable. Keep max_standby_streaming_delay high enough to cover your longest legitimate replica query, accepting the replication lag it introduces.

5. FIX by relocation when neither tradeoff is acceptable. Run the heavy analytics on the primary, where recovery conflicts cannot occur, or on a dedicated replica tuned specifically for long reads with feedback on and a generous delay.

6. Verify the conflict counters stop climbing after your change.

SELECT datname, confl_snapshot FROM pg_stat_database_conflicts;

How to prevent it

• Decide deliberately whether each replica is for near real time reads (low delay, feedback off) or long analytics (feedback on, higher delay), and configure it for that role.
• Keep reporting and batch jobs on a replica sized and tuned for long queries, not on the failover standby.
• Monitor replication lag whenever you raise max_standby_streaming_delay, so lag does not silently grow.
• Watch primary bloat when hot_standby_feedback is on, and keep autovacuum aggressive enough to recover once queries finish.
• Bound the runtime of replica queries with statement_timeout so a runaway report does not force endless replay delay.

When to call a senior engineer

Call for help when you keep trading one problem for another: cancellations gone but the primary is bloating, or lag under control but reads still fail. A senior engineer can measure your conflict types, decide where each workload belongs, and tune feedback and delay to your real query lengths. Erzon can architect a replica topology where analytics run reliably without putting your failover standby or your primary’s health at risk.