How to test that your Supabase backup actually restores

A restore test — also called a restore drill — takes an existing backup, restores it into a disposable database, and verifies what came back: the restore completes, the tables exist, the data is present. It is the only way to know a backup works, because backups fail silently — a truncated dump, a wrong-role dump, a half-failed job all look identical to a good backup as files in a bucket. The operations rule is old and blunt: a backup you have never restored is not a backup, it is a hope. This guide covers three ways to run the test against a Supabase project: by hand in about fifteen minutes, automated with an open-source CLI, or fully managed on a schedule.

The manual restore test, step by step

You need a custom-format dump of your database — if you do not have one yet, the backup options guide has the working pg_dump command — plus Docker and pg_restore on your machine. Nothing below connects to production.

Step 1 — start a throwaway Postgres

Match your Supabase Postgres major version (run select version(); in the SQL editor — Supabase projects run PG 15 or 17):

docker run -d --rm --name restore-test \
  -e POSTGRES_PASSWORD=drill \
  -p 127.0.0.1:55432:5432 \
  postgres:17-alpine

# Wait until it accepts TCP connections
docker exec restore-test pg_isready -h 127.0.0.1 -U postgres

The -h 127.0.0.1 matters: during initialization the official image briefly runs a temporary server that only listens on a Unix socket, so a default socket check can pass while TCP connections still get dropped mid-restore. Checking over TCP means ready actually means ready.

Step 2 — restore the dump

pg_restore \
  --no-owner --no-privileges \
  --dbname "postgresql://postgres:drill@127.0.0.1:55432/postgres" \
  backup-2026-07-10.dump

--no-owner --no-privileges is not optional: the dump records owners and grants for Supabase roles (anon, authenticated, service_role) that do not exist in a bare container, and without the flags the restore drowns in ownership errors.

Even with them, expect some errors — and learn to triage them. Errors that reference Supabase-managed schemas or roles — an RLS policy TO authenticated, a foreign key into auth.users, a function calling auth.uid() — are artifacts of the bare sandbox, not of your backup. One more piece of fixed noise: schema "public" already exists — the dump recreates the public schema that every fresh Postgres already ships with; ignore it. Errors naming your own tables, indexes, or constraints are real findings: that is your backup failing to restore, which is exactly what this test exists to catch.

Step 3 — verify what came back

export TEST_DB="postgresql://postgres:drill@127.0.0.1:55432/postgres"

# Table count — compare against the same query on production
psql "$TEST_DB" -c "select count(*) from pg_tables where schemaname = 'public';"

# Spot-check tables that must never be empty
psql "$TEST_DB" -c "select count(*) from public.users;"
psql "$TEST_DB" -c "select count(*) from public.orders;"

# Is the backup recent? Check an append-heavy table
psql "$TEST_DB" -c "select max(created_at) from public.orders;"

The three questions, in order of how often they save people: did every table come back, did the important tables come back with data in them, and is the data recent. A backup job that has been silently failing for a month passes the first two checks and fails the third.

Step 4 — application-level check, then destroy

SQL checks prove structure and volume; they do not prove your application can use the data. Run the queries your app actually issues — the dashboard aggregate, the reporting join — against $TEST_DB. Keep expectations honest here: a --schema=public dump does not include the auth schema, so login flows will not work against the sandbox — test the data paths, not the auth paths. Then:

docker rm -f restore-test

Total cost: about fifteen minutes and zero risk. The catch is the part nobody writes down: this only works when someone remembers to do it, and the week nobody remembers has a way of being the week before you need it.

Automate it: the open-source CLI

The backupdrill CLI (MIT, github.com/backupdrill/cli) runs the loop above as one command against the latest snapshot in your backup bucket (the GitHub Actions guide shows how to produce those snapshots on a schedule). It needs Docker and pg_restore locally:

npm install -g backupdrill

backupdrill drill                    # restore latest snapshot into a throwaway
                                     # Postgres and verify it
backupdrill drill --snapshot <timestamp>   # drill a specific snapshot
backupdrill drill --verify-all-files       # checksum every Storage file

Each drill runs this exact verification set:

• Archive integrity.The downloaded dump’s sha256 must match the manifest; a mismatch fails the drill immediately, before any restore is attempted.
• pg_restore completes. Schema and data restore first — any error there is a hard fail — then post-data objects (indexes, constraints, triggers) in a second pass.
• Post-data objects restored. Your own indexes, constraints, and triggers must all come back; failures that only reference Supabase-managed schemas or roles (which do not exist in the sandbox) are recorded in the report as expected skips rather than failures.
• Table count matches the manifest, and no table is missing — every table recorded at backup time must exist in the restore.
• Populated tables came back. Any table that had rows at backup time must restore non-empty — the check that catches the half-backed-up database.
• Storage file integrity. Backed-up files are read back from your bucket and their sha256 compared to the manifest — a random sample of up to 100 files per drill by default; --verify-all-files checks every file.

Since 0.1.2 the drill also pre-installs the extensions recorded in the manifest into the sandbox before restoring — and when the backup contains pgvector columns it swaps the sandbox image for one with pgvector built in, so vector-typed tables restore instead of hard failing. Extensions the sandbox genuinely cannot install (Supabase-managed ones like pg_graphql) are noted in the report and only fail the drill if the restore actually depends on them. Configuration and the rest of the commands are in the CLI docs.

Fully managed: drills on a schedule

The hosted service runs the same engine on a schedule, so the test happens whether anyone remembers or not: monthly restore drills on Solo, weekly on Team and Agency, with a report emailed after every drill and failures alerted — you find out a backup is broken from an email, not from an outage. For context on where this sits in the Supabase ecosystem: the official recovery upgrade is PITR at $100/month per project, which is genuinely better at recovery granularity and, like the built-in backups, never restore-tests anything. The two solve different problems, and running both is a legitimate setup.

What a passed drill does — and does not — prove

Honest limits, because a verification tool that oversells itself is exactly the failure it claims to prevent:

• The public schema is what gets tested. Backups cover the public schema by default, so drills verify exactly that — Supabase-managed schemas (auth, storage metadata) are not in the dump and therefore not in the test.
• Row counts at backup time are planner estimates (n_live_tup), so the “populated tables came back” check catches tables restored empty — not row-for-row equality with production.
• Storage verification is a random sample of up to 100 files per drill by default (to keep egress sane), and it verifies the backup copies in your bucket against the manifest — not the live files in Supabase.
• A pass is evidence, not a guarantee. It means this snapshot restored successfully in the verification environment at that time — which is a categorically stronger statement than “the backup job exited 0”, and still not an unconditional promise about any future restore.

How often should you test?

Cadence is one line of a wider plan — the Supabase disaster recovery runbook covers the rest: which failures each backup type can and cannot save you from, and what to do before, during, and after an incident.

• Production projects: weekly. Frequent enough that a broken backup gets caught within days, cheap enough (one restore of one snapshot) that there is no reason to skip it.
• After every schema migration. Migrations are where restores quietly break — a new extension the restore target lacks, a constraint that now depends on data order, a table moved out of the dumped schema.
• Before major releases. The moment you most need a working rollback path is the moment to prove you have one.

FAQ