PostgreSQL Backups on Replicas: Conflict with Recovery

pg_dump: error: Dumping the contents of table "audits" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.

Why This Happens

• Replicas continuously replay changes from the primary database using WAL (Write-Ahead Logs).

• When you run a long query (such as pg_dump) on the replica, it may hold locks while reading large tables.

• If the replica receives WAL updates that need to modify the same data your query is scanning, a conflict occurs.

• Wait for your query to finish (which delays replication), or

• Cancel your query to allow replication to continue.

• Default: 30 seconds.

• If the conflict lasts longer than this delay, PostgreSQL cancels your query.

• If no conflicting updates exist, your query can run as long as it needs, even hours.

How to Avoid This Error

1. Run Backups Against the Primary ✅ (Recommended)

• The primary database does not face recovery conflicts.

• Safest and most reliable option for critical backups.

2. Schedule Backups During Low-Write Hours ⏰

• If you must back up from a replica, run them during periods when the primary has minimal writes (e.g., nighttime or maintenance windows).

• Fewer updates = lower chance of conflict.

3. Increase max_standby_streaming_delay 🔧

• DBAs can increase this value on the replica to allow queries more time before being cancelled:

ALTER SYSTEM SET max_standby_streaming_delay = '5min';
SELECT pg_reload_conf();

• ⚠️ Be cautious: this may cause replication lag if the replica falls behind.

4. Exclude Large/Busy Tables 📌

• If one large table (e.g., audits) causes issues, exclude it:

--exclude-table=public.audits

Best Practice

• Run backups from the primary whenever possible.

• If you must back up from replicas, combine low-write scheduling with careful tuning.