Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Queries on a PostgreSQL standby get canceled while replication replays

ErrorERROR: canceling statement due to conflict with recovery

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, pgJDBC 42
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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();
  2. 2Match what you see, then jump to its fix

    1. 1Reports or exports of a few minutes fail, short queries do notThe standby delay is shorter than your queriesFix
    2. 2Detail 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. 3Rare cancels remain after raising the delay; both settings are above your longest queryDelay is already generous but a cancel still happens now and thenFix

What does the error look like?

  • ERROR: canceling statement due to conflict with recovery
  • Detail: 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.