1
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.
FULL AI ANALYSIS
pg_dump fails intermittently with 'could not serialize access' during high-concurrency writes on PostgreSQL 13
Generated Oct 3, 2026, 12:35:03 AM
Analysis 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.
Possible 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%Use of serializable isolation in other transactions or long-running transactions that hold conflicting locks.
60%Suggested fixes
2
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.
3
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.
4
Diagnostic step 4
Consider using 'pg_dump --snapshot' carefully or switching to a tool designed for online logical backups that chunks tables to limit snapshot scope.
5
Diagnostic step 5
Investigate long-running transactions on the server ('SELECT pid, now() - xact_start AS age, query FROM pg_stat_activity WHERE state = ''active'';') and mitigate by optimizing or scheduling them off the backup window. Escalation: If you cannot provision a replica or serialization errors persist despite reduced concurrency, involve DBA for deeper analysis (query patterns, transaction isolation usage) and consider workload re-architecture or managed backup solutions.