pg_dump Is Already Consistent—When Is --snapshot Needed?

A normal pg_dump already produces an internally consistent view of one PostgreSQL database. Passing --snapshot does not make that single dump “more consistent.” The option matters when pg_dump must share one exact cut line with another independent session or export process.

A reproduced PostgreSQL 17.11 lab made that boundary visible. Three rows existed when one read-only transaction exported a snapshot. A fourth row then committed. A separate snapshot-bound query and a pg_dump --snapshot archive both retained the original three-row digest, while a later ordinary dump restored all four rows. After the exporter ended, PostgreSQL rejected the same snapshot identifier with exit 3.

Designed for a developer or database operator with shell access, this explanation uses a disposable PostgreSQL instance. The commands create two uniquely named, comment-marked databases, prove the archives through clean restores, and refuse to remove any database whose ownership marker does not match.

Choose the Smallest Snapshot Mechanism

PostgreSQL’s current SQL dump documentation states that pg_dump output is internally consistent: it represents a snapshot from the time the dump began even while other work continues. That guarantee answers the common single-dump case.

Current snapshot synchronization documentation defines the narrower problem. Two sessions that begin independently can straddle a third transaction’s commit. Exporting one transaction snapshot lets both sessions see the same pre-existing data while the exporter remains open.

An exported snapshot controls what committed rows are visible. It does not serialize decisions made by sessions that subsequently write. The PostgreSQL write-skew lab shows why stable visibility and serializable application behavior are different guarantees.

An exported identifier is not a saved snapshot file. PostgreSQL keeps it importable only until the exporting transaction ends. Treat the open exporter as part of the operation, not as a setup step that can exit early.

Create One Marker-Owned Baseline

Run this lab only on a disposable PostgreSQL server as an account allowed to create databases. The reproduced environment used Debian 13 and the local postgres operating-system account. Keep all commands in one root shell except where the exporter terminal is called out.

Before creating anything, the preflight records the server and client versions and stops if either lab database already exists. It never chooses a dynamic production name.

set -euo pipefail
umask 077

SOURCE_DB='voxfor_snapshot_lab_182'
RESTORE_DB='voxfor_snapshot_restore_182'
MARKER='VOXFOR_POSTGRES_SNAPSHOT_LAB_182'
LAB_DIR="$(mktemp -d /tmp/voxfor-pgsnap-182.XXXXXX)"

pg() {
  runuser -u postgres -- psql -X -v ON_ERROR_STOP=1 "$@"
}

PREEXISTING="$(
  pg -d postgres -Atc \
    "SELECT string_agg(datname, ',')
       FROM pg_database
      WHERE datname IN ('$SOURCE_DB', '$RESTORE_DB');"
)"
test -z "$PREEXISTING"

SERVER_VERSION="$(pg -d postgres -Atc 'SHOW server_version;')"
CLIENT_VERSION="$(pg_dump --version | sed 's/^pg_dump (PostgreSQL) //')"
printf 'server_version=%s\nclient_version=%s\n' \
  "$SERVER_VERSION" "$CLIENT_VERSION"

PostgreSQL dump clients can target older servers, but version compatibility still belongs in the receipt. Use a current supported client and read the pg_dump version notes before turning a lab command into a cross-version migration policy.

Input two creates the owned source, adds a database comment used by cleanup, and seeds a small table whose ordered count and digest are easy to compare.

runuser -u postgres -- createdb "$SOURCE_DB"
pg -d "$SOURCE_DB" \
  -c "COMMENT ON DATABASE $SOURCE_DB IS '$MARKER';"

pg -d "$SOURCE_DB" <<'SQL'
CREATE TABLE ledger_entries (
  id integer PRIMARY KEY,
  label text NOT NULL,
  cents integer NOT NULL CHECK (cents > 0)
);

INSERT INTO ledger_entries (id, label, cents) VALUES
  (1, 'opening', 1250),
  (2, 'invoice', 2600),
  (3, 'refund', 450);
