Backups and recovery

When a restore succeeds and hands you the wrong data

2026-11-09 · 9 min read

The scariest backup failure is not the one that errors. It is the one that finishes, starts the database, lets you log in and quietly gives you less than you asked for. Every command exited 0, the app is up, and the orders from the last three weeks are not there.

We have hit several versions of this while testing backup and restore scripts. They share one property: the tooling had no way to know what "right" meant, so it reported success for "something came back". This post is about teaching your restore what right looks like.

Who needs this

A shop with a nightly Postgres dump and a growing orders table. Restore finishes, the site loads, products display. Orders look plausible because the newest ones are exactly what nobody scrolls to. Whether the restore is eight hours or eight weeks old is invisible without a freshness check.

A small SaaS on SQLite that moved its database file. The app now writes to /var/lib/app/app.db, but the backup job still dumps the old /var/www/app/data/app.db. The old file exists, is a valid database and passes PRAGMA integrity_check. Every backup is perfect and useless, and the integrity check cannot tell.

A team with point-in-time recovery and a nightly cleanup script. PITR needs an unbroken chain of WAL files from the base backup to the target. One deleted segment in the middle, and recovery can stop at the gap and still bring the database up. We reproduced exactly this, below.

A solo developer who restores "latest" from a repository shared by several projects. restic restore latest takes the newest snapshot that matches your filters. With no filters, that may be a different project's snapshot.

If any of these sounds close to your setup, the cheap fix is the same: compare the restore with something you know to be true.

Six ways a restore lies

1. The WAL chain has a hole

We deleted one WAL segment from the middle of a test archive, switched the safety check off (the --allow-gap flag of the restore script we were testing) and asked for the latest state. Recovery replayed up to the gap and stopped. The database came up. A look at the table we were checking found 200 rows where there should have been 410. No error on the terminal.

Archives lose files in ordinary ways: a cleanup script with the wrong cut-off, a disk that filled up and then recovered, a sync job that skipped a file. You can run for months with a hole and never know, because you only walk the chain when you restore.

The defence has two parts:

• The restore refuses to start when the chain is incomplete, naming the missing segment and exiting with a distinct non-zero code.
• A separate daily job walks the archive, so you find the hole while you still have the live database.

How the chain works is covered in Postgres WAL archiving from scratch, and what you can and cannot lose is in PITR: how much can you lose.

2. The dump is valid but empty or wrong

PRAGMA integrity_check returns ok for a database with no tables. pg_restore -l lists a table of contents for a dump of an empty schema. Both checks answer "is this file well-formed", which is a necessary property and nowhere near sufficient.

This is what happens when the backup points at the wrong path, the wrong database name, or a database that was recreated by a migration run. The fix is to assert content, not format:

# restored SQLite dump: every table must have rows, and the newest row must be recent
f=/tmp/restore/var/backups/dumps/sqlite-app.db
rows=$(sqlite3 "$f" 'select count(*) from orders')
newest=$(sqlite3 "$f" 'select max(created_at) from orders')
[ "$rows" -gt 0 ] || { echo "orders is empty"; exit 1; }
echo "orders: $rows rows, newest $newest"

3. The snapshot is stale because nothing noticed backups stopped

restic restore latest restores the newest snapshot, whatever its age. If the nightly job has failed for two weeks, you get a two-week-old copy and a success message. Print the snapshot time and refuse to accept it past a limit; monitoring backups so silence is not success has the age check.

4. You restored a different snapshot than you meant to

restic snapshots --tag app --latest 3
restic restore latest --tag app --target /tmp/restore

Without --tag, --host or --path, "latest" means latest in the whole repository. Share one repository between two servers and this is a real way to restore the other server's data. Always filter, and print which snapshot ID you used.

5. Old dumps rode along in the snapshot

A dump directory that is not cleaned at the start of each run keeps old files. In our tests a database that had been dropped from the backup config kept appearing in new snapshots, because its last dump was still on disk. The reverse bites too: a dump that fails to overwrite silently leaves yesterday's file in place, and today's snapshot contains yesterday's data under today's date. Clean the directory first, and let a failed dump stop the script.

6. Pieces that are not data went missing

Roles are not in pg_dump -Fc, so a restore onto a fresh cluster fails loudly with role "shop" does not exist, which is the good kind of failure. The quiet kinds are an app that restores fine and then cannot read its secrets because the env file was never backed up, a restored uploads folder owned by the wrong user, and file permissions that differ from production. Compare ownership and modes as well as contents on the drill.

