VaultKeep blog

A backup you've never restored is just a hope: how to test-restore Postgres properly

Checksums prove the file is intact. Only a restore proves it works. Here is the four-step drill we run every week, why row counts matter more than table counts, and how to do it yourself.

Every backup horror story has the same second act. The backups ran. The monitoring was green. The files were there. And then, on the worst day of the year, pg_restore printed something nobody had seen before, or finished cleanly with the largest table empty.

Backups fail silently in ways that checksums can't see:

• The dump was taken with a role that couldn't read three tables. pg_dump warned. Nobody reads warnings on a cron job.
• An extension the schema depends on isn't installed on the restore target, so half the objects fail to create and the data for them is skipped.
• The archive was truncated by a disk-full during upload. Postgres' custom format has no trailer, so the file looks fine until you reach the end.
• A large table was partially locked during the dump and came out short.
• Someone changed the connection string to the transaction pooler and pg_dump has been failing with a cryptic error for six weeks.

None of these change the file's SHA-256. All of them are caught by one thing: restoring the backup and comparing what you get to what you expected.

The four-step drill

This is the verification VaultKeep runs weekly on every project, including free ones. It's nothing exotic; the value is in doing it every week without fail.

1. Capture a manifest at backup time

Before you dump, record what the database looked like. At minimum: the list of tables and the row count of each.

select n.nspname as schema, c.relname as table,
       c.reltuples::bigint as estimated_rows
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r','p')
  and n.nspname not in ('pg_catalog','information_schema','pg_toast')
order by 1, 2;

reltuples is an estimate maintained by autovacuum; it's instant but can be stale. For tables under a few million rows, run an exact select count(*) and record which method you used. Store the manifest next to the backup, along with the dump tool version and the Postgres major version.

2. Verify integrity before you bother restoring

Hash the archive when you write it, hash it again when you read it back. If they differ, stop; the restore test would only tell you what you already know. This is cheap and catches truncated uploads and bit rot.

3. Restore into a throwaway cluster

Not your production database. Not a shared staging database that has other data in it. A fresh, empty Postgres of the same major version as the source (or newer). Locally that is one command:

docker run --rm -d --name verify -e POSTGRES_PASSWORD=x -p 54123:5432 postgres:17
pg_restore --no-owner --no-privileges --jobs 4 \
  --dbname "postgresql://postgres:x@localhost:54123/postgres" backup.dump

Capture pg_restore's exit code and its stderr. It exits non-zero for any error, including harmless ones like "role does not exist", so don't fail on the code alone; read the errors and decide which ones are acceptable for your schema.

4. Compare with the manifest

Now run the same inventory query against the restored cluster and diff:

• Table count. Every table in the manifest should exist. A missing table is a hard failure.
• Row counts. For tables where the manifest holds an exact count, the restored count must match exactly. For estimated counts, allow a tolerance (we use ±10% on large tables, and flag anything outside it).
• Spot checks that matter to you. Latest created_at in your most important table should be within minutes of the backup time. Foreign-key counts. Whatever would make you say "that's not my database."

Then tear the cluster down. Log the result somewhere durable, with the checks attached, so you can show it later.

Why row counts, not just table counts

Table counts catch missing objects. Row counts catch the failures that actually happen: partial dumps, permission gaps on specific tables, a restore that hit an error on one COPY and moved on. A restore that reports "142 of 142 tables" and 60% of the rows is the scenario the drill exists for.

How often

Weekly is the floor. The point isn't the schedule, it's that the drill runs without a human remembering, and that a failed drill wakes someone up. A verification that quietly fails is worse than none, because it manufactures confidence.

Doing this by hand versus not

Everything above fits in a shell script and a cron job, and if you have one project and enjoy operating things, do it. We described a free GitHub Actions setup that gets you most of the way.

VaultKeep exists for people who don't want to operate it: the drill runs weekly per project, the result shows up on the dashboard with the raw checks, a failure emails you, and there's a public badge that says when the last restore was verified. That badge is the whole product in one line: "restore verified 2 days ago" is a claim very few backup setups can actually make.