Supabase's daily backups cover the database. The docs say they don't include objects stored through the Storage API, so restoring a database backup brings back the storage.objects rows but not the files they point to.
The other reason I built this: a backup you've never restored is a guess. Plain Postgres has no auth schema, so if a table has a column defaulting to auth.uid(), its CREATE TABLE fails during pg_restore. The COPY into it fails too, and pg_restore keeps going. It finishes, every other table is there, and that one isn't. The errors are in the output, mixed in with the usual noise about roles that only exist on Supabase, so they're easy to wave through. I found this through a real 105-table database: a strict restore of it first failed on a function parameter defaulting to auth.uid(), and when I reproduced that locally with plain pg_restore, a table was simply missing.
So I wrote a CLI that does both halves. It's MIT licensed: https://github.com/backupdrill/cli
npx backupdrill backup
pg_dump in custom format (public by default, BACKUPDRILL_SCHEMAS for more) and streams it to an S3-compatible bucket you own: AWS S3, Cloudflare R2 or Backblaze B2.manifest.json with tables, row counts, sizes and sha256 checksums, plus a RECOVERY.md with restore steps.examples/.backupdrill drill
A drill restores the latest snapshot into a throwaway Postgres container (you need Docker) and checks that:
pg_restore completes and the indexes, constraints and triggers get created--verify-all-files for all of them)Before restoring, it installs the extensions listed in the manifest. It also creates stand-ins for auth.uid(), auth.jwt(), auth.role(), auth.email() and the anon, authenticated and service_role roles, so the auth.uid() case above restores instead of dropping a table. An index, constraint or policy that points at a Supabase-managed table like auth.users is reported as skipped, so you can see it.
If your app has rules only it knows about, --check-cmd "npm run smoke" runs your own checks against the restored copy before it's thrown away.
When you need the backup for real, backupdrill restore puts the database into a fresh, empty Supabase project and uploads the Storage files back. Run it with --dry-run first, then confirm by typing the target project ref.
restore doesn't recreate Auth users. Those you set up again by hand.auth.users (a profiles table usually does), move your Auth users into the new project before running restore. Otherwise those constraints fail on the real target, even though the drill only reported them as skipped.--no-privileges), so reapply permissions after a restore. RLS policies are included.backupdrill estimate measures your database and Storage and projects the monthly cost before you put it on a daily schedule.I also run a hosted version at https://backupdrill.com. It uses the same engine and adds scheduled backups, scheduled drills and an email when something fails. The CLI doesn't need it or an account.
What would you want a drill to check that this one doesn't? And if you've ever restored a Supabase project from your own backup, I'd like to hear what broke.
The user introduces an open-source CLI tool designed to test and restore Supabase backups, addressing gaps like missing Storage files and auth.uid() issues during restores. Feedback from other users suggests improvements such as checking RLS policies, foreign key integrity, and PostgreSQL sequences. The tool aims to ensure backups are reliable by simulating restores and verifying data integrity.
This looks really useful! One thing I’d love to see is a check for RLS policies and foreign key integrity after restoration. Also, verifying that Storage files actually exist and PostgreSQL sequences are synced would make the drill even more reliable. Great work on this!
Thanks! Some of that is already in there:
manifest.json, and the drill downloads a random sample of up to 100 and checks them against it. --verify-all-files checks every file.auth.users. The sandbox has no Supabase auth tables, so the drill reports it as skipped, and restore --dry-run checks it against the real target and tells you how many rows have nothing to point at.TO authenticated / anon, but the drill connects as a superuser and doesn't test what they allow. In the sandbox auth.uid() is a stub that returns null, so per-user policies can't be checked there yet.--check-cmd, which gets the restored copy's connection string in BACKUPDRILL_SANDBOX_URL.The distinction between a backup completing successfully and a restore actually being usable is important. The auth.uid() issue is a good example of how a restore can appear successful while silently leaving gaps in the recovered database.
One additional check I'd find useful is application-level consistency after restoration. Verifying that tables exist and files match their checksums is a strong baseline, but it doesn't necessarily prove that foreign-key relationships, Storage object references, and the application's expected access rules still work together.
The custom smoke-test command seems like a good way to bridge that gap without trying to make the CLI understand every application's business logic. It would also be useful to see a final summary separating hard failures, skipped Supabase-managed dependencies, and checks that passed, so someone can judge recovery readiness without digging through a long restore log.
A restore drill that clearly states what it verified—and what remains unverified—is much more valuable than a simple success message.