I restore a fair number of Supabase backups that aren't mine. Not because anything went wrong, but because I build a tool that restore-tests them, and almost every new database has taught me something about how backups quietly go bad.
The pattern is always the same. The backup job is green. The file is in the bucket. It's a reasonable size. Nobody has any reason to doubt it. And then the first time someone actually restores it, something is missing.
None of the five cases below involve exotic failures. They're all defaults, or reasonable-looking choices, and every one of them produces a backup that looks fine until you use it.
1. The dump with no data in it
The Supabase CLI has a supabase db dump command, and it's the first thing many people reach for. It's in the docs, it's one line, it produces a big .sql file.
By default, that file contains your schema and no rows. Tables, functions, policies, all there. Data, none. You need --data-only for a second file with the rows, and roles are a third run with --role-only. The file is thousands of lines long and looks complete, which is exactly why this one survives for months: nobody opens a backup file to check for COPY statements.
If your backup is built on supabase db dump, open the file and search for COPY or INSERT. If they're not there, you're backing up a blueprint.
pg_dump against the Session pooler connection string doesn't have this problem, since it dumps data by default. Just make sure its major version is at least your project's Postgres version, or it will refuse to run, which at least is a loud failure.
2. The files that were never in the backup
Supabase Storage keeps your uploaded files outside the database. What the database has is storage.objects, a table with one row per file: bucket, path, size, timestamps.
So every database backup, whether it's Supabase's own daily backup, point-in-time recovery, or your pg_dump, contains the rows that describe your files and none of the files. Supabase's docs say this plainly, but it's easy to read past.
The failure shows up after a restore. The app starts, every query works, and every avatar, invoice PDF and user upload is a broken link, because the database is pointing at files it never had.
If users upload anything, the buckets need their own backup. Supabase exposes Storage over an S3-compatible endpoint (Storage → Settings), so rclone copy from each bucket into a bucket you own works fine. Copy into a new dated folder each run; syncing one folder in place means a file deleted in production tonight is deleted from your backup tonight.
3. The backup you can't download
On the Pro plan, Supabase takes a daily backup and keeps it for seven days. Lots of people treat that as their off-site copy.
It isn't off-site, and on most current projects you can't get it out. Projects on the physical backup process, which is every project on a recent Postgres version and every project with point-in-time recovery enabled, can restore from those backups in the dashboard but can't download them. If the problem is the project itself (it's deleted, the account is locked, billing lapsed), those backups are on the wrong side of the problem.
Supabase's backups are good at what they're for: rolling back a bad day inside the project. They're not a copy you hold. For that you need your own dump in your own bucket, and on the Free plan there are no automated backups at all, so your own dump is the only copy that exists.
4. The table that doesn't survive the restore
This one is my favorite, because the restore "succeeds."
Supabase schemas use auth.uid() everywhere, and a very natural place to use it is a column default: owner uuid default auth.uid(). The row fills in its owner automatically. Nice.
When you test a restore, you restore into a throwaway plain Postgres. Plain Postgres has no auth schema. So CREATE TABLE for that table fails, the COPY of its rows fails, and pg_restore carries on to the end, because by default it reports errors and continues. You end up with every table except that one.
The error lines are there. But a Supabase dump restored into plain Postgres always produces a handful of expected complaints about roles and schemas that only exist on Supabase, and after the third time you learn to scroll past anything mentioning auth. That habit is exactly what hides this.
The fix is small: before restoring, create the three roles (anon, authenticated, service_role) and an auth schema with stub uid(), role(), email() and jwt() functions that return null. Don't create auth.users; errors about foreign keys into it really are sandbox noise. I wrote up the full reproduction on DEV if you want to see it happen in two containers.
5. The backup that stopped last month
The last one isn't about what's in the backup. It's about when.
A cron job or a scheduled GitHub Actions workflow runs every night. One day a password gets rotated, or the runner image changes, or the database grows past a timeout. The job starts failing. Nothing pages anyone, because failing silently is what scheduled jobs do. The bucket still has files in it, just old ones.
When you finally need the backup, it's six weeks stale.
This is the only one of the five that a restore test alone doesn't catch, because an old backup restores perfectly. You catch it by checking the newest row: restore the latest backup and run select max(created_at) on a table that gets written to every day. If the answer isn't from last night, the job is broken.
The habit that catches all five
Every case above has the same shape: the backup looks fine as a file and fails as a restore. So the fix is to restore it, regularly, and look at what came back.
My routine, which takes about fifteen minutes by hand:
- Pull the newest backup from your bucket.
- Start a throwaway Postgres in Docker with the same major version as your project.
- Create the role and
authstubs, thenpg_restore --no-owner --no-privileges. - Compare the table count with production.
- Check row counts on the two or three tables that must never be empty.
- Check
max(created_at)on a table that's written every day. - Pick a few Storage files from the backup and compare checksums with production.
- Throw the container away.
Step 4 catches case 4. Step 5 catches case 1. Step 6 catches case 5. Step 7 catches case 2. And step 1 catches case 3, because you can't do it at all if the only copy is one you can't download.
Do it once a month. Put it in the calendar. The first time takes longer, because you'll find something.