The check that fixes most of this

A restore test that passes should print numbers a human can compare with production. Ours prints, per table, a row count:

drill: restored 4 files
drill: sqlite-app.db: integrity ok, tables:
    notes: 3 rows
drill: pg appdb restored into scratch db drill_appdb_1158, rows per table:
    public.orders: 5000 rows
drill: OK

Then you, or a script, compare these with the live database. A table that is empty or much smaller than live is a failed backup even though every command succeeded. Automate the comparison with a tolerance, since the live database has grown since last night:

live=$(sudo -u postgres psql -At -d appdb -c 'select count(*) from orders')
restored=$(sudo -u postgres psql -At -d scratch -c 'select count(*) from orders')
# the restore may be at most 5% behind live
min=$(( live * 95 / 100 ))
[ "$restored" -ge "$min" ] || { echo "restored $restored vs live $live"; exit 1; }

The threshold is yours to pick; the point is that something fails when the gap is wrong. For append-only tables, add a freshness test on the newest timestamp: if max(created_at) is more than 36 hours old on a table that gets rows every day, the restore is stale.

Rule of thumb: a restore that cannot fail is not a test.

A second, cheaper signal is a canary row. Have the app (or a cron job) write a row with the current date into a small heartbeat table once a day. After any restore, the newest heartbeat tells you how old the data is without understanding your schema.

Make strict the default

The lesson we took for our own scripts:

1. A restore that exits 0 has not proven it returned what you wanted. Count rows, compare a checksum, look at the newest timestamp.
2. Verify the archive on a schedule. Do not discover the gap during the incident.
3. Make the tool strict by default. Loosening it, for instance restoring to a time before a known gap, should be a flag you type on purpose.

For the Postgres chain case, the flag is worth having: if you know your target is before the hole, restoring to that exact time is legitimate and the database is correct at that moment. Combined with "latest" it produces the stale database described above. Restoring to a timestamp is walked through in restore Postgres to a timestamp.

Let the app vouch for the restore

Database-level checks stop at the database. The strongest signal is one level up: start the restored application and ask it. If your /health endpoint can report a few counts, as the handbook's demo app does (notes, orders, uploads), a full rebuild can compare that JSON with the numbers recorded before the disaster. The same drill also hashes every uploaded file and compares one checksum, which catches a partially restored uploads folder that no row count would notice.

For a real incident, add a human step. Log in as a test user, open the newest record you remember creating, and check that it exists. It takes a minute and it is the one check that does not depend on your tooling being right about what "right" means. Write that record's name into the runbook before you need it; a restore runbook is the right place for it.

Mistakes we see

• Trusting the exit code of the last command. Pipelines hide failures without set -o pipefail, and some tools exit 0 on partial work.
• Verifying structure instead of content. integrity_check, pg_restore -l and restic check all pass on a perfectly consistent, perfectly useless backup.
• Testing the restore only right after setting up backups. The day-one restore passes. The failure comes three months later when someone moves a path or a disk fills.
• Comparing the restore with nothing. A count printed to a log nobody compares with live is decoration.
• Restoring over production to "test". Always restore to a temp directory and a scratch database.

Which check catches which failure

No single check is enough, which is why the drill runs several.

FAQ

How do I know my restore is complete?

You do not, strictly; you gain confidence. Row counts per table compared with live, a freshness check on the newest record and a checksum of any files you can hash together cover most real failures. Do these on every drill and write the output down.

Does restic check prove the backup is good?

It proves the repository is internally consistent and, with --read-data-subset, that a sample of the stored data is readable. It does not know what is inside your snapshot, so a snapshot of the wrong directory passes.

How often should I run a restore test?

Weekly for the cheap automated one that restores to a temp directory and a scratch database, and a full rebuild on a clean server a few times a year. The recovery drill post shows the full version.

What is a good tolerance when comparing restored and live row counts?

It depends on how fast your data grows. For a nightly backup, a table that grows by a few percent per day tolerates 5 to 10 percent behind live. For tables that rarely change, require an exact match. Pick numbers from your own growth, not from ours.

Can I detect a WAL gap without doing a restore?

Yes. List the archived segments and check that the names run in sequence from the base backup's start to the newest one. A daily job that does this finds the hole while the live database still holds the data.

The handbook's restore drill restores the latest snapshot into a temp directory, integrity-checks every SQLite dump, restores every Postgres dump into a scratch database and prints a count per table. It is part of the Small App on One VPS handbook.

backuppostgressqlite