A real restore drill, start to finish

These are the real terminal transcripts of a BackupDrill restore drill, run on 2026-11-07 with CLI 2.11.0 against a 199 MB Supabase-shaped Postgres 17 database with 1,000 Supabase Auth users and 250 files in an S3-compatible store. After the drill, the same snapshot was restored into a fresh Supabase Postgres running the Supabase Auth server, and a user signed in with the password they had before the backup. The script that reproduces all of it is linked below, so you do not have to take our word for any of it. We made three cosmetic edits: long s3:// and temporary directory paths are shortened, the machine-readable JSON output is omitted, and the restore’s dry run is left out for length. Every other terminal line is here, warnings included.

Updated 2026-11-07. This page first showed a run from 2026-07-25 on CLI 2.1.0, then one from 2026-10-11 on 2.2.3. On 2026-11-06 we ran it on 2.10.0, because a snapshot now holds more than the database: from 2.9.0 it records the grants to Supabase’s API roles, and from 2.10.0 it backs up the Auth users. We added 1,000 Auth users in Supabase Auth’s own table layout, a profiles table with a foreign key into auth.users and a sign-up trigger, and the grants a project writes by hand. That run also restored the snapshot into a fresh project and signed in. The run below is from the next day, on 2.11.0, with the same fixture. 2.11.0 compares the row count of each ordinary table with the backup, which adds one line to the drill and one to the restore.

Read this first. The database here is a fixture we built and seeded, not a customer’s production project. We won’t publish someone else’s data, and we won’t pass off 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. The restore at the end goes into Supabase’s own Postgres image with GoTrue, the Supabase Auth server, both running on the same machine. No hosted Supabase project took part in this run. That has one visible effect: 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, the Auth users, 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: 14 tables, ~1,496,499 rows
→ Dumping → s3://acme-backups/backupdrill/acme-prod/2026-11-07T11-35-38-795Z/dump.pgcustom
✓ Database dumped (21.6 MB, sha256 c5617d2f2cdf…)
✓ Auth users backed up (1,000 users, 1,000 sign-in identities, 0.2 MB)
→ 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 — 14 tables + 250 Storage files.

