Your Postgres backup exists. Does it restore?

Most teams can answer "do we have backups?" in a second. Far fewer can answer "when did we last restore one, end to end, and how long did it take?" A backup you have never restored is a hope, not a backup.

The annoying part is that broken backups look fine. The file is there, cron says it ran, the size is roughly what it was last week. You find out it is broken on the worst possible day: the day you need it.

This post covers six quiet ways a Postgres backup breaks, a manual restore drill you can run in five minutes with Docker, and how to put that drill in cron so it runs every night. Everything here works with plain pg_dump and a stock postgres Docker image — no product required.

Six silent ways a Postgres backup fails

1. The dump was cut off mid-write

The disk filled up, the OOM killer stepped in, or the server rebooted during the nightly job. You are left with a file that has a valid header and half the data. Here is what restoring one looks like — a test shop database whose custom-format dump was truncated:

pg_restore: error: could not read from input file: end of file

The restore did not stop cleanly at zero tables. It got through part of the archive: order_items and customers came back with all their rows, while orders and products came back empty. If you only checked "does the file exist and is it non-empty", this backup passed.

How to spot it: actually restore it, and compare row counts of your key tables against what you expect.

2. The pipe lied about success

This line looks harmless in a crontab:

pg_dump mydb | gzip > /backups/mydb.sql.gz

The exit status of a pipeline is the exit status of its last command. If pg_dump fails — wrong password, lost connection, lock timeout — gzip happily compresses the partial output and exits 0. Cron sees success. Your monitoring sees success.

Fix: skip the pipe. The custom format is already compressed, and the exit code is pg_dump's own:

pg_dump -Fc -f /backups/mydb-$(date +%F).dump mydb || echo "pg_dump failed" >&2

If you do need a pipeline, run it from a bash script with set -o pipefail.

3. Roles are not in the dump

pg_dump dumps one database. Roles (users) are cluster-wide objects, so they are not included. A plain SQL dump still contains lines like ALTER TABLE ... OWNER TO app;, and on a fresh server they fail with role "app" does not exist. By default psql prints the error and keeps going, so the restore "finishes" with an exit code of 0.

Fix: either restore with --no-owner --no-privileges, or back up globals too:

pg_dumpall --globals-only > /backups/globals.sql

And when replaying plain SQL, make errors fatal: psql -v ON_ERROR_STOP=1 -f dump.sql.

4. An extension is missing on the target

If your database uses PostGIS, TimescaleDB, or another extension that is not part of core Postgres, the dump contains CREATE EXTENSION, but the extension's files must exist on the server you restore to. A vanilla postgres image does not have them, and everything that depends on the extension fails.

Fix: restore into the same image you run in production (for example postgis/postgis:16-3.4), and keep that image tag written down next to the backup job.

5. The restore tools are older than the dump

pg_restore can read archives made by older pg_dump versions, but not newer ones. Dump with Postgres 17 tools, try to restore on a box with Postgres 15 tools, and you get an "unsupported version in file header" error — at 3 a.m., during an incident.

Fix: restore with the same or newer major version than the one that made the dump. Test with exactly the version you would use in a real recovery.

6. The backup is fine — it is just old

The cron job was disabled during a migration and never re-enabled. Or someone pointed it at the staging database. Every file restores perfectly, and every file is three weeks out of date.

How to spot it: after restoring, check the newest timestamp in a table that changes all the time:

SELECT now() - max(created_at) AS age FROM orders;

If the age is larger than your backup interval, the backup is stale, no matter how cleanly it restored.

Bonus: restore time is a number you should know

Even a healthy backup can surprise you. Restoring rebuilds every index and constraint, so a dump that took two minutes to create can take far longer to restore. That time is your real recovery time objective (RTO). Measure it before an incident does it for you.

A five-minute restore drill with Docker

