Embarrassed_Machine5

Auth is actually the easier part. Supabase keeps users and identities in the `auth` schema, so I include those tables in the Postgres backup and restore them into the new project. Password hashes are preserved too, although users may need to log in again. Storage is kinda the opposite. The metadata is in Postgres, but the actual files still need to be backed up separately

The trigger-written snapshots are a nice idea. Do you also keep a copy of Postgres, Auth and Storage outside the Supabase project, or is this mainly for recovering from bad writes? I'm building ReviveDB, a managed Supabase backup service, so I've ended up way too deep in that distinction 😄

Yeah, that makes sense. Accepting the small consistency gap is probably better than turning the backup system into a second SaaS 😄 The trust point is fair too. It's something I'm spending a lot of time on with ReviveDB, but as a new product you ultimately have to earn it. If you ever feel like trying it on a test project, I'd honestly appreciate the feedback

Nice writeup. Actually testing a restore and adding a dead-man switch are two things a lot of DIY backup scripts miss. One thing I'm curious about: how are you handling files that change between the DB dump and the rclone run? Since they're separate, storage.objects and the copied files can represent slightly different points in time. I'm building [revivedb.dev](http://revivedb.dev), so I'm probably way too deep into this stuff, but it's nice to see a DIY guide that treats restore as part of the backup

That 2.7ms query running 412k times is the strongest example here. Most performance tools would call it fast and miss the actual problem. I'd want the ranking to show whether a query is high because of latency, call count, or both. A before/after view would also be useful for spotting regressions after a deploy. For production access, generated least-privilege SQL and a very obvious indicator when anything is sent to Anthropic would go a long way

With 30 tables, I'd turn the current database into a clean baseline migration instead of pasting the dump into the SQL editor: 1. Start a local Supabase instance 2. Import only your application objects, excluding `auth`, `storage`, owners and grants 3. Run `supabase db diff -f baseline` 4. Run `supabase db reset` locally to prove the migration works from scratch 5. Preview the remote change with `supabase db push --dry-run` The key is testing against local Supabase, not plain Postgres, because a fresh Supabase project already contains managed schemas and roles

Nice project. Cross-provider copying with checksum verification would be really useful for keeping an off-platform copy of Supabase Storage. Is that on the roadmap?

We take an inventory from Postgres’ system catalogs, restore the backup into a clean database, and take the same inventory again. Sequences are compared by schema, name and `last_value`; schema objects by name and definition. Any missing or changed object fails verification.

That's brutal, hope support gets you back online soon. Let us know how it turns out

Row counts are a useful start, but they miss broken sequences, policies, functions and Storage objects. Full disclosure: I'm building [revivedb.dev](http://revivedb.dev) . We restore every database backup into an isolated Postgres instance and compare it with the source inventory, including row counts, schema objects and sequences. Storage files get size and SHA-256 checks too. That restore-testing gap is basically why we built it.

12+ hours is rough. Id ask support if they can restore the last known good backup into a new project instead of retrying the restore in place. Hope you'll update us once it's resolved. Curious what caused it and whether the data was intact

pg\_dump doesn't cover Storage btw, only the rows pointing at your files. Restore it and every download 404s. And if you go that route, your role needs pg\_read\_all\_data + BYPASSRLS or the dump silently comes out half empty. Took me way too long to figure that one out. I ended up building [revivedb.dev](http://revivedb.dev) for this, does DB + Auth + Storage + Edge Functions and test-restores every backup. Happy to get some feedback :)

Yep, Ive changed it so a failed drop raises an operational alert instead of sitting unnoticed. I also re-check `pg_roles` after cleanup. Since it's only `pg_read_all_data` membership, I'm keeping `pg_shdepend` as a diagnostic rather than running `DROP OWNED` automatically. Thanks again for digging into this!

It's just the group membership: `GRANT pg_read_all_data TO <role>`. No table-level grants, and `BYPASSRLS` is a role attribute, so normally there shouldnt be anything blocking `DROP ROLE`. `pg_shdepend` still sounds useful as a diagnostic if that assumption ever breaks. Have you run into this with temporary backup roles in practice?

Yep, both run against `postgres`. And agreed on the retry, repeating the same drop on a dependency error won't help. I'll log the first error and only retry transient failures. `DROP OWNED` feels too risky to run automatically since it can actually delete objects

You're right. `VALID UNTIL` only expires the password. I already use prefixed roles and clean up inactive leftovers, but after `DROP ROLE` I currently trust the API response. I'll add a `pg_roles` verification after the drop and retry if the role is still there. Thanks for the suggestion!

I made [https://revivedb.dev](https://revivedb.dev) for this. It backs up your database plus Auth, Storage and Edge Functions outside Supabase. It connects through Supabase, so you don't have to share your database password. Backups are stored in the EU and every database backup gets test-restored. It's free for one project. Would love to get some feedback!

Good point. The backup also includes Storage and Edge Functions. Private Storage files currently require a service-role key, which can also write, so a manual Postgres role would only solve the database side. A fully restricted setup would need read-only Storage access as well, with separate credentials for restores

Yeah, that's a fair point. The backup connection is read-only, but the OAuth token used to create it has broader access. "OAuth-managed read-only access" is a more accurate description. Curious if others would also prefer the extra manual setup for tighter permissions

Your comment got me thinking. The production database password is no longer required or stored. Backups now use a temporary read-only role through OAuth. Thanks for pointing it out! [I wrote up how it works here.](https://www.reddit.com/r/Supabase/comments/1vgacj9/using_supabase_oauth_for_readonly_postgres_access/)

you probably want pg\_dump, not branching. i'm building [revivedb.dev](http://revivedb.dev) if you also want Storage and functions backed up.

Good reminder to back up the stuff outside Postgres too, Edge Functions, Storage and project config. Hope they get this resolved soon

Probably! If you remember the name, send it over. The ones I found either stopped at pg\_dump or still left you managing the bucket and restore process. I’m trying to make the whole thing managed, including Storage, Auth, Functions, config and automatic restore checks.

Thanks, appreciate it!

Thanks, appreciate it. And yeah, fair concern. Supabase OAuth can't access the database yet, so the password is still required. It's encrypted, but I get that asking a new service to hold it is a big ask. SOC 2 is something I'd like to do later, but it's not affordable for me in this stage.

Yeah the "where" is the easy part honestly, restoring it is what actually matters and barely anyone tests that until they have to. learned that one the hard way myself. also worth remembering it's not just the database, storage buckets need to leave the platform too if you want a real off-platform copy. (disclosure, I'm building [revivedb.dev](http://revivedb.dev) which does both, dump + storage, restore-verified, so take it with a grain of salt)

Ended up building a small tool for exactly this, it restores every backup into a throwaway postgres, checks the storage files against what it captured, and just tells you if something's off instead of a green check you have to trust [revivedb.dev](http://revivedb.dev), covers auth and edge functions too, not just the db

built [revivedb.dev](https://revivedb.dev/) because "pg\_dump nightly to S3" sounds simple until you actually need to restore and realize you never tested that the dump works, or that your storage buckets and edge functions weren't in that script at all. so it's not really a DIY-backup alternative, it restores into a scratch db and verifies it on every run, and covers the whole Supabase surface (db, storage, edge functions), not just the table data, and stores it in a managed EU bucket so you're not wiring that up yourself either. still in beta, feedback welcome. figured i'd share since this thread is exactly the problem it's built for.