Home › Databases

ERROR: canceling statement due to conflict with recovery (PostgreSQL) — how to fix

ERROR:  canceling statement 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.

Quick fix: On the replica, either let queries delay replay longer (max_standby_streaming_delay = 5min in postgresql.conf, then SELECT pg_reload_conf();) or tell the primary to keep the rows the replica needs (hot_standby_feedback = on on the replica). Also shorten long reports or run them on a dedicated replica. On AWS RDS/Aurora set these in the parameter group.

What it means

A hot standby replays the primary's WAL while also serving read queries. Sometimes replay needs to do something that conflicts with a running query, most often applying a VACUUM cleanup on the primary that removes old row versions your query's snapshot can still see. The standby then waits up to max_standby_streaming_delay (default 30s) and, if the query is still running, cancels it with this error (SQLSTATE 40001).

The DETAIL line tells you the kind of conflict:

DETAIL Cause
User query might have needed to see row versions that must be removed VACUUM / HOT pruning on the primary (most common)
User was holding a relation lock for too long ACCESS EXCLUSIVE lock on primary (ALTER TABLE, TRUNCATE, DROP, VACUUM truncation)
User was holding shared buffer pin for too long buffer pin conflict
User was or might have been using tablespace that must be dropped / connected to a database that must be dropped DROP TABLESPACE / DROP DATABASE on primary
User transaction caused buffer deadlock with recovery rare buffer deadlock

Check how often it happens per database on the standby:

SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts;

Fix 1: allow longer replay delay on the standby

On the standby, in postgresql.conf:

max_standby_streaming_delay = 5min      # default 30s; -1 = wait forever
max_standby_archive_delay   = 5min      # same, when replaying from archive
SELECT pg_reload_conf();

Trade-off: while a query blocks replay, the replica falls behind (replication lag). Fine for a reporting replica, bad for one used for failover or read-your-writes traffic.

Fix 2: hot_standby_feedback

On the standby:

hot_standby_feedback = on

The standby then tells the primary the oldest transaction it still needs, so VACUUM on the primary won't remove those rows. This removes most confl_snapshot cancellations.

Trade-off: long queries on the replica now cause table bloat on the primary, because dead rows can't be cleaned up until they finish. Combine with a query timeout on the replica, e.g.:

ALTER ROLE reporting SET statement_timeout = '15min';

It doesn't prevent lock conflicts (confl_lock) from DDL on the primary.

Fix 3: managed databases (RDS, Aurora, Cloud SQL, Azure)

You can't edit postgresql.conf; change the parameters in the replica's parameter group / flags:

Fix 4: make the queries shorter or move them

Fix 5: retry in the application

The cancellation is safe to retry; the HINT says so. Treat SQLSTATE 40001 on replica reads as retryable, with backoff. Don't retry indefinitely for reports that always exceed the delay; fix those with Fix 1–4.

Last updated 2026-11-12.