🚀 New: chi (χ) — an open-source autoresearch harness for fleets of LLM coding agents. Read the announcement.
Kuldeep Pisda
Parallel pg_dump on a Standby Could Corrupt the Standby, and the Patch Won't Undo It

Parallel pg_dump on a Standby Could Corrupt the Standby, and the Patch Won't Undo It

PostgreSQL 18.7, 17.12, 16.16, 15.20 and 14.25 fix snapshot export on standbys. Since PostgreSQL 10, pg_dump -j on a replica could write wrong hint bits there. Upgrading stops new damage, but nothing in the update finds or repairs the old damage.

8 min read

Contents

PostgreSQL’s November 12 update releases (18.7, 17.12, 16.16, 15.20 and 14.25) fix a bug in how a hot standby exports snapshots. The release notes describe the consequence plainly: it “is known to be triggered by parallel pg_dump, when run on a standby server. In that context it could corrupt both the dump output and the standby server’s data.”

The second half of that sentence is the part to act on. A bad dump fails a restore test and you take another one. A standby with wrong hint bits keeps serving wrong answers after you install the fix, keeps them across a promotion, and passes the check most teams would reach for first. The announcement’s “Updating” section lists post-update steps for GIN, pg_trgm, TOAST, partitioned-table NOT NULL, bpchar and incremental-backup problems. It has no step for this one.

How pg_dump -j shares one snapshot

A parallel dump uses one leader connection and one connection per worker. The pg_dump docs explain that this needs synchronized snapshots, “introduced in PostgreSQL 9.2 for primary servers and 10 for standbys”, so every worker sees the same data. The leader calls pg_export_snapshot(), which writes the snapshot to a file on the server, and each worker runs SET TRANSACTION SNAPSHOT to load it.

A snapshot is mostly a list of transactions that were still in progress when it was taken. On a primary the top-level XIDs go in an xip array and subtransaction XIDs go in subxip. If any backend has more than 64 subtransactions (PGPROC_MAX_CACHED_SUBXIDS in proc.h), the snapshot is marked suboverflowed, subxip is treated as incomplete, and readers fall back to pg_subtrans to map a subtransaction to its parent.

A standby builds snapshots differently. A comment in GetSnapshotData() in procarray.c says recovery snapshots “store all xids in the subxip array because this is by far the bigger array. We just leave the xip array empty.” The standby marks such a snapshot suboverflowed when it has learned from the WAL that some primary transaction overflowed its subtransaction cache. The primary writes an XLOG_XACT_ASSIGNMENT record for every 64 subtransaction XIDs a transaction is assigned, which is how the standby finds out.

Snapshot export didn’t account for the different layout. The fix commit says export treated the overflow flag as meaning subxip was redundant, “so an importing session could see in-progress transactions as aborted.” For a recovery snapshot, subxip holds every in-progress XID. Dropping it produced a snapshot that, in Peter Geoghegan’s words on pgsql-hackers, “claims no transaction is running.” The patched ExportSnapshot() in snapmgr.c keeps the array when the snapshot came from recovery:

/*
 * Ignore the subxid array if it has overflowed, unless the snapshot was
 * taken during recovery - in that case, top-level XIDs are in subxip as
 * well, and we mustn't lose them.
 */
if (suboverflowed && !snapshot->takenDuringRecovery)
    nsubxids = 0;

The commit says this was an oversight in 6c2003f8a, the June 2017 change that made snapshot export work on standbys. Every release since PostgreSQL 10 had it, and the fix is backpatched through 14.

Why wrong answers became wrong data

If the bug only gave the importing transaction a wrong view, it would only break that dump. Geoghegan’s explanation of why it goes further: HeapTupleSatisfiesMVCC treats an XID that the snapshot says isn’t running, and that pg_xact doesn’t show as committed, as aborted. A transaction that was still running on the primary is, from that worker’s point of view, dead. The worker records that by setting hint bits on the tuple, such as HEAP_XMIN_INVALID, so later readers skip the lookup.

Those hint bits live in the standby’s own copy of the page, and every session on the standby reads them. Geoghegan lists the symptoms he has seen in the wild: “rows reappearing after deletion, rows vanishing after insertion, and duplicate entries in unique indexes.” The transaction later commits on the primary, but the standby’s page already says its row versions are invalid. The damage stays until something replaces the page, such as a full-page image in replayed WAL, which Geoghegan notes “is also likely to mask the problem in practice.”

