A backup job that exits 0 tells you a file was written. It does not tell you the file can become a running database with your schema, your extensions, and your roles intact. The only way to know is to restore it on a schedule and time yourself, and the first restore you ever attempt should not be during an outage.
This is the drill I run — roughly monthly, and always after any change to the schema, the extension list, or the Postgres major version. It takes about twenty minutes once it's scripted, and every single time I've introduced it somewhere, the first run failed.
Why does a backup that "succeeded" fail to restore?
Because pg_dump captures the contents of one database, not the environment it lived in. The three things it leaves behind are the three things that break your restore:
Roles are cluster-level, not database-level. pg_dump does not include CREATE ROLE. Restore into a fresh cluster and you get:
pg_restore: error: could not execute query: ERROR: role "app_user" does not exist
Command was: ALTER TABLE public.orders OWNER TO app_user;
Extensions must exist as installed binaries on the target machine. The dump contains CREATE EXTENSION vector;, but that only works if the target already has the pgvector shared library on disk:
ERROR: could not open extension control file "/usr/share/postgresql/16/extension/vector.control": No such file or directory
This is the one that bites people who dump from a managed provider with extensions preinstalled and restore into a stock postgres:16 container.
Client and server versions have to line up. pg_dump refuses to dump from a server newer than itself:
pg_dump: error: server version: 16.3; pg_dump version: 15.6
pg_dump: error: aborting because of server version mismatch
The failure mode that actually hurts is silent: your cron box quietly ships an older pg_dump than your upgraded server, the job starts failing, and nobody reads the log because nothing pages on a backup job. Alert on backup failure and staleness, not just on failure.
A backup job's exit code proves a file exists; only a restore proves the file is a database.
What does the drill actually look like?
Five steps, all scriptable, all runnable on a laptop.
- Pull the most recent backup artifact — from object storage, not from a copy sitting on the same host as production.
- Start a throwaway Postgres of the same major version as production.
- Restore into it with errors treated as fatal.
- Run assertions: row counts on your three or four most important tables, plus a query that exercises an extension.
- Record the wall-clock time and tear it down.
Here's the core of it. Taking the dump:
#!/usr/bin/env bash
set -euo pipefail
pg_dump \
--format=custom \
--no-owner \
--no-privileges \
--file="backup_$(date -u +%Y%m%dT%H%M%SZ).dump" \
"$DATABASE_URL"
--no-owner and --no-privileges are what make the dump portable: ownership statements get dropped, so the restore doesn't demand that app_user exists on the target. You then re-apply grants from your migration tooling, where they belong. Keep roles in version control as SQL; do not rely on them surviving in a dump.
The drill itself:
#!/usr/bin/env bash
set -euo pipefail
DUMP="$1"
PGVER="${PGVER:-16}"
CONTAINER="restore-drill-$$"
start=$(date +%s)
docker run -d --name "$CONTAINER" \
-e POSTGRES_PASSWORD=drill \
-p 55432:5432 \
"pgvector/pgvector:pg${PGVER}"
until docker exec "$CONTAINER" pg_isready -U postgres -q; do sleep 1; done
export PGPASSWORD=drill
CONN="postgresql://postgres@localhost:55432/postgres"
pg_restore --dbname="$CONN" --exit-on-error --jobs=4 "$DUMP"
psql "$CONN" -v ON_ERROR_STOP=1 -f drill_assertions.sql
echo "restore completed in $(( $(date +%s) - start ))s"
docker rm -f "$CONTAINER" >/dev/null
Two flags carry most of the weight. --exit-on-error is the important one: by default pg_restore prints errors, keeps going, and exits 0, which means an unattended restore check without it will happily report success on a half-populated database. --jobs=4 parallelizes table data and index builds, and it only works with the custom or directory formats — another reason to stop using plain SQL dumps for anything large.
The assertions file is deliberately boring, and it should fail loudly:
\set ON_ERROR_STOP on
-- structural: does the extension work, not just exist?
SELECT '[1,2,3]'::vector <-> '[3,2,1]'::vector AS distance_check;
-- volumetric: catch a restore that "worked" but landed empty
DO $$
DECLARE n bigint;
BEGIN
SELECT count(*) INTO n FROM orders;
IF n < 1000 THEN
RAISE EXCEPTION 'orders table has only % rows — restore is suspect', n;
END IF;
END $$;
-- freshness: how much data would we actually have lost?
SELECT now() - max(created_at) AS data_age FROM orders;
That last query is the one worth reading out loud in a team channel. It converts an abstract retention policy into a number: if we restored right now, we would be missing this much. That number is your real RPO, and it is usually worse than whatever the backup docs implied.
Assert on row counts and data age, not on the restore's exit code — an empty database restores perfectly.
Nightly dumps or point-in-time recovery?
pg_dump gives you a consistent snapshot and nothing between snapshots. If it runs at 03:00 and you lose the primary at 17:00, you have lost fourteen hours. Point-in-time recovery closes that gap by shipping the write-ahead log continuously, so you can replay to a chosen moment — including "one second before that DELETE without a WHERE."
| Approach | Typical RPO | Restore complexity | Where it fits |
|---|---|---|---|
pg_dump custom format to object storage |
Since last dump (hours) | Low — one command | Side projects, small internal apps, portable migrations |
| Managed provider snapshots + PITR | Seconds to minutes | Low, but provider-shaped | Anything on RDS, Cloud SQL, Supabase, Neon, Crunchy Bridge |
| pgBackRest or WAL-G to your own bucket | Seconds to minutes | Medium — real config, real ops | Self-hosted Postgres you intend to keep |
| Physical replica only | Near zero for hardware loss | N/A | Not a backup — replicates your DROP TABLE faithfully |
That last row is the trap I see most often. A standby protects you from a dead machine; it does not protect you from a bad migration, because the destructive statement replicates in milliseconds. Keep both.
If you self-host and want continuous archiving without writing your own WAL shipping, pgBackRest is the option that handles full/differential/incremental backups, parallel compression, and retention expiry with one config file and one command — at the cost of a genuinely non-trivial setup pass and a config format you will have to read the docs for every time you touch it. WAL-G is the leaner alternative when you want to push straight to S3-compatible storage with minimal moving parts, though you'll find fewer worked examples when something goes wrong. On the managed side, Neon's branching turns a restore drill into creating a branch from a past timestamp and pointing a test connection string at it, which is the lowest-friction version of this whole workflow — the constraint being that it's provider-specific, so you should still keep an independent logical dump if you ever want to leave.
Whatever you pick, store backups in an account or bucket that your application's credentials cannot delete. Ransomware and a bad terraform destroy fail the same way.
A replica is not a backup, and a backup you cannot restore without the original provider is a hostage.
How often should you run the drill?
Monthly is a reasonable floor for a small team. Run it additionally after: a Postgres major version upgrade, adding or removing an extension, changing the backup tool or its flags, and any change to who owns the storage bucket. Wire it into CI on a schedule if you can — a weekly GitHub Actions job that restores yesterday's dump into a service container and runs the assertions gives you a red build instead of a discovery at 2 a.m.
Track exactly two numbers over time: minutes to a usable database (your RTO) and the data_age from the assertions (your RPO). If either number surprises someone on the team, you've found the actual gap. As of mid-2026 I've never seen a first drill where both numbers matched what people assumed.
FAQ
How do I test a Postgres backup without touching production?
Restore the dump into a disposable container on a non-production port, run assertions against it, then destroy the container. Nothing in the drill connects to production except the read that fetched the backup file from object storage.
Why does pg_restore exit 0 even though it printed errors?
Because pg_restore treats most errors as non-fatal by default so it can restore as much as possible. Pass --exit-on-error (and --single-transaction if you want all-or-nothing) whenever a script is checking the result.
Is a read replica enough of a backup for a small app?
No. A replica protects against losing a machine but faithfully replicates destructive SQL such as a bad DELETE or migration. You need point-in-time recovery or periodic dumps to recover from a mistake you made yourself.
Bottom line
If you run a side project or a small internal app, nightly pg_dump --format=custom --no-owner to object storage plus a monthly scripted restore drill is enough, and it's an afternoon of work. Once real customer data is involved, move to continuous archiving — your provider's PITR if you're managed, pgBackRest or WAL-G if you're self-hosted — and keep the logical dump as your escape hatch from the provider. Never count a physical replica as a backup. And measure the drill: the restore you have never timed is the one that takes three hours on the worst possible day.
Top comments (0)