SQL

pg -d "$SOURCE_DB" -Atc "
  SELECT count(*) || '|' ||
         md5(string_agg(
           id || ':' || label || ':' || cents,
           ',' ORDER BY id
         ))
    FROM ledger_entries;
" | tee "$LAB_DIR/baseline.txt"

Observed baseline:

server_version=17.11 (Debian 17.11-0+deb13u1)
client_version=17.11 (Debian 17.11-0+deb13u1)
BASELINE|3|e37050a846901d099a5a0ace8fbb2444

That digest is not a general database checksum. It is a deterministic assertion for this fixture: the same ordered logical rows must produce the same value after restore.

Hold One Cut Line While Data Changes

Open a second terminal for the exporter and connect to the source database. The SET TRANSACTION reference requires the importing transaction to be REPEATABLE READ or SERIALIZABLE and to import before its first query or data change. The exporter here is read-only REPEATABLE READ so its view stays fixed.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;

SELECT pg_export_snapshot();

SELECT count(*) || '|' ||
       md5(string_agg(
         id || ':' || label || ':' || cents,
         ',' ORDER BY id
       ))
  FROM ledger_entries;

Leave that psql session open after it prints an identifier such as 00000030-00000002-1. Copy the exact value into SNAPSHOT_ID in the original shell. Do not run COMMIT, ROLLBACK, or \q yet.

Now commit a distinguishable row from the original shell and calculate the current source receipt. This write occurs after the exported cut line.

SNAPSHOT_ID='paste-the-exported-identifier-here'

pg -d "$SOURCE_DB" -c "
  INSERT INTO ledger_entries (id, label, cents)
  VALUES (4, 'after_snapshot', 9900);
"

pg -d "$SOURCE_DB" -Atc "
  SELECT count(*) || '|' ||
         md5(string_agg(
           id || ':' || label || ':' || cents,
           ',' ORDER BY id
         ))
    FROM ledger_entries;
" | tee "$LAB_DIR/source-now.txt"

After the late insert, the current source must differ from the three-row baseline. That is the intentional race boundary; without it, two matching exports would prove very little.

A third, independent SQL consumer imports the identifier before reading. Its result should still match the baseline even though a normal new transaction sees four rows.

{
  printf '%s\n' \
    'BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;'
  printf "SET TRANSACTION SNAPSHOT '%s';\n" "$SNAPSHOT_ID"
  cat <<'SQL'
SELECT count(*) || '|' ||
       md5(string_agg(
         id || ':' || label || ':' || cents,
         ',' ORDER BY id
       ))
  FROM ledger_entries;
COMMIT;
SQL
} | runuser -u postgres -- \
      psql -X -A -t -q -v ON_ERROR_STOP=1 -d "$SOURCE_DB" |
      tee "$LAB_DIR/bound-query.txt"

Import order is not cosmetic. If a query runs first, PostgreSQL has already selected a transaction snapshot and SET TRANSACTION SNAPSHOT must fail rather than silently switching views.

Make pg_dump Consume and Restore the Same Cut Line

With the exporter still open, pass the identifier to pg_dump. The current --snapshot option documentation describes exactly this use: synchronizing a dump with a concurrent session or logical replication slot.

runuser -u postgres -- pg_dump \
  --dbname="$SOURCE_DB" \
  --format=custom \
  --snapshot="$SNAPSHOT_ID" \
  --no-owner \
  --no-privileges \
  > "$LAB_DIR/snapshot.dump"

pg_restore --list "$LAB_DIR/snapshot.dump" |
  grep 'TABLE DATA public ledger_entries'
sha256sum "$LAB_DIR/snapshot.dump"

A zero exit and a table-data entry prove that an archive was produced; they do not prove the intended rows are recoverable. Create a clean, marked restore target, load with --exit-on-error, and recompute the same logical receipt.

