Nightly pg_dump looked fine for months until restore day: backups that would not restore

A developer ran a data fix against the production database of our scheduling SaaS with a missing WHERE clause and overwrote the time zone of 38,000 appointments. The change committed at 14:12. We decided to restore last night's dump into a separate instance and copy the correct values back. We were using plain SQL dumps compressed with gzip at the time, so the restore was gunzip -c | psql. It stopped in the middle of the COPY for the appointments table:

psql:<stdin>:4182931: ERROR:  missing data for column "starts_at"
CONTEXT:  COPY appointments, line 2210441: "2210441	8812	2026-03-1"

The file was truncated. So was the one from the night before, and the one before that. The dashboard for the backup cron had been green every night.

Reproducing it

The backup job was a shell script run by cron on a utility VM:

#!/bin/bash
DATE=$(date +%F)
pg_dump -h db-replica.internal.example.com -U backup appdb | gzip > /backups/appdb-$DATE.sql.gz
aws s3 cp /backups/appdb-$DATE.sql.gz s3://example-backups/appdb/
echo "backup ok"

We ran the same pg_dump by hand against the same host during business hours and, after 26 minutes, got the real error:

pg_dump: error: Dumping the contents of table "appointments" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
pg_dump: detail: Command was: COPY public.appointments (id, account_id, starts_at, ends_at, tz, created_at) TO stdout;

What we ruled out

• Permissions. The backup role could read every table; a permission problem would fail at the start, while pg_dump takes its table locks, not halfway through the data.
• Disk full on the VM. 60 percent free; the output size was small, not cut at a disk limit.
• Network timeouts to S3. Upload logs showed complete uploads with matching byte counts. The files were complete copies of truncated dumps.
• A client and server version mismatch. That produces pg_dump: error: aborting because of server version mismatch immediately. Our client was 16.8 against a 16.8 server.

Root cause

Four months earlier, to take load off the primary, we pointed the backup at the streaming replica. A dump runs as one long repeatable-read transaction. While it was copying the large appointments table, vacuum on the primary removed row versions the dump's snapshot still needed, and once the replica had delayed WAL replay for max_standby_streaming_delay (30 seconds by default) it cancelled the query. At first the dump finished before vacuum got in the way; as the table grew, it lost the race almost every night. Plain-format pg_dump writes data table by table, so it streamed everything up to that point, then exited with status 1. In a Bash pipeline, the exit status is that of the last command, gzip, which succeeded. The script printed backup ok, cron logged success, and our monitoring only checked that a new object appeared in S3. The dumps kept getting written, roughly 40 percent smaller than before, every night, for 119 days.

What is happening under the hood

Four independent gaps lined up:

1. Replica query conflicts. A hot standby has to choose between replaying WAL and keeping old row versions for long queries. By default it waits a short time, then cancels the query.
2. Pipeline exit status. Without set -o pipefail, a | b returns the status of b. A failing producer piped into a successful compressor looks like success.
3. Partial output by design. pg_dump does not write to a temporary file and rename it; in plain format it streams as it goes. A failure leaves a well-formed beginning and an abrupt end.
4. Monitoring checked existence, not content. "A file arrived" is not "a database can be restored from it". Nobody had restored a backup since the job was written.

There was also a quieter issue. pg_dump dumps one database and nothing else. Roles and their passwords are cluster-level objects and are not included, so even a complete dump restored into a fresh instance fails on ALTER TABLE ... OWNER TO app_owner with ERROR: role "app_owner" does not exist unless roles are created first.

Environment

Minimal reproduction

Any failure in the producer shows the problem; a missing table is the quickest way to make pg_dump fail:

createdb demo
psql demo -c "CREATE TABLE a (x int); INSERT INTO a VALUES (1);"

pg_dump --table=does_not_exist demo | gzip > demo.sql.gz
echo "exit status: $?"        # prints 0, pg_dump printed an error

set -o pipefail
pg_dump --table=does_not_exist demo | gzip > demo.sql.gz
echo "exit status: $?"        # prints 1

To reproduce the replica cancellation itself, run a long COPY on a standby with the default max_standby_streaming_delay while a bulk DELETE and VACUUM run on the primary for the same table.

Who is affected

Any backup that pipes pg_dump (or mysqldump, or tar) into a compressor or uploader without pipefail, and any backup process that has never been restored. It hits small teams hardest because the backup script is usually written once and never revisited.

The fix

We recovered the 38,000 appointments from the audit trigger table instead, and then rewrote the backup job. The new script fails loudly, uses the custom format, includes globals, and verifies its own output:

#!/usr/bin/env bash
set -euo pipefail

DATE=$(date -u +%Y%m%dT%H%M%SZ)
DIR=/backups/$DATE
mkdir -p "$DIR"
export PGHOST=db-backup-standby.internal.example.com PGUSER=backup PGDATABASE=appdb

pg_dump --format=custom --compress=6 --file="$DIR/appdb.dump" --no-password
pg_dumpall --globals-only --no-role-passwords --file="$DIR/globals.sql"

# the archive must have a readable table of contents
pg_restore --list "$DIR/appdb.dump" > "$DIR/toc.txt"

# every user table must have a data entry in the archive
EXPECTED=$(psql -XAtc "SELECT count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema')")
ACTUAL=$(grep -c ' TABLE DATA ' "$DIR/toc.txt")
if [ "$EXPECTED" -ne "$ACTUAL" ]; then
  echo "table data mismatch: expected $EXPECTED got $ACTUAL" >&2
  exit 1