That masking is also why this took years to pin down. A 2023 bug report described pg_dump runs against a replica whose dumps sporadically failed to restore, with ALTER TABLE ... ADD CONSTRAINT reporting a foreign key whose referenced row was “not present in table”. The fix commit cites that report.

The condition that makes it reachable

You need all of these:

ConditionWhy it matters
pg_dump -j (or any tool using pg_export_snapshot()) connected to a hot standbyOnly snapshots taken during recovery use the all-in-subxip layout
PostgreSQL 10 or later, before this week’s releasesStandby snapshot export arrived in 10
A primary transaction with more than 64 subtransactions that were assigned XIDs, recent enough to be marked overflowed in the standby’s snapshotThe suboverflowed flag is what made export drop the array

The third row is less exotic than it sounds. Every savepoint and every PL/pgSQL block with an EXCEPTION clause is a subtransaction. One that writes gets an XID. A batch job that wraps each row in a savepoint, or a function that loops over rows with an exception handler, crosses 64 quickly. ORMs that turn nested transaction blocks into savepoints do the same. Andrey Borodin described on the list a production case with exactly that mix, lots of savepoints and exported snapshots, where VACUUM failed with “cannot freeze committed xmax”. He was careful to say it was not yet established that snapshot export caused it, “but likely so.”

The fix is in the server’s export code, so the binary that matters is the standby’s. Upgrading the machine that runs pg_dump does nothing.

Detection is the weak part

Borodin was investigating foreign-key violations after a failover and found tuples with HEAP_XMIN_INVALID set although their xmin had committed. “pg_visibility reported them, but verify_heapam() did not.” He posted amcheck patches to detect both the xmin and xmax variants and asked for them to be backpatched with this release. The 18.7 release notes contain no amcheck change, so a clean verify_heapam() run on 18.7 tells you nothing about this bug.

Borodin doesn’t say which pg_visibility function found the tuples. The documented candidate is pg_check_visible(relation), which “returns the TIDs of non-all-visible tuples stored in pages marked all-visible in the visibility map.” It scans the whole relation. By that definition it can only flag tuples on pages that the visibility map marks all-visible, so an empty result is not proof of a clean table.

He also wrote, “I am still thinking about how best to repair the existing damage.” No upstream repair procedure exists yet.

What to do about it

First, upgrade standbys before the next scheduled parallel dump. Until then, run pg_dump without -j against replicas, or point parallel dumps at the primary. A single-session dump doesn’t export a snapshot, and a primary snapshot never had the recovery layout.

Second, find out whether any standby has ever served pg_dump -j. Check backup scripts, cron entries and managed backup tooling for parallel dumps aimed at replica hostnames. If none did, you’re done with this bug.

If one did and it’s still a standby, rebuild it from the primary with a fresh base backup. The wrong hint bits were written locally on that standby, and a standby doesn’t send changes upstream, so the primary’s pages don’t have them. Rebuilding is cheaper than trying to prove a replica clean with tools that can’t fully see the damage. The same reasoning applies to any replica or base backup cloned from the affected one, because pg_basebackup copies pages as they are on disk.

If an affected standby was promoted at some point, the damage may now be on your primary, and there’s no rebuild source. Run pg_check_visible() on the tables that matter, check foreign keys and unique constraints with queries, and watch Borodin’s amcheck thread.

Treat old parallel dumps taken from a standby as unverified until a full restore succeeds. A restore that recreates foreign keys and unique indexes is a decent check on the dump-output half of this bug, because that’s how the 2023 report found it.

Other fixes in this release that don’t repair the past

The same release has another fix of this kind. The incremental backup visibility map fix (PostgreSQL 17 and up) “does not correct existing WAL summaries”, so incremental backups taken after the update can still be affected until you take a new full backup. The base backup fix for truncated or zero-padded copies of files like postgresql.auto.conf and pg_logical/replorigin_checkpoint was backpatched to 15 only. 14.25 is the final PostgreSQL 14 release, so it won’t get that fix.

Sources