runuser -u postgres -- createdb "$RESTORE_DB"
pg -d "$RESTORE_DB" \
  -c "COMMENT ON DATABASE $RESTORE_DB IS '$MARKER';"

runuser -u postgres -- pg_restore \
  --exit-on-error \
  --no-owner \
  --no-privileges \
  --dbname="$RESTORE_DB" \
  < "$LAB_DIR/snapshot.dump"

pg -d "$RESTORE_DB" -Atc "
  SELECT count(*) || '|' ||
         md5(string_agg(
           id || ':' || label || ':' || cents,
           ',' ORDER BY id
         ))
    FROM ledger_entries;
" | tee "$LAB_DIR/snapshot-restore.txt"

Only the restored database is the acceptance surface. This mirrors the broader rule in restore data verification: a backup tool returning success is not the same as the recovered data answering the expected question.

At this point, three independent views should agree on the old cut line: the exporter’s query, the bound SQL consumer, and the restored snapshot-bound archive. The live source should disagree in exactly one known way: it contains row four.

Release the Snapshot and Prove Both Boundaries

Return to the exporter terminal and run ROLLBACK; followed by \q. Ending that transaction makes the exported identifier unavailable. The next input tries the stale value and requires failure.

set +e
{
  printf '%s\n' \
    'BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;'
  printf "SET TRANSACTION SNAPSHOT '%s';\n" "$SNAPSHOT_ID"
} | runuser -u postgres -- \
      psql -X -v ON_ERROR_STOP=1 -d "$SOURCE_DB" \
      > "$LAB_DIR/stale-snapshot.out" 2>&1
STALE_STATUS=$?
set -e

test "$STALE_STATUS" -ne 0
grep -E 'snapshot .* does not exist|invalid snapshot identifier' \
  "$LAB_DIR/stale-snapshot.out"
printf 'stale_snapshot_exit=%s\n' "$STALE_STATUS"

PostgreSQL 17.11 returned:

ERROR:  snapshot "00000030-00000002-1" does not exist
stale_snapshot_exit=3

This rejection is valuable. It proves the identifier was tied to a live exporter rather than behaving like a durable recovery point.

For the other boundary, make an ordinary dump after the fourth row has committed, replace only the marked restore database, and prove that this later archive contains the current four-row state.

runuser -u postgres -- pg_dump \
  --dbname="$SOURCE_DB" \
  --format=custom \
  --no-owner \
  --no-privileges \
  > "$LAB_DIR/later.dump"

RESTORE_COMMENT="$(
  pg -d postgres -Atc "
    SELECT coalesce(shobj_description(oid, 'pg_database'), '')
      FROM pg_database
     WHERE datname = '$RESTORE_DB';
  "
)"
test "$RESTORE_COMMENT" = "$MARKER"
runuser -u postgres -- dropdb "$RESTORE_DB"

runuser -u postgres -- createdb "$RESTORE_DB"
pg -d "$RESTORE_DB" \
  -c "COMMENT ON DATABASE $RESTORE_DB IS '$MARKER';"
runuser -u postgres -- pg_restore \
  --exit-on-error \
  --no-owner \
  --no-privileges \
  --dbname="$RESTORE_DB" \
  < "$LAB_DIR/later.dump"

pg -d "$RESTORE_DB" -Atc "
  SELECT count(*) || '|' ||
         md5(string_agg(
           id || ':' || label || ':' || cents,
           ',' ORDER BY id
         ))
    FROM ledger_entries;
" | tee "$LAB_DIR/later-restore.txt"

One consolidated observed receipt separates the shared cut line from the later control:

snapshot_id=00000030-00000002-1
BASELINE|3|e37050a846901d099a5a0ace8fbb2444
SOURCE_NOW|4|8a63b52625461bc8abf97537e957d9c0
BOUND_QUERY|3|e37050a846901d099a5a0ace8fbb2444
SNAPSHOT_RESTORE|3|e37050a846901d099a5a0ace8fbb2444
LATER_RESTORE|4|8a63b52625461bc8abf97537e957d9c0

