Queries on a PostgreSQL standby get canceled while replication replays
ERROR: canceling statement due to conflict with recoveryShort answer
A hot standby must replay WAL from the primary. When replay would remove rows or take a lock that your query still needs, the standby waits up to max_standby_streaming_delay, then cancels the query. Raise that delay on the standby, or turn on hot_standby_feedback and accept some table bloat on the primary.
Fix it fast
1Find out which cause you have
On the standby, see how many queries each kind of conflict has canceled. The kind tells you which cause below applies.
SELECT * FROM pg_stat_database_conflicts WHERE datname = current_database();2Match what you see, then jump to its fix
- 1
Reports or exports of a few minutes fail, short queries do notThe standby delay is shorter than your queriesFix - 2
Detail line says the query might have needed row versions that must be removed; confl_snapshot risesVACUUM on the primary removed rows your query still neededFix - 3
Rare cancels remain after raising the delay; both settings are above your longest queryDelay is already generous but a cancel still happens now and thenFix
- 1
What does the error look like?
ERROR: canceling statement due to conflict with recoveryDetail: User query might have needed to see row versions that must be removed.org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery
Cause 1 of 3
The standby delay is shorter than your queries
1Check
Replay waits for a conflicting query only as long as max_standby_streaming_delay (streaming replication) or max_standby_archive_delay (WAL from an archive). The delay counts from when the standby received the WAL, so a query never gets more than that. Check the current values.
SHOW max_standby_streaming_delay;
SHOW max_standby_archive_delay;2Fix
On a standby used for reporting, allow a longer delay. A very long delay can leave the standby behind the primary, so pick a number that fits your longest report. A standby kept for failover should stay short.
-- postgresql.conf on the standby
max_standby_streaming_delay = 15min
-- then reload
SELECT pg_reload_conf();3Verify
Run the long query again on the standby and check the conflict counters stop rising.
SELECT datname, confl_snapshot, confl_lock FROM pg_stat_database_conflicts;Confirmed by postgresql.org
Cause 2 of 3
VACUUM on the primary removed rows your query still needed
1Check
A cleanup conflict happens when the primary vacuums dead rows that an old snapshot on the standby can still see.
SELECT datname, confl_snapshot FROM pg_stat_database_conflicts;2Fix
Set hot_standby_feedback = on on the standby. The primary then keeps the dead rows those queries need. The cost is delayed cleanup on the primary, which can mean bloat.
-- postgresql.conf on the standby
hot_standby_feedback = on
SELECT pg_reload_conf();3Verify
Watch confl_snapshot stay flat during the next long query.
SELECT datname, confl_snapshot FROM pg_stat_database_conflicts;Confirmed by postgresql.org
Cause 3 of 3
Delay is already generous but a cancel still happens now and then
1Check
The delay is not a per-query timeout. It is the total time allowed to apply WAL once received, so one slow query early on leaves later conflicting queries almost no grace. Several overlapping reports are enough to trigger it.
SHOW max_standby_streaming_delay;
SELECT pid, now() - xact_start AS age, left(query, 60) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC;2Fix
Stagger the long reports, or give them a standby of their own. As a last resort, -1 makes replay wait forever, which lets the standby fall behind without limit.
-- postgresql.conf on the report-only standby
max_standby_streaming_delay = -1
SELECT pg_reload_conf();3Verify
Compare replay lag on the primary while reports run.
SELECT application_name, replay_lag FROM pg_stat_replication;Confirmed by postgresql.org
How do I stop it coming back?
- Give reporting standbys and failover standbys different settings; do not share one standby for both jobs.
- Keep transactions on the standby short and close read-only transactions that sit idle.
- Chart pg_stat_database_conflicts so a rising count shows up before users report it.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix