Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

PostgreSQL "canceling statement due to conflict with recovery": fix standby query cancellations

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

ERROR: canceling statement due to conflict with recovery is raised on a hot standby when a read query needs something the primary's write-ahead log (WAL) is about to remove, and the query is still running after replay has waited the allowed delay for it. The standby cancels the query so replay can continue. The fix is to find which conflict type is occurring, then choose between retrying in the application, a longer delay on the standby, or hot_standby_feedback, each of which costs something.

What the error means

A standby replays WAL records generated by the primary. The primary does not know what queries are running on the standby, so some of its actions conflict with them. The PostgreSQL 17 hot standby page lists the conflict cases: Access Exclusive locks taken on the primary (explicit LOCK and various DDL), dropped tablespaces, dropped databases, and vacuum cleanup records that clash with standby snapshots that can still “see” any of the rows to be removed or with queries accessing the target page.

On the standby "there is no choice: the WAL-logged action already occurred on the primary so the standby must not fail to apply it." A mechanism therefore cancels conflicting queries, after a delay set by two parameters:

  • max_standby_streaming_delay applies when WAL arrives by streaming replication.
  • max_standby_archive_delay applies when WAL is read from an archive, for example while catching up.

Both default to 30 seconds, and the replication settings page says a value of -1 "allows the standby to wait forever". The hot standby page stresses that the delay is compared to the time since the WAL data was received by the standby, so "the grace period allowed to any one query on the standby is never more than the delay parameter, and could be considerably less if the standby has already fallen behind".

The PostgreSQL source code (tag REL_17_12, postgres.c), not a documentation page, shows the message with a DETAIL line that names the cause, for example User query might have needed to see row versions that must be removed., User was holding shared buffer pin for too long., User was holding a relation lock for too long. or User transaction caused buffer deadlock with recovery. The SQLSTATE is 40001, serialization_failure in the error code appendix. In the documentation's words, “The most common reason for conflict between standby queries and WAL replay is “early cleanup”.”

Two related cases end differently. Replaying DROP DATABASE terminates the whole conflicting session, and "if the conflict is over a lock held by an idle transaction, the conflicting session is terminated". The page adds "(this behavior might change in the future)".

Likely causes

Cause What you see How to confirm
Early cleanup (snapshot conflict) DETAIL: User query might have needed to see row versions that must be removed. confl_snapshot rises in pg_stat_database_conflicts
Lock conflict from DDL on the primary DETAIL: User was holding a relation lock for too long. confl_lock rises; the primary ran ALTER, DROP, TRUNCATE or LOCK at that time
Buffer pin conflict DETAIL: User was holding shared buffer pin for too long. confl_bufferpin rises
Tablespace or database dropped A cancelled query using temporary files, or a disconnected session confl_tablespace rises, or the session was terminated
Reports cancelled while a conflicting WAL record waits Cancellations during long reports on heavily updated tables Replay waited the delay (default 30 seconds) for the query and the query was still running; the delay limits how long replay waits, not how long a query may run

Prove the cause before changing anything

All of these only read. Run them on the standby unless stated.

1. Confirm it is a standby.

SELECT pg_is_in_recovery();

The documentation says the function "Returns true if recovery is still in progress."

2. Count cancellations by type.

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

The documentation describes the view as "database-wide statistics about query cancels due to conflict with recovery on standby servers". The confl_snapshot column counts queries "canceled due to old snapshots" and confl_bufferpin those "canceled due to pinned buffers". Take the numbers twice, some time apart, and subtract: the difference is the cancellations in between.

3. Read the current settings.

SHOW max_standby_streaming_delay;
SHOW max_standby_archive_delay;
SHOW hot_standby_feedback;

Defaults are 30 seconds, 30 seconds and off.

4. Compare query length to the delay. From the application or the error log, note how long the cancelled queries ran. The delay is not a query run limit: the replication settings page says it "is not the same as the maximum length of time a query can run before cancellation". A long query on a table that is heavily updated on the primary is the classic case. The hot standby page says "tables that are regularly and heavily updated on the primary server will quickly cause cancellation of longer running queries on the standby."

5. Log conflict waits. The log_recovery_conflict_waits parameter, off by default, logs when the startup process waits longer than deadlock_timeout for conflicts.

Fix

Retry in the application

The hot standby page says "Canceled queries may be retried immediately (after beginning a new transaction, of course)" and that a retried query "may well succeed". For short reports this is the lowest-risk change and does not touch the servers.

Lengthen the delay on the standby

The page says that for a standby "primarily for high availability", set the delay "relatively short", and for one meant for long-running queries "a high or even infinite delay value may be preferable". The cost: "a long-running query could cause other sessions on the standby server to not see recent changes on the primary, if it delays application of WAL records."

First print the path of the configuration file the standby is using, and the current value. The documentation describes config_file as the "main server configuration file (customarily called postgresql.conf)":

SHOW config_file;
SHOW max_standby_streaming_delay;

Then save that file. The path below is only an example and will not exist on every installation; use the path that SHOW config_file; printed:

cp -p /var/lib/postgresql/17/main/postgresql.conf /var/lib/postgresql/17/main/postgresql.conf.2026-11-22

Risk: the next edit makes the standby wait longer before cancelling queries, so the standby can fall further behind the primary while a long query runs.

Edit max_standby_streaming_delay in the standby's postgresql.conf (for example max_standby_streaming_delay = 5min), then run the reload:

SELECT pg_reload_conf();

Run SHOW max_standby_streaming_delay; again. The documentation says settings in postgresql.auto.conf, which holds values set with ALTER SYSTEM, "override those in postgresql.conf", so if it still returns the old value, the setting is overridden in postgresql.auto.conf.

The documentation says the file "is reread whenever the main server process receives a SIGHUP signal", sent by pg_ctl reload or pg_reload_conf().

To undo it: restore the saved file under its original name and reload. If you also made the hot_standby_feedback change below, undo that one first, because this saved copy was taken before it.

ls -l /var/lib/postgresql/17/main/postgresql.conf.2026-11-22 && cp -p /var/lib/postgresql/17/main/postgresql.conf.2026-11-22 /var/lib/postgresql/17/main/postgresql.conf
SELECT pg_reload_conf();

Turn on hot_standby_feedback

The documentation lists this as the first remedy: it "prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur." The cost is on the primary: it "will delay cleanup of dead rows on the primary, which may result in undesirable table bloat." The setting only reduces cancellations from cleanup records, and the parameter page says it "can cause database bloat on the primary for some workloads."

First print the current value:

SHOW hot_standby_feedback;

Then save the standby's configuration file under a second name (use the path that SHOW config_file; printed; the path below is only an example):

cp -p /var/lib/postgresql/17/main/postgresql.conf /var/lib/postgresql/17/main/postgresql.conf.2026-11-22-feedback

Risk: the next edit lets a long query on the standby hold back dead-row cleanup on the primary, so tables and indexes on the primary can grow. It does not prevent lock conflicts caused by DDL on the primary.

Edit hot_standby_feedback = on in the standby's postgresql.conf, then reload:

SELECT pg_reload_conf();

To back out: restore the saved file under its original name, reload, and run SHOW hot_standby_feedback; to confirm it matches what you printed.

ls -l /var/lib/postgresql/17/main/postgresql.conf.2026-11-22-feedback && cp -p /var/lib/postgresql/17/main/postgresql.conf.2026-11-22-feedback /var/lib/postgresql/17/main/postgresql.conf
SELECT pg_reload_conf();

Avoid the conflict at its source

For lock conflicts, run DDL on the primary outside the standby's reporting window. A DROP DATABASE replay disconnects sessions immediately, whatever max_standby_streaming_delay is set to.

Example (illustrative): rep01 is a reporting standby. A 95-second report on a table the primary updates all day fails with canceling statement due to conflict with recovery and the DETAIL about row versions. pg_stat_database_conflicts shows confl_snapshot at 12 in the morning and 19 in the afternoon, so 19 minus 12 equals 7 cancellations. The delay setting is the default 30 seconds. The report runs longer than that, so it can be cancelled when a cleanup record for rows it needs is replayed and replay has already waited 30 seconds for it. The team saves the standby's file, sets max_standby_streaming_delay = 5min (300 seconds), reloads, and accepts that replay on rep01 can lag by up to the length of a long report. The report completes. They leave hot_standby_feedback off because the primary's table is large and they do not want it held back.

Confirm the fix

  1. The error no longer appears for the same query in the application log.
  2. confl_snapshot and the other counters stop rising between two readings.
  3. SHOW returns the values you intended.
  4. Replication lag on the standby stays within what the business accepts.

What to watch afterwards

  • Take pg_stat_database_conflicts readings regularly, and alert on a sustained rise.
  • Watch lag if you raised the delay, and primary table size if you enabled feedback.
  • Keep the same max_connections and related settings on standbys that the primary has; the page recommends values on standbys equal to or greater than the primary's.

Risks and limits

  • These commands have not been run or lab-tested.
  • The two delay parameters have no effect on the primary. The page says they "have no effect if set on the primary."
  • hot_standby_feedback does not remove every conflict type. The replication settings page says it "can be used to eliminate query cancels caused by cleanup records", which leaves lock conflicts from DDL on the primary. On the primary-side cost, the hot standby page says "the cleanup situation will be no worse than if the standby queries were running directly on the primary server".
  • The configuration file path shown is an example; the path differs by installation.

Sources