Open-source CLI that test-restores your Supabase backups (Postgres + Storage files, your own S3/R2/B2 bucket) #52245
Replies: 2 comments 1 reply
|
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! |
|
The distinction between a backup completing successfully and a restore actually being usable is important. The 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. |
Uh oh!
There was an error while loading. Please reload this page.
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.objectsrows 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
authschema, so if a table has a column defaulting toauth.uid(), itsCREATE TABLEfails duringpg_restore. TheCOPYinto it fails too, andpg_restorekeeps 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 toauth.uid(), and when I reproduced that locally with plainpg_restore, a table was simply missing.So I wrote a CLI that does both halves. It's MIT licensed: https://github.com/backupdrill/cli
Backup
pg_dumpin custom format (publicby default,BACKUPDRILL_SCHEMASfor more) and streams it to an S3-compatible bucket you own: AWS S3, Cloudflare R2 or Backblaze B2.manifest.jsonwith tables, row counts, sizes and sha256 checksums, plus aRECOVERY.mdwith restore steps.examples/.Drill
A drill restores the latest snapshot into a throwaway Postgres container (you need Docker) and checks that:
pg_restorecompletes and the indexes, constraints and triggers get created--verify-all-filesfor 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 theanon,authenticatedandservice_roleroles, so theauth.uid()case above restores instead of dropping a table. An index, constraint or policy that points at a Supabase-managed table likeauth.usersis 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 restoreputs the database into a fresh, empty Supabase project and uploads the Storage files back. Run it with--dry-runfirst, then confirm by typing the target project ref.What it doesn't cover
restoredoesn't recreate Auth users. Those you set up again by hand.auth.users(aprofilestable usually does), move your Auth users into the new project before runningrestore. 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 estimatemeasures 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.
All reactions