254 objects landed in the bucket: the dump, auth.dump with the Auth users, the manifest, the runbook, and the 250 files. Nothing is proprietary: both dumps are plain pg_dump output that standard 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-11-07T11-35-38-795Z
→ 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
✓ Auth users: 1,000 users restored (1,000 identities); 1 foreign key(s) into the Auth tables created; 1 trigger(s) recreated; a test sign-up worked
→ Tearing down ephemeral Postgres…

  ✓ archive integrity — sha256 matches (c5617d2f2cdf…)
  ✓ storage file integrity — all 250 files match their manifest checksums
  ✓ sandbox extensions — installed pg_trgm, pgcrypto
  ✓ pg_restore — completed in 1.8s (2 parallel jobs)
  ✓ post-data objects — user objects restored; 1 Supabase-managed object(s) skipped (they reference tables such as auth.users, which the drill sandbox doesn't have; restoring into a new project needs the rows they point at to be there first)
  ✓ table count — restored 14, manifest 14
  ✓ no missing tables — all manifest tables present
  ✓ populated tables came back — no populated table restored empty
  ✓ row counts match the backup — all 12 tables match the backup exactly (1,476,250 rows)
  ✓ API grants — access for anon, authenticated and service_role set as it was at backup time on 24 object(s)
  ✓ app checks — command exited 0 in 0.1s

✓ Drill PASSED — snapshot 2026-11-07T11-35-38-795Z, 14 tables / 1,496,250 rows restored in 1.8s

What each check actually proves

✓ archive integrity — sha256 matches (c5617d2f2cdf…)

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 1.8s (2 parallel jobs)

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 — user objects restored; 1 Supabase-managed object(s) skipped (they reference tables such as auth.users, which the drill sandbox doesn't have; restoring into a new project needs the rows they point at to be there first)

Your indexes, constraints, triggers and RLS policies came back. The policies use Supabase roles (authenticated, anon) and auth.uid(), which a bare Postgres does not have, so the drill sandbox creates stub versions of them first. The one object it could not create at this point is the foreign key from profiles to auth.users: there is no auth.users in the sandbox yet, so the drill lists it as skipped instead of counting it as a pass. The Auth users line below adds it.

✓ table count — restored 14, manifest 14

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; the next check covers that. Functions, views, sequences and types are restored but not 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

This catches one of the worst failures that raises no error: a restore that produces the right schema with empty tables. Tables that were already empty at backup time are not held against the drill.

✓ row counts match the backup — all 12 tables match the backup exactly (1,476,250 rows)

The drill counted the rows of each ordinary table, partitions included, in the dump file itself, then ran count(*) on the restored copy. One row more or fewer in any of them fails the drill. Twelve of the fourteen are compared: the materialized view order_totals gets its 20,000 rows from a REFRESH rather than from the dump, and the partitioned parent events holds no rows of its own, so those two stay under the non-empty check above. That leaves 1,476,250 rows to compare out of the 1,496,250 restored. It doesn't check what is in the rows, and it doesn't compare with the live database.

✓ API grants — access for anon, authenticated and service_role set as it was at backup time on 24 object(s)

The backup recorded what anon, authenticated and service_role were allowed to do on the schema and on each table, sequence and function in it. The drill set exactly that in the sandbox and read it back. The dump itself carries no grants, and since October 30, 2026 Supabase no longer grants new tables in public to these roles by default, so without this step Data API requests to the restored tables get permission denied. Grants to other roles are not recorded.

✓ 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 checks five tables against the row counts the seed wrote, looks for orphaned orders and order items, and compares the revenue total in orders with the materialized view. No hosted drill runs it: --check-cmd is CLI-only.

✓ Auth users: 1,000 users restored (1,000 identities); 1 foreign key(s) into the Auth tables created; 1 trigger(s) recreated; a test sign-up worked

This one runs after pass or fail has been decided, and it never changes the verdict. The drill restored auth.dump into the same sandbox and found the 1,000 users and 1,000 sign-in identities the backup counted. It then added the foreign key the post-data check skipped and recreated the on_auth_user_created trigger. Finally it inserted a test user as GoTrue's database role, the way an email sign-up does, inside a transaction it rolled back; the trigger ran without an error. The transcript prints this line before the list, because the list is written out once everything has finished.

Restore into a fresh project, then sign in

A drill restores into a throwaway Postgres and stops there. This run went one step further. The target was Supabase’s own Postgres image with GoTrue running against it, so the Auth tables existed and were empty, as they are in a new project. The snapshot went in with backupdrill restore, the command path C of the restore docs uses, after a --dry-run that wrote nothing:

$ backupdrill restore --config backupdrill.config.json --database \
    --storage-dir …/recovered-files --confirm-target 127.0.0.1

→ Checking target is empty…
→ Downloading dump…
→ Downloading the Auth archive…
→ Checking foreign keys that point outside the backup…
✓ references outside the backup: no foreign key points outside public
→ Checking 2 extension(s) can be installed on target…
→ Restoring the Auth users and the database into target in one transaction…
→ Verifying restored tables…
✓ table count: restored 14, manifest 14
✓ no missing tables: all manifest tables present
✓ populated tables came back: no populated table restored empty
✓ row counts match the backup: all 12 tables match the backup exactly (1,476,250 rows)
✓ API grants: access for anon, authenticated and service_role set as it was at backup time on 24 object(s)
✓ Database restored (14 tables, 1,496,250 rows)
✓ Auth users restored: 1,000 users (1,000 identities); trigger(s) recreated: on_auth_user_created
! Everyone signs in again: sessions aren't in the snapshot. Reconfigure Auth in the Supabase dashboard before switching your app over: sign-in providers (OAuth client IDs and secrets), SMTP, redirect URLs, the site URL and SSO.
→ Downloading 250 Storage files → …/recovered-files
✓ Storage files written to …/recovered-files
✓ Restore complete — snapshot 2026-11-07T11-35-38-795Z: database restored, 250 Storage files written to …/recovered-files

Then the script went to GoTrue’s own API on the restored project, the same endpoints supabase-js calls, and checked the database:

==> sign in on the restored project
  ✓ user1@example.com signed in with the password it had before the backup (HTTP 200, access token issued)
  ✓ the same account with a wrong password is refused (HTTP 400)
  ✓ a new sign-up through GoTrue got its profiles row from the restored trigger
  ✓ password-reset links pending at backup time: 5 at the source, 0 after the restore
  • The password hashes survived. The password was set before the backup, and the restored project has a different JWT secret. GoTrue checked it against the hash that came out of auth.dump and issued an access token; a wrong password got a 400.
  • The sign-up trigger came back with the users. A brand-new user signed up through GoTrue and got a profiles row, which only the restored on_auth_user_created trigger writes. Without it, the restore would look fine until the next person signed up.
  • Links that were pending got cleared. Five users had a password-reset link outstanding when the backup ran. After the restore there are none, so an old email can’t reset a password on the new project.

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. Testing RLS takes requests to the Data API with real users’ JWTs, and this run makes none: the sign-in step stops once the access token is issued.
  • It is not a comparison with the live database. Row counts are compared with the dump, which pg_dump wrote from one consistent snapshot, so rows committed after that snapshot was taken are in neither count. And the drill counts rows; it doesn’t compare what is in them. 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. Against a real Supabase project they are captured and rebuilt.
  • The sign-in ran against Supabase’s software, not its platform. The target was the same Postgres image and Auth server a project runs, started on one machine. Only password sign-in was tried: the 400 users who signed up with Google were restored, but signing them in needs a Google OAuth client on the target, which a new project has to be given again anyway.
  • The snapshot is the public schema and the Auth users, not the whole project. Sessions, MFA factors, Auth settings (sign-in providers, SMTP, redirect URLs), other Supabase-managed schemas, Edge Function source and project configuration are not in it, so neither the drill nor the restore brings them back. The coverage matrix is the full list.

What changes if you run it again

We ran this three times in July, again in October, and twice in November: once after adding the Auth users, and once for the row-count check. Some values stay the same between runs and some don’t, and that difference is why the drill counts for itself instead of trusting the manifest:

ValueRerunsWhy
Every number published on this page: rows restored (1,496,250) and compared (1,476,250), table count, Auth user and identity counts, the invariant totals, per-file sha256StableThe drill counts rows in the dump and in the restored database, and the seed fixes what those figures depend on: the row values, how many Auth users and identities there are, and the file bytes.
The manifest's estimated row countDriftsIt is n_live_tup, a planner estimate. On the earlier 13-table fixture we saw 1,495,250, then 1,745,499, then 1,495,499 across three runs in July, and 1,745,499 in October. This run reported 1,496,499 against 1,496,250 measured. It only decides which tables the non-empty check expects rows in; the row-count check counts the dump instead.
Dump and auth.dump sha256ChangesTwo reasons: the fixture's uuid primary keys and bcrypt salts are random and eight tables stamp now(), so the bytes really do differ. pg_dump also writes a creation timestamp into the archive header, so even identical data would not hash the same.
Restore secondsChangesHardware.

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 why we verify a restore instead of trusting what a backup reports about itself.

The 1.8 seconds reflects this machine’s hardware; it is not a product claim. It also times only the pg_restore phase, not the whole recovery: downloading the snapshot and checksumming the files happen before the clock starts. A real recovery also has to download the snapshot, and your time depends on dump size and disk. That is 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. --with-login then starts Supabase’s Postgres image (about 1.7 GB to pull) and GoTrue. All of them 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                # backup + drill
./examples/reproducible-drill/run.sh --with-login   # then restore and sign in

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 (daily on every paid plan, once on the Free plan’s first backup) with the same structural checks, the Auth users line when the snapshot has them, and Storage sampled up to 100 files. Paid plans email you a report every week, and if a drill fails, you get an email. What the hosted service adds is that drills run whether anyone remembers or not. Start free or read how to test a restore by hand.