A backup you've never restored is just a rumour

I have 24 users and zero of them have deployed. If one of them deploys tomorrow and their server dies on Thursday, the only thing standing between them and a lost week is a backup I have never opened.

That sentence bothered me enough to change how I think about backups. Here is where I landed.

Everyone has a backup job. Almost nobody has a restore.

Ask a room of developers whether their database is backed up and most hands go up. Ask how many have restored one of those backups, start to finish, and the hands mostly come down.

A backup job that exits with code 0 tells you a file was written. It does not tell you the file is complete, that it matches the schema you are running today, or that it will load on a fresh machine at 2am. Until you have restored it, you are trusting a file you have never opened. That is a rumour with a timestamp.

What a real test looks like

A real test is short to describe:

1. Restore the backup into a scratch database that nothing else uses.
2. Query it.

The second step is the one people skip. A restore that finishes without errors can still hand you a database with missing rows, an empty table, or a sequence that restarted at 1. A backup that restores quietly with missing data is worse than no backup, because you will believe it.

So the check is not "did the restore command succeed". It is "do the numbers look like production". Row counts on your main tables, the newest timestamp in a table that gets written constantly, and one real query your app depends on. If those come back right, you have evidence. If you only checked the exit code, you have a hope.

The loop I run

This is the loop CrescoDB uses for apps deployed to a user's own server:

1. A nightly dump is taken on the server.
2. That dump is restored into a scratch database on the same server and checked.
3. The dump is copied off the box to R2.
4. It is pulled back down from R2 and restored again somewhere else.

To be clear about where the data lives: backups sit in R2 as well as on the user's server. Your data is on your own machine, and a copy of the backup also lives in object storage. I would rather say that plainly than let anyone assume the backup never leaves the box.

The second restore is the point of step four. Restoring on the same server proves the dump is readable. Restoring a copy that came back from storage, on a different machine, proves the copy survived the trip and that you can recover when the original server is gone, which is the case that actually matters.

Automatic backups and scheduled restore checks are part of the paid plans, not the free tier.

What only shows up on restore

Backups tend to fail in quiet, boring ways. These are the ones worth checking for, and what to do about each:

• Missing roles or ownership. A dump may reference database roles that do not exist on the new machine, so objects fail to restore or end up owned by the wrong user. Create the roles first, or dump with ownership handled deliberately.
• Extensions. If your schema needs an extension and the new server does not have it installed, parts of the restore fail. Keep a list of required extensions next to the backup job.
• Version mismatch. Restoring a dump from a newer major version into an older server often does not work. Keep the restore target on the same major version, or newer.
• Truncated files. A dump cut short by a full disk or a killed process can look like a normal file. Check the size against previous nights and let the restore fail loudly if the file is incomplete.
• Sequences and counters. Row data can come back while sequences reset, which breaks the next insert. Test an insert after restoring, not just a select.
• Permissions on the backup itself. A backup readable by the wrong users is its own problem. Check who can read the file, and treat it like production data, because it is.

None of this is exotic. It simply never gets discovered until someone restores, which is the whole argument for restoring on a schedule instead of during an outage.

Test your own tonight

Three commands, using Postgres as the example. Swap in your own names and paths.

Take a dump:

pg_dump -Fc -f /tmp/backup.dump your_database

Restore it into a scratch database:

createdb scratch_check && pg_restore --no-owner -d scratch_check /tmp/backup.dump

Compare it against production:

psql -d scratch_check -c "SELECT count(*) FROM your_main_table;"

Run the same count against your live database. If the numbers match and a recent row is present, you now know something you did not know this morning. Drop the scratch database when you are done:

dropdb scratch_check

Put those four lines in a script, run it from cron, and make it complain when the counts disagree. That is most of the work.

The short version

A backup is only a backup after it has been restored and queried. Do it into a scratch database, check real numbers, and do it again from a copy that came back from somewhere else. If you want to see how this fits into deploying a backend to your own server, CrescoDB is at crescodb.com, and the free audit page is at crescodb.com/audit.

And if you have a backup job running right now, go restore last night's. I would like to hear what you find.