Back up a database consistently

Here’s a mistake that produces backups which look perfect and restore to garbage: pointing your file backup tool at a running database’s data directory.

A running database changes related files together. While your backup tool walks the directory, the database keeps writing. So the copy gets table files from 2:00 AM and index files from 2:03 AM. That torn mixture was never the real state of the database at any moment. Restored, it’s corruption. The server may refuse to start. Or worse, start and serve subtly wrong data.

The fix is to ask the database for a backup instead of taking one behind its back. A logical dump, or a physical backup the database supports, gives you one consistent picture.

Dump with pg_dump

Create a PostgreSQL dump in custom format, then read its table of contents:

pg_dump --format=custom --file app.dump appdb
pg_restore --list app.dump | head

pg_dump runs inside a single transaction. The dump reflects one consistent instant, no matter how long it takes or how busy the database is. The custom format is compressed and lets pg_restore restore selectively.

--list doubles as a quick sanity check:

; Archive created at 2026-08-03 03:00:02 CEST
;     dbname: appdb
;     Format: CUSTOM
...

If the file is truncated or corrupt, this fails right away. A zero-byte app.dump from a dump that failed silently in cron is a classic way to discover, months later, that you have no database backups at all. Check the exit status and the file size after every dump.

Prove it restores

Restore into a separate empty database. Never over the live one:

createdb appdb_restore
pg_restore --dbname appdb_restore app.dump
psql appdb_restore -c 'select count(*) from orders;'

Then run application-level checks. Compare row counts and important relationships between live and restored. Matching counts and intact foreign keys are the proof that this backup is worth keeping.

Two operational notes

Protect the dumps. A logical dump is your entire database in readable form. Encrypt it at rest, for example by backing it up into a restic repository.

And plan for version compatibility. A dump from PostgreSQL 17 with postgis installed needs a target that can provide both. Write the versions and extensions into the recovery plan.