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 database’s real state 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 supported physical backup creates a consistent recovery representation.

Dump with pg_dump

Create a PostgreSQL custom-format lab dump:

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

pg_dump runs inside a single transaction, so 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.

The --list command doubles as a quick sanity check — it reads the dump’s table of contents:

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

If the file is truncated or corrupt, this fails immediately. A zero-byte app.dump created by 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 on row counts and important relationships. Matching row counts and intact foreign keys between live and restored are the proof that this backup is worth keeping.

Two operational notes to close. Protect credentials and dumps — a logical dump is your entire database in readable form, so encrypt it at rest, for example by backing it up into a restic repository. And coordinate database version compatibility and extensions with the recovery plan: a dump from PostgreSQL 17 with postgis installed needs a target that can provide both.