A Small Backup Restore Drill

Photo: Unsplash.

A backup job saying “success” proves that a command wrote something. A restore drill checks whether that something can become a usable database.

For a PostgreSQL custom-format backup, create an isolated target on a non-production instance:

createdb blog_restore_test
pg_restore \
  --exit-on-error \
  --no-owner \
  --dbname=blog_restore_test \
  ./blog-2026-05-18.dump

Use client tools compatible with the server version and read the output. --exit-on-error prevents a long stream of later messages from hiding the first meaningful failure.

Then check facts that matter to the application:

psql blog_restore_test -c 'select count(*) from posts;'
psql blog_restore_test -c 'select max(updated_at) from posts;'
psql blog_restore_test -c '\dt'

Counts alone are weak, so add a small application-level smoke test if possible: start a disposable app instance against the restored database, load a known record, and perform one read-only query through the normal interface.

Record four things in the runbook:

• Which backup was restored and its checksum.
• The exact restore command and tool versions.
• How long the restore took.
• Which checks passed or failed.

Destroy the isolated target only after saving the result. Never point an untested restore command at production, and never assume object storage retention is the same thing as a recovery plan.

A quarterly drill is enough for many small services. What matters is repeating it after schema, encryption, credentials, or backup tooling changes.

References

• PostgreSQL: pg_dump
• PostgreSQL: pg_restore