A restore drill that passes tells you that one backup restored on one day. That's worth knowing, but most of what drills can teach you only shows up when you compare them: restore times that creep up quarter after quarter, a table whose row count stopped matching after a migration, a role someone creates by hand every time and never mentions. If the only record is a green check in CI, none of that survives.
We built backupdrill, which runs restore drills for Supabase projects. Whether the backup ran at all is a monitoring question, and I covered alerts when a Supabase backup to S3 fails or never runs in an earlier post. This log records which backup restored, what was checked, and how long it took.
The fields
We put these fields into a free restore drill log template on Tally, one form submission per drill.
| Field | Why it's there |
|---|---|
| Drill date, who ran it | So you can ask that person what they did by hand. |
| Database or project | One log usually covers several. |
| Which backup was restored | File name or snapshot time. "The latest" doesn't let anyone repeat the drill next month. |
| Where it was restored | Local Docker, staging, or a new cloud project. Note the target before comparing restore times. |
| Restore time in minutes | The number someone will ask you for during an outage. |
| What was checked | Restore finished without errors, table count matches, row counts on key tables, app connects, uploaded files are there. |
| Result | Passed, passed with issues, or failed. |
| What went wrong, what to fix before next time | Fixes to check at the next drill. |
Two of these need more than a line.
Where you restored it. A throwaway Docker Postgres with durability switched off restores much faster than a real database. In our own benchmark, the same 1 GB restore took 61 seconds with fsync, synchronous_commit and full_page_writes off, and 110 seconds on stock PostgreSQL settings. If one drill ran on a laptop and the next on staging, the timing difference may come from the target, not the backup.
Passed with issues. This is the result I'd read first. A drill where someone had to create a missing role, re-run a step or skip a table "because it's only logs" passed in the sense that the data came back. In a real recovery those steps happen under pressure, possibly by someone who has never seen them. Written down, they can be fixed or scripted before the next drill.
Restore time: measure it, don't derive it from the dump
Dump size alone doesn't tell you how long index builds and foreign-key checks will take. A custom-format dump carries index definitions, not indexes, so the restore builds every one of them from scratch and checks every foreign key again.
When we timed restores of a 10 GB PostgreSQL 17 database on a 2 vCPU / 8 GB VM, the dump was 1.74 GiB and a single-job pg_restore took 12.1 minutes. Across our drill restores of that database, 77% of the time (559 seconds) went to indexes, constraints and foreign keys. Loading all 18.8 million rows took 163.
The restore step isn't the whole recovery either. For the same database, the drill's restore step averaged 721.6 seconds, while the full drill, which also downloads the dump, starts the database and checks every table, took 795.3. Log the number that answers the question you'll get. For "how long until we're back", that's the end-to-end time, plus however long it takes to point the app at the new database.
Row counts: compare exactly, and against the backup
"Row counts spot-checked on key tables" is the weakest line on the checklist, because spot checks drift toward "looks about right". Two changes make it worth doing.
Compare against the backup, not production. Production has moved on since the dump was taken, so a difference of a few hundred rows doesn't show whether the restore lost data. pg_dump exports from one consistent snapshot, so the per-table counts in the dump are exactly what should come back.
For ordinary tables in the backup, including leaf partitions, backupdrill drill compares restored row counts with the counts read from the dump and fails on any difference. Materialized views, partitioned parents and extension-owned tables are excluded from this exact comparison; they are only checked for being non-empty. It doesn't query production for this; it reads the dump one extra time.
Let the drill write the machine half
Nobody should be typing restore seconds into a form when the tool already has them. backupdrill drill prints its checks to stderr and one JSON line to stdout, so you can keep the whole report:
backupdrill drill > drill-$(date -u +%F).json
The report holds the snapshot name, the result, every check with its detail, the restore time, and how many tables and rows were restored. Pulling out what the log needs:
jq -r '.report | [.snapshot, .pass, .restoreSeconds, .restoredTableCount, .restoredRowTotal] | @tsv' drill-2026-11-15.json
jq -r '.report.checks[] | "\(if .pass then "ok " else "FAIL" end) \(.name): \(.detail)"' drill-2026-11-15.json
The check names map onto the template's checklist: archive integrity (the dump's sha256 against the manifest), table count, no missing tables, populated tables came back and row counts match the backup. If you pass --check-cmd "npm run smoke", your own test runs against the restored copy and shows up as app checks. The command exits 1 when any check fails.
Add --record and the drill also writes a small file to your bucket under <prefix>/<project>.drills/, named after the time it finished and whether it passed. It looks like this:
{
"schemaVersion": 1,
"tool": "backupdrill-cli",
"toolVersion": "2.11.0",
"finishedAt": "2026-11-15T04:12:09.311Z",
"pass": true,
"snapshot": "2026-11-15T03-00-12-418Z",
"restoreSeconds": 69.1
}
If you build your own version of this, two of its decisions are worth copying. Each drill writes a new file instead of overwriting a "latest result" object, because two overlapping drills can finish in one order and write in the other, and an older pass would then hide a newer failure. And a failed drill records no restore time, since a number from a restore that stopped halfway would make the next estimate look better than it is. Check details stay out of the file too: a failure message can quote table data, and more people may be able to read the bucket than the drill logs. backupdrill exporter reads these files and exposes when the last drill ran and whether it passed as Prometheus metrics.
What the human half is for
The JSON records what this drill checked. Use the log to note checks you ran separately and anything you still need to test.
For snapshots without Auth users, our drill sandbox has no auth.users table. A profiles table with rows that reference it can pass the drill, because the foreign key is reported as a skipped Supabase-managed object, and then fail in a new project where auth.users is empty. backupdrill restore --dry-run against the real target catches it. The drill log is where "passed, but a restore into a new project needs the users copied in first" gets written down.
Storage is similar. Unless you pass --verify-all-files, the drill checksums a random sample of up to 100 files. If particular files matter to you, check those before you tick "uploaded files are there".
Reading the log
Every quarter or so, read the log top to bottom. Are restore minutes growing faster than the data? Is any "passed with issues" fix still open from more than one drill ago? When did each project last pass? If you don't use the CLI, our guide on how to test that a Supabase backup actually restores walks through the same drill by hand with Docker and pg_restore, and the same log works for it.
We built BackupDrill. The CLI used here is MIT licensed: https://github.com/backupdrill/cli
Top comments (0)