Postgres in Docker: four backups taken under load

All four methods produced a backup that restored into a fresh container and passed every check, three runs out of three. If you only read one row, read the last one: a cp -a of the data directory of a running Postgres also passed, and the PostgreSQL manual still says not to do it. This page shows what the test saw and why it is not permission.

What was measured

The database was created with pgbench -i -s 50 inside a postgres:18 container whose data directory sat on a bind mount. During each backup a pgbench -n -c 4 -j 2 TPC-B write load ran, starting 8 s before the backup began and continuing until it ended. Each of the three runs did all four methods in turn on the same, growing database, and each backup was restored into a new container on an empty directory.

Restore checks, applied to the restored container:

• Row counts: 50 branches, 500 tellers, 5,000,000 accounts.
• The pgbench balance invariant. Every transaction adds the same delta to one account, one teller and one branch and inserts it in pgbench_history, so in a consistent database sum(abalance) = sum(tbalance) = sum(bbalance) = sum(history.delta). The -n (no vacuum) flag matters: without it pgbench truncates the history table at the start of each run and the invariant breaks across runs, which is what our first attempt did (see below).
• pg_amcheck --heapallindexed on the restored database, which uses the amcheck extension to verify heap pages and B-tree indexes. Data checksums were on (show data_checksums returned on); the test did not change that setting.

Per-run output is in checks.json and raw/*.check.txt.

Reading the table

• pg_dump -Fc made a 15 MB file from a 792 MB database; plain SQL was 490 MB. pgbench data is synthetic and compresses well, so the ratio will not hold for your data.
• pg_basebackup was the slowest to take (5.3 s) but the fastest to restore (2.7 s). Restore time runs from the same point for all four methods (the backup artifact exists, nothing is restored yet), so the dumps include starting an empty container and its first-start initialisation (1.7 to 3.2 s of the total) as well as createdb and the replay. The dumps took 12.0 s (plain) and 11.5 s (custom). The directory copies took 2.7 s (pg_basebackup) and 5.5 s (raw copy, which also ran crash recovery). That the dumps are slower is consistent with them replaying SQL or pg_restore and rebuilding the indexes while pg_basebackup copies files and needs no rebuild, but the test did not measure where the dump time went.
• The raw copy was 1,496 MB, larger than the 844 MB basebackup. It copies the whole data directory as it stands, WAL segment files included; why the two differ by this much was not investigated further.

What the raw copy did

It started on the first attempt in 3 of 3 runs, ran crash recovery, and passed all the checks. Each restore log (raw/rawcopy-r*.restore-attempt1.log) contains “database system was not properly shut down; automatic recovery in progress”. The pg_basebackup restores instead logged “starting backup recovery”, the path that comes from a backup_label.

Our reading, as an inference rather than a measurement: if cp -a copied global/pg_control before it reached pg_wal, the WAL in the copy would extend past the point where the data files were copied, and replay from the last checkpoint would repair any torn pages. The test did not record the order in which cp -a copied files, and that order follows the directory listing, so it is luck of the tool and the filesystem, not a guarantee. The PostgreSQL manual says the server must be shut down to get a usable file system backup, unless you take an atomic snapshot of the data directory including the WAL. Three clean runs at about 500 transactions per second (the pgbench progress lines in raw/*.pgbench.log) on a small VM do not test a busier server, a slower disk, or a copy tool that orders files differently.

What went wrong in the test

• First attempt: the balance invariant failed for the custom-format dump and for pg_basebackup restores (the first plain-dump restore passed, because it came right after a fresh history table). The cause was the test, not Postgres: pgbench without -n truncates pgbench_history on each start. The run was discarded and redone with -n.
• An earlier version of the script timed the dump restores from the moment the empty container was ready, but the directory copies from the start of the copy, so the restore times were not comparable (and the raw copy ranged from 4.5 s to 11.7 s across its three runs). The script now starts one timer for all four methods, and the whole test was run again; all numbers on this page are from that second run, which passed every check again.
• First attempt: the raw copy copied the bind-mount root (the 18/docker directory ended up nested inside the directory used as the data directory), so Postgres refused to start. That was a script bug, fixed by copying 18/docker itself.

Reproduce it

#!/usr/bin/env bash
# Test pg-backup: Postgres 18 in Docker, pgbench scale 50 under continuous write load; pg_dump (plain, custom), pg_basebackup, raw copy.
set -uo pipefail
source "$(dirname "$0")/../_common/lib.sh"
ID=pg-backup; NAME=h-$ID; D="$ROOT/lab/tests/$ID"; E="$ROOT/evidence/tests/$ID"; RAW="$E/raw"
rm -rf "$RAW"; mkdir -p "$RAW"
trap 'vm down $NAME' EXIT
host_env "$RAW"
vm up $NAME || exit 1
gssh 'sudo apt-get install -y -qq time >/dev/null 2>&1; docker pull -q postgres:18 >/dev/null && echo pulled'
vm scp-to "$D/guest.sh" /tmp/pg-guest.sh
gssh 'SCALE=50 bash /tmp/pg-guest.sh' > "$RAW/guest-stdout.txt" 2>&1
vm scp-from /srv/pgraw/. "$RAW/"
guest_env "$RAW" 'docker --version; docker exec pg1 postgres --version; docker exec pg1 pgbench --version'
python3 "$D/summarize.py" "$RAW" "$E"

The guest-side script guest.sh has the exact commands. The backup commands were:

# plain dump, custom dump (run against the container, output redirected on the VM)
docker exec pg1 pg_dump -U postgres -d pgb > backup.sql
docker exec pg1 pg_dump -U postgres -Fc -d pgb > backup.dump

# base backup, streamed WAL, inside the container
docker exec pg1 pg_basebackup -U postgres -D /backups/base -X stream -c fast

# raw copy of the running data directory (the method this page does not recommend)
sudo cp -a /srv/pgdata/18/docker /srv/backups/raw

Restores: psql -v ON_ERROR_STOP=1 < backup.sql and pg_restore --exit-on-error -d pgb < backup.dump into a new container with an empty data directory and createdb pgb; for the base backup and the raw copy, the directory was copied to /var/lib/postgresql/18/docker of a new container and started.

pg_dump dumps one database and, per the PostgreSQL manual, does not dump roles or tablespaces; for a whole cluster the manual points to pg_dumpall (--globals-only for the roles alone). The test restored a single database into a container that already had the postgres superuser and did not try that, so a restore into a fresh server would also need the roles. pg_restore without --exit-on-error carries on after an error, which is why the restore command above has it.

The restore script has one retry path for the directory copies: if the container does not come up and a stale postmaster.pid is present, it deletes the file and starts once more. It never ran (start_attempts 1 in all six directory restores), so every restore on this page started on the first attempt.

What this test did not cover

• Point-in-time recovery and WAL archiving. Only the four methods above were compared.
• Larger databases. The database was 792 MB and the VM had 3 GB of RAM, so everything was largely cached and times are short.
• A heavier write load or a slower disk, where a raw copy is more likely to fail. The raw copy’s 3 passes say nothing about its failure rate.
• Other Postgres versions, replicas, extensions beyond amcheck, and tablespaces.
• Whether amcheck would catch every kind of corruption; it found nothing in any restore.
• Network backup targets and encryption of the backups.