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. This guide covers what to check, from the cheapest check 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:

The wrong thing was dumped. A connection string pointing at a replica that stopped replicating, a staging database, or one schema of several.
The file is incomplete. A disk filled, a pipe broke, or an upload was cut off, and nothing in the chain checked the exit status of every stage.
The dump does not load. A missing extension or role on the target, a server version the dump was not written for, or an object the dump tool cannot express in a form the server will accept.
The key is gone. An encrypted backup is only as good as your access to the key. If the only copy lived on the server you are recovering, you have no backup.
Nobody knows how. The restore works, but the steps live in one person's head, and it takes a day to rediscover them during an outage.

Four levels of verification

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

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 reads the archive's table of contents without a database. It fails on a corrupt or truncated archive, and the listing shows whether the tables you expect are there at all.

pg_restore --list app.dump | grep "TABLE DATA"

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.

Estimates are not counts. pg_stat_user_tables.n_live_tup in PostgreSQL and information_schema.tables.table_rows for InnoDB are estimates. Use count(*).
A busy source moves. Counts taken a minute after the dump started will not match the dump on any table that is being written to. The counts have to come from the same snapshot the dump read.

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:

Schedule it. Test the latest backup on a fixed cadence, and alert when a test fails or does not run.
Run it somewhere else. The test machine should hold nothing but read access to the backup storage and the decryption key. If the restore only works on the production host, you are testing the production host.
Keep the result. Record the date, the backup tested, the counts and the outcome. An auditor or a customer's security questionnaire will ask for it, and “we check it sometimes” is not an answer either will accept.
Write the runbook while nothing is on fire. The commands above, with your hostnames in them, in a place people can find during an outage.

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:

In memory (safegrd verify). The snapshot is decrypted and replayed without any database, and every table's row count is held to the manifest. It checks the data, not that the schema loads.
Into a sandbox (safegrd verify --sandbox-target). The snapshot is loaded into an empty PostgreSQL or MySQL database you provide, and the counts are checked there. This is level 4 above, end to end.

Both run on your machine with your key, and the remote server receives the result, never the data. Each result is kept as a dated attestation record. Every plan runs scheduled drills; 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.