Accept the shared-cut-line result only when the baseline, imported query, and restored snapshot-bound dump have the same ordered count and digest; the late commit changes the current source; the later ordinary restore matches that four-row source; the stale identifier is rejected after exporter closure; and final cleanup confirms that both named lab databases are absent. A missing equality or negative control is a failed test, not a warning.

Carry the Pattern Into a Production Runbook

Keep the exporter window short. An exported snapshot does not copy rows into a separate object; the open transaction preserves an MVCC visibility horizon while consumers import and work. PostgreSQL’s routine vacuuming documentation explains that old row versions cannot be removed while they may still be visible to a transaction. On a busy system, bound the operation, record its owner, and monitor age instead of leaving an exporter idle. The PostgreSQL XID-age investigation gives the separate evidence path for old transactions and vacuum pressure.

Plan locks as well as visibility. pg_dump takes ACCESS SHARE locks so objects are not removed while they are dumped, and parallel workers use additional lock checks. An exclusive DDL request can queue behind the dump and can in turn affect later lock requests. If schema work overlaps the export window, use the PostgreSQL DDL timeout boundary to decide which clock should own the failure; do not infer that a synchronized snapshot prevents lock contention.

Let one coordinator own four facts: the exporter session, snapshot identifier, list of consumers, and completion state. Start every consumer before ending the exporter, capture each exit code, then release the transaction immediately after all imports and dumps have either succeeded or failed. A process that receives the identifier but has not imported it is not yet protected.

Do not add a manual exporter around pg_dump -j just because multiple connections appear in process monitoring. The pg_dump reference says a parallel dump opens njobs + 1 connections and uses synchronized snapshots so its leader and workers see the same data. Supply --snapshot only when that whole dump must align with something outside its own worker group.

Keep recovery validation separate from consistency selection. --snapshot answers “which committed view did this dump read?” It does not answer whether roles, extensions, large objects, ownership choices, application queries, or recovery time objectives survive restore. Test those properties in a clean target appropriate to the real application.

Finally, avoid stretching this method into a cluster-wide promise. pg_dump works on one database, and PostgreSQL documents that separate databases emitted by pg_dumpall are individually consistent but not synchronized with one another. Use physical backup, storage snapshots with PostgreSQL-safe coordination, or another cluster-aware design when one cut line must cover more than one database.

Remove Only the Owned Lab Databases

Cleanup rechecks the exact database comment before either drop. It refuses an absent marker, a reused name, or any unexpected database. The secret-free receipt and archive hashes can remain outside the database for review.

safe_drop_lab_db() {
  DB_NAME="$1"
  DB_COMMENT="$(
    pg -d postgres -Atc "
      SELECT coalesce(shobj_description(oid, 'pg_database'), '')
        FROM pg_database
       WHERE datname = '$DB_NAME';
    "
  )"

  test "$DB_COMMENT" = "$MARKER"
  runuser -u postgres -- dropdb "$DB_NAME"
}

safe_drop_lab_db "$RESTORE_DB"
safe_drop_lab_db "$SOURCE_DB"

REMAINING="$(
  pg -d postgres -Atc "
    SELECT count(*)
      FROM pg_database
     WHERE datname IN ('$SOURCE_DB', '$RESTORE_DB');
  "
)"
test "$REMAINING" = '0'
printf 'cleanup=databases_absent evidence_dir=%s\n' "$LAB_DIR"

If any stage fails, first end the exporter with ROLLBACK, preserve the snapshot ID, versions, commands, exit codes, hashes, and secret-free output, then remove only the two exact lab database names after their database comments equal VOXFOR_POSTGRES_SNAPSHOT_LAB_182. Never drop an existing, unmarked, or dynamically discovered database to make the rerun pass.

Operationally, change one habit: reach for --snapshot to coordinate independent consumers, not to repair a consistency problem that a normal pg_dump does not have. For more tested database boundaries, use the Database guides after the restore receipt is complete.