fi

sha256sum "$DIR"/* > "$DIR/SHA256SUMS"
aws s3 cp --recursive "$DIR" "s3://example-backups/appdb/$DATE/"
curl -fsS -m 10 https://hc.example.com/ping/appdb-backup > /dev/null

The last line pings a dead man's switch: if no ping arrives in 26 hours, we get paged. Because of set -e, any failure before it means no ping.

For the replica, we created a dedicated backup standby that serves no application traffic and set it to wait for long queries instead of cancelling them:

# postgresql.conf on the backup-only standby
max_standby_streaming_delay = -1
hot_standby_feedback = off

With -1 the standby pauses replay until the dump finishes, so it falls behind by the dump duration every night, which is fine for a box nobody reads from. We deliberately did not turn on hot_standby_feedback, because it makes the primary keep dead rows for the dump's whole duration and causes bloat there.

- pg_dump -h db-replica.internal.example.com -U backup appdb | gzip > /backups/appdb-$DATE.sql.gz
+ set -euo pipefail
+ pg_dump --host=db-backup-standby.internal.example.com --format=custom --file="$DIR/appdb.dump"
+ pg_dumpall --globals-only --no-role-passwords --file="$DIR/globals.sql"
+ pg_restore --list "$DIR/appdb.dump" > "$DIR/toc.txt"

Alternatives and trade-offs

• Physical backups with WAL archiving (pgBackRest, Barman, or pg_basebackup plus archiving). They give point-in-time recovery, which would have let us restore to 14:11 exactly. For a 180 GB database this is the better primary backup; we added pgBackRest a month later and kept pg_dump as a logical second copy that survives physical corruption.
• Directory format with parallel jobs (--format=directory --jobs=4) dumps and restores faster on large databases, at the cost of many files instead of one.
• Managed database snapshots if you are on a managed service. They still need restore testing.

How to test the fix

A nightly job restores the newest dump into a throwaway container and runs sanity checks:

#!/usr/bin/env bash
set -euo pipefail
LATEST=$(aws s3 ls s3://example-backups/appdb/ | awk '{print $2}' | sort | tail -1)
aws s3 cp --recursive "s3://example-backups/appdb/$LATEST" /restore/
cd /restore && sha256sum -c SHA256SUMS

docker run -d --name restore-test -e POSTGRES_PASSWORD=test -p 55432:5432 postgres:16.8
until pg_isready -h localhost -p 55432; do sleep 1; done
export PGHOST=localhost PGPORT=55432 PGUSER=postgres PGPASSWORD=test

psql -X -f globals.sql postgres
createdb appdb
pg_restore --exit-on-error --jobs=4 --dbname=appdb appdb.dump

psql -XAt appdb -c "SELECT count(*) FROM appointments WHERE created_at > now() - interval '2 days'"
docker rm -f restore-test

The last query must return a number above zero; if yesterday's rows are missing, the dump is stale. --exit-on-error makes pg_restore stop on the first error instead of continuing and reporting a count at the end.

Confirming in production

For the first two weeks we checked each night's restore job output by hand, then wired it to alerts. A healthy run looks like:

appdb.dump: OK
globals.sql: OK
toc.txt: OK
restore finished in 41m12s
recent appointments: 18422

We also track replica cancellations with pg_stat_database_conflicts and dump size per night and alert if it changes by more than 15 percent from the 7-day median, which would have caught the original problem on day one. We documented the restore time (41 minutes), which became our real recovery time objective for logical restores, instead of the guess in the runbook.

Regression guard

The restore test runs every night and pages on failure. A CI check fails any shell script in the infra repository that contains a pipe but no set -o pipefail, using ShellCheck plus a small grep. Every quarter, an engineer who did not write the script performs a full restore from the runbook alone.

Security implications

Backups contain every row of the database, so they deserve the same protection as the primary: encrypted bucket, access limited to the backup and restore roles, and no public read under any circumstances. The backup login can read every table, so pg_hba.conf allows it only from the utility VM's address, and only to the backup standby. We used --no-role-passwords so password hashes do not travel with every dump. Keep pg_dump and pg_restore on current minor releases as well: the August 2025 minor releases fixed CVE-2025-8714, where a plain-format dump from a malicious server could run psql meta-commands during restore.

Prevention checklist

• Put set -euo pipefail at the top of every backup script.
• Verify each dump with pg_restore --list and a table count before uploading.
• Dump globals separately; pg_dump does not include roles.
• Restore-test the newest backup automatically, every night.
• Alert on missing pings and on large size changes, not just on file existence.

References

• PostgreSQL docs, pg_dump: https://www.postgresql.org/docs/16/app-pgdump.html
• PostgreSQL docs, pg_restore: https://www.postgresql.org/docs/16/app-pgrestore.html
• PostgreSQL docs, pg_dumpall: https://www.postgresql.org/docs/16/app-pg-dumpall.html
• PostgreSQL docs, Handling Query Conflicts on hot standby: https://www.postgresql.org/docs/16/hot-standby.html
• GNU Bash manual, Pipelines: https://www.gnu.org/software/bash/manual/html_node/Pipelines.html
• PostgreSQL security information, CVE-2025-8714: https://www.postgresql.org/support/security/CVE-2025-8714/