Infrastructure · 19 Oct 2026
The backup that restored perfectly and was wrong
A restore that finishes without errors proves the files were readable, not that the data is there. The checks that tell a real backup from a convincing one.
Emin C. Homalioglu · 5 min read · Updated 03 Nov 2026

A restore that finishes without errors proves one thing: the files were readable. It says nothing about whether the data is complete. Most backup failures live in that gap, and nobody sees it until the day the backup is needed.
The short version
If your company runs its own database, ask the person responsible three questions:
- When was a backup last restored into an empty system, and how long did it take?
- What was the result compared against?
- Who gets alerted when that comparison fails, and has it ever fired?
"We have backups" answers none of them. A date, a duration and a number answer all three.
What "restored" actually proved
When a restore exits with code zero, three things happened. The archive could be opened, the database accepted the statements in it, and the application started. None of that is a claim about contents. An empty database passes all three.
Five ways a backup is wrong and still restores
It was cut off. The dump ran while the disk filled up, or the process in the pipe died, or a timeout killed it. A truncated SQL dump restores every table up to the cut and nothing after it. Compressed archives often stay valid up to the break, so the tool reports little or nothing. You get a database that looks complete and ends in the middle of the alphabet.
It was never one moment. pg_dump reads all data from a single transaction snapshot (PostgreSQL documentation). A copy of the data directory of a running database does not. Its tables come from different moments, and after the restore there are orders without the customers they reference. Both are called "a backup".
It left out what the database points at. Uploaded files, generated documents and images usually live outside the database. The dump holds the paths only. The same goes for the keys that decrypt encrypted columns: the data comes back and stays unreadable.
It went into the wrong place. The restore ran into a database that already held data, so the check passed against yesterday's rows instead of the backup's. Restoring into a fresh, empty instance rules this out.
Constraints were off during the import. Many import paths disable foreign keys for speed. Orphaned rows go in without a complaint, and nothing checks them afterwards.
Count with the right tool
We ran into this ourselves during a database migration. For the first comparison between source and target we used the row estimates from Postgres's statistics view, pg_stat_user_tables.n_live_tup, because they come back instantly for every table. Several tables showed gaps large enough to look like lost data. Nothing was lost. The value is an estimate maintained by vacuum and analyze runs (PostgreSQL documentation), and on the source it was stale. A plain SELECT COUNT(*) on both sides matched exactly.
The reverse case is the dangerous one: a stale estimate that happens to match while real rows are missing. Since then every verification we run counts with COUNT(*), table by table, and treats statistics as a hint, never as evidence.
Checks that can fail
A check only means something if it compares against values recorded when the backup was taken. Counting afterwards only tells you what is there now.
- Rows per table, never a total. A total hides a missing table behind a large one.
- The newest timestamp per table. The maximum
created_athas to sit close to the backup time. This one query catches truncated dumps and stale snapshots. - Orphans. For every foreign key, count child rows without a parent. The answer is zero.
- A checksum over a stable slice, such as a hash of the ordered primary keys per table. Cheap, and it catches what counts miss.
- Files. Number of objects and total bytes against what the database references. Then open three of them and read them.
- One real path through the application. Log in, open the newest record, open the file attached to it. A script that only talks to the database will not notice a missing key.
Make the backup prove itself
Every backup gets a manifest with these values, written at the moment the backup is created. A scheduled job restores the latest backup into a throwaway instance, reads the manifest and compares. If a number differs, the job fails loudly and alerts someone. Weekly is enough for most systems, and the first failure this job reports is the cheapest one you will ever have.
Time it while you are at it. How much data you can afford to lose and how long you can afford to be down are estimates until a restore has been timed from start to finish: download the archive, restore it, rebuild the indexes, run the checks. Teams that have never done it often guess hours and then need a day.
A backup becomes a fact on the day someone restores it and counts what came back.
19 Oct 2026 · Emin C. Homalioglu


