A Backup You Have Never Restored Is Not a Backup

The worst moment to discover that a backup does not work is the moment you need it. And yet the overwhelmingly common pattern is exactly that: backups configured once, a green tick in a dashboard for eighteen months, and then a restore that produces an empty database.

Testing a database backup restore is not a best practice in the way that most best practices are optional. A backup is a claim, and until you have restored one, it is an untested claim.

Here is how they fail, and what a verification actually has to check.

The four ways a backup is silently useless

1. It is empty and nobody looked

A pg_dump that fails partway can still produce a file. A dump run against the wrong database name produces a valid, correct, empty file. Both look like success to anything checking for a non-zero exit or a file that exists.

The cheapest check in the world: look at the size, and compare it to yesterday's. A backup that is 400 bytes when yesterday's was 40 MB has told you everything. A backup that has been 400 bytes for six months has been telling you all along.

2. It contains data but not the things around it

pg_dump of a single database does not include roles, and does not include other databases. Restore it onto a fresh server and the tables arrive while every GRANT refers to a role that does not exist. Your app connects and gets permission denied on everything.

Extensions are the same story. If your schema depends on pgcrypto or uuid-ossp and the target does not have them, the restore fails partway, leaving you with some tables.

3. The flags were wrong for the restore you need

pg_dump produces different things depending on the format, and the mistake is usually discovered under pressure:

• Plain SQL restores with psql and is human-readable. It cannot be restored selectively, and it is slow for large databases.
• Custom format (-Fc) restores with pg_restore, supports parallelism and selective restore, and is what you want for anything sizeable.

Backing up in plain format because the tutorial did, and then discovering during an incident that you cannot restore a single table, is a specific and avoidable kind of bad day.

4. You cannot decrypt it

If backups are encrypted — and they should be — then the key is part of the backup. A key that lives only on the machine that was lost, or only in an environment variable of the service that is down, is a key you do not have when it matters.

What a real verification looks like

The test is not "does the file exist." It is restore it somewhere else and ask it a question.

# 1. Restore into a scratch database, not the live one
createdb verify_$(date +%Y%m%d)
pg_restore -d verify_$(date +%Y%m%d) --no-owner --no-privileges latest.dump

# 2. Ask it something only real data can answer
psql -d verify_$(date +%Y%m%d) -c "
  select
    (select count(*) from users)            as users,
    (select count(*) from orders)           as orders,
    (select max(created_at) from orders)    as newest_order;
"

# 3. Drop it
dropdb verify_$(date +%Y%m%d)

Step 2 is the entire point. A restore that completes without error still tells you nothing about whether the data is there. Row counts and a recency check do.

Three things worth asserting:

• Row counts are in the right order of magnitude. Not exact — data changes — but a table that had 200,000 rows and now has 12 is a failure.
• The newest record is recent. If the most recent order in your "nightly" backup is from March, your backup job stopped in March.
• The app can actually connect. Point a staging instance at the restored database and load one page.

Do it on a schedule, not on a resolution

The realistic cadence is monthly, automated, with the result somewhere you will notice. A calendar reminder to "test backups" is a reminder you will snooze.

What makes it stick is making the failure loud: if the verification query returns fewer rows than a threshold, that should page someone the same way a production error would. A backup system that fails silently is indistinguishable from no backup system, right up until it matters.

How Dockup handles this

Three decisions, each aimed at a specific failure above.

Backups go somewhere else. A backup on the same disk as the database is not a backup — it is a copy that dies with the disk. Dockup streams database backups directly to object storage as they are produced, so the file never depends on the host that made it.

They are encrypted, and the key is not on the box. Backups are encrypted with AES-256-GCM as they stream. The relevant part for recovery is that the key is platform-held rather than sitting in the environment of the service being backed up.

A backup that produced nothing is not recorded as a backup. This is the one that addresses the empty-file failure directly: if the dump exits non-zero, or produces zero bytes, the upload is deleted and the backup is recorded as failed. You do not end up with a list of green entries where one of them is a 400-byte file.

dockup db backup my-project/main-db --json     # take one now
dockup db backups my-project/main-db --json    # list them with sizes

The sizes in that listing are the cheapest health check you have. Watch them trend.

The uncomfortable question

If your production database were destroyed in the next ten minutes, how long would it take you to have it back, and how much would you have lost?

If you cannot answer both numbers, you do not have a backup strategy — you have backup files. The difference is entirely in whether anyone has ever done the restore.

Frequently asked questions

How often should I test a restore? Monthly is a reasonable default, automated rather than manual. The important part is that a failure is loud, not that the cadence is aggressive.

Why did my restore complete but produce no data? Usually the dump was taken against the wrong database or failed partway while still writing a file. Compare backup sizes over time — an empty dump is obvious in a size trend and invisible in a status column.

Should backups be encrypted? Yes, and the key must live somewhere that survives losing the machine. An encrypted backup whose key was on the lost server is not recoverable.

Is a snapshot the same as a backup? No. A volume snapshot captures the disk, including whatever state the database was in at that instant. A logical dump is consistent by construction. Most teams want both, for different failures.