You do not need a spare server. A throwaway container on any machine that can read your backups is enough. This assumes a custom-format dump (pg_dump -Fc) and Postgres 16 in production — change the image tag to match yours.

# 1. A disposable Postgres with no network access at all
docker run -d --name drill --network none -e POSTGRES_PASSWORD=drill postgres:16

# 2. Wait for the real server, not the temporary one the image starts during init
until docker exec drill pg_isready -h 127.0.0.1 -U postgres >/dev/null 2>&1; do sleep 1; done

# 3. Copy the backup in and restore it, timing the restore
docker cp /backups/mydb-2026-10-08.dump drill:/tmp/backup.dump
docker exec drill createdb -U postgres restored
time docker exec drill pg_restore -U postgres -d restored \
  --no-owner --no-privileges --exit-on-error /tmp/backup.dump

# 4. What actually came back?
docker exec drill psql -U postgres -d restored \
  -c "ANALYZE" \
  -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10"

# 5. Is it fresh?
docker exec drill psql -U postgres -d restored \
  -c "SELECT now() - max(created_at) AS age FROM orders"

# 6. Clean up
docker rm -f drill

A few details that matter:

• --network none means the restored copy of your data cannot talk to anything, even if a dump contains something unexpected. The loopback interface still works, which is all pg_isready and psql inside the container need.
• -h 127.0.0.1 in step 2 is deliberate. During first start, the official image runs a temporary server that only listens on a Unix socket while it runs init scripts. Checking over TCP waits for the real server.
• --exit-on-error makes pg_restore stop at the first error. Without it, pg_restore keeps going and only reports "errors ignored on restore" at the end — easy to miss in a log.
• n_live_tup after ANALYZE is an estimate, which is fine here. You are looking for tables that are suddenly empty or much smaller than usual, not for exact counts.
• The time output from step 3 is your RTO for this database. Write it down.

If all of this passes, you know three things you did not know before: the file is complete, it restores into a clean server, and the data in it is recent.

Run it every night

A drill you run once is an audit. A drill that runs every night is monitoring. You can wrap the commands above in a script and put it in cron — or use restore-drill, a small open-source tool (Apache-2.0) I wrote that does exactly this and exits 0 or 1 so cron and your alerting can act on it:

docker run --rm \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /var/backups/pg:/backups:ro \
  mrvi0/restore-drill run --source /backups --pg-version 16 \
  --fresh-table orders --fresh-column created_at --max-age 24h

restore-check: PASS
  backup     /backups/shop-2026-10-08.dump (13.9 MB, modified 2026-10-08T15:09:25+00:00)
  format     custom
  postgres   16
  restore    OK in 1.3s
  freshness  orders.created_at: newest 2026-10-08 15:08:23.982371+00, age 1m (max 1d) OK
  tables     4 tables, ~1,642,032 rows

It picks the newest backup from a directory or an S3 prefix, detects the format (custom, tar or plain SQL, optionally gzipped), restores it into a throwaway container with no network, checks freshness and row counts, and can send a Telegram alert. Notifications carry only pass/fail and metrics, never table contents or error text.

A nightly cron entry:

0 6 * * * restore-check run --source s3://my-bucket/pg/ --pg-version 16 >> /var/log/restore-check.log 2>&1

What it does not do yet, so you can decide if it fits: pg_dumpall output and directory-format (-Fd) dumps are not supported, S3 backups are downloaded to local disk first, and the Docker image needs the host Docker socket, which is root-equivalent on that host. If you would rather not mount the socket, install it with pipx instead.

Your turn

Pick one production database and run the five-minute drill today. If it passes, you have a number for your RTO and one less thing to worry about. If it fails, you just found out on a quiet afternoon instead of during an outage.

If you try restore-drill, issues and feedback are very welcome on GitHub. I am also exploring a hosted version that alerts you when the drill did not run and keeps restore history for audits — there is an early-access list if that is useful to you.

How do you test your backups today — or do you? I would love to hear in the comments.