Guides / Test that a backup restores

How to test that a PostgreSQL or MySQL backup restores

A dump that exits 0 tells you the dump tool exited 0. It does not tell you the file will load. Below are four checks, from the cheapest to the one that proves the most, with commands you can run today without buying anything.

What goes wrong with backups nobody restores

These are the failures that show up only at restore time:

Four levels of verification

Each level catches more than the one before it, and costs more to run.

Four levels of verification, each catching more. 1, the file exists and its checksum matches. 2, the archive is readable. 3, it restores into an empty database. 4, row counts match the source. 1. The file exists, checksum matches catches lost and cut-off uploads 2. The archive is readable catches cut-off files, missing tables 3. It restores into an empty database catches dumps that do not load 4. Row counts match the source catches tables that loaded empty or short
Each level costs more to run and proves more. Only levels 3 and 4 involve a restore.

1. The file exists and has not changed

Check the object is in storage, is a plausible size, and matches a checksum taken when it was written. This catches lost and truncated uploads. It says nothing about whether the contents can be restored.

2. The archive is readable

For a PostgreSQL custom-format dump, pg_restore --list prints the archive's table of contents without a database, so you can see whether the tables you expect are there at all. It reads only the table of contents at the start of the file. A file cut off after that still lists every table. To read every byte, restore to /dev/null:

pg_restore --list app.dump | grep "TABLE DATA"
pg_restore -f /dev/null app.dump
head -c 800000 app.dump > cut.dump

pg_restore --list cut.dump | grep "TABLE DATA"
3429; 0 16398 TABLE DATA public orders postgres
3430; 0 16404 TABLE DATA public payouts postgres

pg_restore -f /dev/null cut.dump; echo "exit $?"
pg_restore: error: could not read from input file: end of file
exit 1
A 1.5 MB dump of a 200,000-row table, cut to its first 800,000 bytes. The listing looks complete. Reading the data fails. Run in a postgres:17 container with pg_restore 17.11.

A plain SQL dump from mysqldump has no table of contents. The last line of a complete dump is a -- Dump completed comment (unless it was run with --skip-comments), so a file that does not end with it was cut short.

3. It restores into an empty database

This is the first level that proves a restore works. Create a scratch database, ideally on a different machine from production, and load the dump into it. Stop on the first error: a restore that carries on past errors and reports success is the failure you are testing for.

# PostgreSQL: one transaction, so a failure leaves nothing behind
createdb restore_check
pg_restore --exit-on-error --single-transaction --no-owner -d restore_check app.dump
# MySQL: the client stops at the first error by default
mysql -e "CREATE DATABASE restore_check"
mysql restore_check < app.sql

MySQL commits each CREATE TABLE as it runs, so a restore that fails part way leaves a half-built database behind. Drop it before trying again.

4. What came back matches what went in

A restore that loads without error can still be missing data: an empty table loads perfectly. Compare row counts per table against the source. Two details make this harder than it sounds.

PostgreSQL can do this: export a snapshot from a transaction, count inside that transaction, and have pg_dump read the same snapshot with --snapshot. Keep the first session open until the dump finishes.

# session 1, left open
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot(); -- e.g. 00000003-0000001B-1
SELECT format('SELECT %L, count(*) FROM %I.%I', schemaname || '.' || relname, schemaname, relname)
  FROM pg_stat_user_tables ORDER BY 1 \gexec
# session 2, same snapshot
pg_dump -Fc --snapshot=00000003-0000001B-1 -f app.dump app

Run the same counting query against restore_check and diff the two lists. mysqldump --single-transaction opens its own consistent snapshot and cannot share it, so for MySQL either count during a quiet window or count the rows in the dump itself.

Row counts do not prove every value is right, but they catch the failures that actually happen: a missing table, a table that loaded empty, a dump that stopped part way through a large table. If your application has a query that exercises what matters most, run it against the restored copy too.

Make it routine

A restore test you ran once proves the backup worked once. To keep it true:

How SafeGrd does it

SafeGrd runs these checks as a Fire Drill. Row counts are read from the dump stream as it is written, so they always describe exactly what the backup contains, busy tables included. A drill then comes in two depths:

Both run on your machine with your key. SafeGrd receives the result and never the data. Each result is kept as a dated attestation record. Every plan runs scheduled drills, and pricing lists how often, and how deep, each plan drills. The recovery runbooks cover restoring from a machine that is not the one that took the backup.

A Fire Drill and a restore of that backup. The host is standalone, so the CLI says the result was not reported to SafeGrd. On an enrolled host it is recorded and signed.