pg_dump fails intermittently with 'could not serialize access' during high-concurrency writes on PostgreSQL 13

Environment

PostgreSQL 13 on Debian 11, primary database handling OLTP writes during business hours with several highly concurrent UPDATE/INSERT workloads from application servers. Backups performed with pg_dump (logical) using multiple parallel jobs at 02:00 daily.

Symptoms

pg_dump process terminates with 'ERROR: could not serialize access due to concurrent update' for one or more transactions, causing the nightly logical backup to fail intermittently only during the busiest import windows. Retrying the dump sometimes succeeds outside the peak load window.

Observations

pg_dump is invoked without --serializable-deferrable in previous runs but pg_dump may use a snapshot transaction for consistency; database logs show many concurrent transactions locking rows in hot tables. No unusual resource exhaustion (CPU, memory, I/O) observed; long-running UPDATEs are present during the attempted dump.

Already attempted

Shifted the pg_dump start time to lower-traffic periods (helpful but not always possible), tried a retry loop wrapper for failed pg_dump jobs, and attempted to run the dump on the primary while briefly pausing a heavy importer — impractical for production windows.

Question

Why is pg_dump failing with serialization errors under heavy concurrent writes, and what are safe, less disruptive strategies to obtain consistent logical backups without impacting production write throughput?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

pg_dump snapshot conflicts with concurrent DML on hot tables, leading to serialization errors when the snapshot cannot be maintained consistently. 95%

Running logical parallel dumps (-j) can exacerbate serialization window length across multiple worker connections. 85%

pg_dump run on primary under heavy write load without read replica available. 75%

Summary

Logical backups using a consistent snapshot can conflict with concurrent transactions under strict isolation. If pg_dump's snapshot spans tables that are being heavily updated, concurrent serializable or repeatable-read transactions can cause serialization conflicts. The safest options are to move backups to a replica, use physical backups (pg_basebackup), or adjust backup isolation/strategy to avoid long-lived snapshots against busy tables.

Suggested Fixes

Diagnostic step 1

Prefer taking logical dumps from a warm read replica: create a standby, run pg_dump there to avoid interfering with primary write workload. This is reversible and reduces contention.

Diagnostic step 2

Use physical backups (pg_basebackup) or filesystem snapshots at the storage layer for large datasets where logical consistency across many tables is expensive; these are consistent by design for base backups and avoid serialization errors.

Diagnostic step 3

If a logical dump must run on primary, schedule it during the lowest-traffic window, reduce parallelism (avoid -j) to shorten the snapshot window, and increase application-side retry logic for transient serialization failures.