Your pg_dump Is Not a Backup Until You've Restored It

A backup needs a tested path back to a working system. Most production systems I audit have the same backup story: a cron job runs pg_dump every night, the files land somewhere with dates in their names, and everyone feels safe.

That feeling is the problem. A directory full of dated dump files proves exactly one thing — that a process wrote files. Whether those files can become a working database again is a separate question, and it's the only question that matters.

A dump you've never restored is a hypothesis

Restore testing is the actual backup. The dump file is just its raw material. The failure modes it catches are all invisible from the outside: a dump truncated by a full disk or a killed process, a pg_restore version mismatch, missing roles and extensions that only exist on the old server, or a job that's been faithfully dumping the wrong database for months. Every one of these produces a file with a plausible size and a recent date.

The fix is a scheduled job, not a policy document: restore the latest dump into a scratch instance, run the application's migrations check, count rows in the tables that matter, and execute a handful of known queries. If any step fails, that's an alert with the same severity as production being down — because recovery readiness is not established; investigate other verified copies.

There's a second benefit nobody prices in: the restore job is your disaster drill. The day something actually breaks, you're running a script that's succeeded hundreds of times, not improvising a runbook at 3 a.m.

Your database is not your data

Here's the gap that bites hardest. Real systems store rows and files: invoice PDFs, guest photos, incident attachments, scanned documents. The rows hold the paths; object storage holds the bytes.

A backup regime that covers only PostgreSQL restores you into a database full of pointers to files that no longer exist. Technically a successful restore. Practically, a data loss.

Files referenced by database rows are part of the data, full stop. The backup scope must include the object storage, versioned or mirrored on the same schedule and retention as the database, and the restore test should verify both sides together. Systems like a hotel management platform generate media constantly. A room-status photo is evidence, not decoration, and losing it is a business problem even when every row survives.

Retention and restore-time targets

Two numbers define a backup regime. Both should be decisions, not accidents:

• How much can you afford to lose? The gap between backups is data you've agreed to lose. Nightly dumps mean up to 24 hours gone. If that is unacceptable, compare more frequent backups with WAL (write-ahead log) archiving and point-in-time recovery. Test the method against the required recovery point.
• How long can you afford to be down? Measure the restore, don't estimate it. A logical restore of a large database takes hours; index rebuilds dominate, and the first time people discover this is usually during the outage. If the measured time exceeds the target, evaluate physical backups and tools such as pgBackRest, then measure restore time again.

Retention answers a different question: how far back can you reach when corruption is discovered late? A bad deploy that silently mangles data can sit unnoticed for weeks. The practical shape: a rolling window of dailies plus longer-lived weekly and monthly snapshots.

Verify the seams continuously

Between disasters, data drifts. Rows get deleted without their files, files get uploaded without their rows, a cleanup script overreaches. A periodic reconciliation job keeps the two stores honest:

It's a boring job: list what the rows reference, list what storage holds, diff the sets. It turns silent corruption into a ticket instead of a surprise. Run it weekly; alert on any growth in the "missing" set.

Back up what a rebuild can't recreate

The priority order falls out of one question: if this server vanished, what could we not recreate? Code is in git. Containers can be rebuilt from Dockerfiles if their sources and dependencies remain available. Packages can be reinstalled if the required versions remain obtainable.

What cannot be recreated: the database, the uploaded files, the TLS certificates and secrets, and the pile of small configuration decisions accumulated over years. That irreplaceable set is the backup scope — and it's always bigger than just PostgreSQL.

None of this is exotic engineering. It's one scratch instance, two scheduled jobs, and the discipline to treat a failed restore test as a production incident. The goal is evidence of recovery within agreed data-loss and downtime limits.

When you do restore into a fresh environment, details you took for granted resurface fast. Timezone configuration is a classic, which is why I keep timestamps grouped correctly across timezones in the restore test's query set.