A real restore drill, start to finish

Marketing pages show mock terminals. These are the real transcripts of a BackupDrill restore drill, run on 2026-10-11 with CLI 2.2.3 against a 197 MB Supabase-shaped Postgres 17 database and 250 files in an S3-compatible store — plus the script that reproduces it, so you do not have to take our word for any of it. Two cosmetic edits: long s3:// paths are shortened, and the machine-readable JSON output is omitted. Every terminal line is here, warnings included.

Updated 2026-10-11. This page first showed a run from 2026-07-25 on CLI 2.1.0. We re-ran it because the engine changed: that run listed five of the fixture’s RLS policies as skipped, and the drill sandbox now carries stub Supabase roles, so those policies restore. Re-running also turned up a bug — on a machine whose language is not English, a translated pg_restore made a healthy drill fail — fixed in 2.2.3.

Read this first. The database here is a fixture we built and seeded, not a customer’s production project — we are not going to publish someone else’s data, and we are not going to dress up a demo as a case study. It is a local Postgres 17 shaped like a Supabase project, and the files live in a local S3-compatible store standing in for Supabase Storage; no hosted Supabase project took part in this run. That has one visible consequence — the ! line about storage.buckets in the transcript, explained under “What it does not prove” below. What is real is the engine, the checks, and every number below.

The backup

One command wrote a dump, a full copy of the Storage files, a checksummed manifest, and a self-contained recovery runbook into an S3-compatible bucket:

$ backupdrill backup --config backupdrill.config.json

→ Inspecting database…
✓ Postgres 17.6 — schema(s) public: 13 tables, ~1,745,499 rows
→ Dumping → s3://acme-backups/backupdrill/acme-prod/2026-10-11T09-15-38-493Z/dump.pgcustom
✓ Database dumped (21.6 MB, sha256 f21e7a064d2c…)
→ Syncing Storage files…
→   user-uploads: 250 files
! Bucket attributes not captured (relation "storage.buckets" does not exist); this manifest will record them as absent — restore will say so instead of guessing.
✓ Storage synced (250 files, 33.6 MB)
✓ Recovery runbook written → …/RECOVERY.md
✓ Manifest written → …/manifest.json
✓ Backup complete — 13 tables + 250 Storage files.

253 objects landed in the bucket: the dump, the manifest, the runbook, and the 250 files. Nothing proprietary — dump.pgcustom is plain pg_dump output that stock pg_restore reads with or without us.

The drill

The drill downloads that snapshot into a throwaway sandbox, provisions a temporary Postgres, restores into it, verifies the result against the manifest, and destroys the sandbox:

$ backupdrill drill --config backupdrill.config.json \
    --verify-all-files --check-cmd "node check-invariants.mjs"

→ Drilling snapshot 2026-10-11T09-15-38-493Z
→ Downloading dump…
→ Verifying Storage files…
→ Starting ephemeral postgres:17-alpine…
→ Restoring into ephemeral Postgres…
→ Running app checks…
row counts: {"orders":"200000","order_items":"600000","customers":"20000","attachments":"250","events":"300000"}
orphan order_items: 0 | orphan orders: 0
revenue orders vs matview: 25099150000 vs 25099150000
all business invariants hold
→ Tearing down ephemeral Postgres…

  ✓ archive integrity — sha256 matches (f21e7a064d2c…)
  ✓ storage file integrity — all 250 files match their manifest checksums
  ✓ sandbox extensions — installed pg_trgm, pgcrypto
  ✓ pg_restore — completed in 2.2s
  ✓ post-data objects — all post-data objects restored
  ✓ table count — restored 13, manifest 13
  ✓ no missing tables — all manifest tables present
  ✓ populated tables came back — no populated table restored empty
  ✓ app checks — command exited 0 in 0.1s

✓ Drill PASSED — snapshot 2026-10-11T09-15-38-493Z, 13 tables / 1,495,250 rows restored in 2.2s

What each check actually proves

✓ archive integrity — sha256 matches (f21e7a064d2c…)

The dump we downloaded from the bucket is byte-for-byte the one the backup wrote. A truncated upload or a silently corrupted object fails here, before anything is restored.

✓ storage file integrity — all 250 files match their manifest checksums

Each backed-up file was read back out of the bucket and re-hashed. This run used the CLI's --verify-all-files, so all 250 were checked; a hosted scheduled drill samples up to 100 per drill and has no setting to raise that, so above 100 files a hosted report proves less than this run did.

✓ sandbox extensions — installed pg_trgm, pgcrypto

The extensions the dump depends on were installed before restoring. Without this, an index built on a trigram operator class fails to restore and the failure looks like data loss.

✓ pg_restore — completed in 2.2s

Schema and data restored with no unexpected errors. A version-pinned allow-list covers the handful that are expected against any Supabase-shaped dump — “schema public already exists”, for instance — and anything outside it fails the drill. That is stricter than a bare exit code, which is never clean on a real restore.

✓ post-data objects — all post-data objects restored

Your indexes, constraints, triggers and RLS policies came back. The policies reference Supabase roles (authenticated, anon) that a bare Postgres does not have, so the drill sandbox creates stub versions of those roles and of the auth.uid()-style functions first. Objects that point at Supabase-managed tables such as auth.users still could not be created there; they would be listed as expected skips rather than counted as passes. This fixture has none.

✓ table count — restored 13, manifest 13

The restore produced the same number of tables, partitions and materialized views the manifest recorded. Equal counts alone do not prove they are the same objects — that is the next check's job — and functions, views, sequences and types are restored but never counted here.

✓ no missing tables — all manifest tables present

Every table recorded at backup time exists by name in the restored database.

✓ populated tables came back — no populated table restored empty

The nastiest silent failure — a restore that produces the right schema with empty tables — is caught. Tables that were already empty at backup time are not held against the drill.

✓ app checks — command exited 0 in 0.1s

The hook where your own assertions go: a command you supply runs on your machine against the restored sandbox, and a non-zero exit fails the drill. Ours asserts exact row counts for five of the thirteen tables plus an exact revenue total — so loss in those five fails the drill, while loss in one of the other eight would still have to be caught by the non-empty check above. The assertions worth putting here are the ones only your code knows, and no hosted drill runs them: --check-cmd is CLI-only.

What it does not prove

A passed drill means this snapshot restored successfully in the verification environment at that time. It is strong evidence, not an unconditional guarantee about a future restore. Specifically:

It is not an RLS test. The fixture’s RLS policies are restored — the sandbox creates stub authenticated and anon roles so they can be — but nothing exercises them. Verifying RLS behaviour needs an authenticated client stack with real JWTs — no restore sandbox can fake that, so we do not claim it.
It is not a row-for-row comparison with production. The manifest’s row counts are planner estimates, so the check is “every table that had rows came back non-empty”, not “the counts match”. The --check-cmd hook above is how you add the assertions only your code can make.
Storage verification reads the backup copies. It compares what is in your bucket against the manifest written when the backup ran — it does not re-read the live files from Supabase.
One thing this fixture could not exercise. Because the source is a local Postgres rather than a hosted project, the backup could not read storage.buckets — that is the ! line in the transcript above. Bucket attributes (public flag, size limit, allowed MIME types) were recorded as absent, and a restore from this snapshot would say so rather than guess. Against a real Supabase project they are captured and rebuilt.
The dump covers the public schema by default. Supabase-managed schemas, Auth users, Edge Function source and project configuration are not in the snapshot and therefore not in the drill. The coverage matrix is the full list.

What changes if you run it again

We ran this three times in July and again in October. The distinction matters more than any single number, because it is the reason the drill measures rather than trusts:

Value	Reruns	Why
Every number published on this page: rows restored (1,495,250), table count, the invariant totals, per-file sha256	Stable	The drill counts rows in the restored database, and the row values and file bytes those figures depend on are generated deterministically.
The manifest's estimated row count	Drifts	It is n_live_tup, a planner estimate. Across three runs of the same fixture in July we saw 1,495,250, then 1,745,499, then 1,495,499; the October run reported 1,745,499.
Dump sha256	Changes	Two reasons: the fixture's uuid primary keys are random and seven tables stamp now(), so the bytes genuinely differ — and pg_dump writes a creation timestamp into the archive header, so even identical data would not hash the same.
Restore seconds	Changes	Hardware.

On one of those runs the manifest claimed 250,000 more rows than the database actually held, and the drill’s own count was right anyway. That is the whole argument for verifying a restore instead of reading a backup’s self-report.

And 2.2 seconds is hardware, not a product claim — and it times the pg_restore phase alone, not the whole recovery: downloading the snapshot and checksumming the files happen before the clock starts. A real recovery also pays for the download, and yours depends on dump size and disk. Which is exactly why it is worth measuring on a schedule instead of guessing.

Run it yourself

The fixture, the seed, the invariant script and the exact commands are in the open-source repository. It starts two throwaway fixture containers, and the drill starts a third for its sandbox, so three run at peak; all are removed when it finishes. Nothing touches a real project.

git clone https://github.com/backupdrill/cli
cd cli && pnpm install --frozen-lockfile && pnpm build
./examples/reproducible-drill/run.sh

examples/reproducible-drill → has the schema, the seed, and a note on which numbers should match ours and which cannot.

This drill was run by hand with the CLI, and two lines above depend on that: --check-cmd adds the app-checks line outright, and --verify-all-files widens the Storage check from a sample to all 250 files. Neither flag has a hosted equivalent. The hosted service runs the same engine on a schedule — weekly on every paid plan, once on the Free plan’s first backup — and emails you the report, with the seven structural checks and Storage sampled up to 100 files. The value it adds is that it happens whether anyone remembers or not. Start free or read how to test a restore by hand.