Five things restore-testing Supabase dumps turned up

We spent the first week of October restore-testing backups made with the Supabase CLI: the three files from Supabase's backup and restore guide, replayed with the psql command from the same guide into a fresh Supabase database. The goal was a GitHub Action that does that restore on every run, so we needed to know every way it fails.

Some of it fails loudly, which is fine. The parts worth writing down are the ones that finish without an error and leave you with less than you think.

The setup

These are the commands from Supabase's guide, unchanged:

supabase db dump --db-url "$DB_URL" -f roles.sql --role-only
supabase db dump --db-url "$DB_URL" -f schema.sql
supabase db dump --db-url "$DB_URL" -f data.sql --use-copy --data-only \
  -x "storage.buckets_vectors" -x "storage.vector_indexes"

psql --single-transaction --variable ON_ERROR_STOP=1 \
  --file roles.sql --file schema.sql \
  --command 'SET session_replication_role = replica' \
  --file data.sql --dbname "$TARGET_URL"

The target was a local Supabase from supabase start, on the same Postgres major version as the source. A plain Postgres container is not enough: the dump refers to the auth schema and to roles like authenticated that only a Supabase database has.

1. A cut-off dump can restore with exit code 0

This is the one that matters most, and it has nothing to do with Supabase.

On 27 September we dumped a database of two tables, 5,000 rows and 300 rows, cut the data file at three points and replayed each with --single-transaction and ON_ERROR_STOP=1, which are there to stop at the first problem and roll everything back.

• Cut in the middle of a value: psql stopped with an error and wrote nothing.
• Cut inside the last column of a row: exit code 0. The first table came back with 2,596 of its 5,000 rows and the second came back empty.
• Cut exactly between the two tables: exit code 0, first table complete, second empty.

Each table's rows sit in one COPY block that ends with a line holding only \.. When the file runs out first, psql takes the end of the file as the end of the block and carries on.

The cheap check is the line pg_dump writes at the very end of a complete file:

grep -c 'PostgreSQL database dump complete' data.sql   # 1 is good, 0 means truncated

Only data.sql keeps it. The CLI strips comments out of the schema file (CLI 2.111), so search the data file.

2. Your grep for users is probably wrong

The second check most people write is whether the accounts made it into the dump. Our first version searched for COPY auth.users, and it would have failed on every real dump, because the CLI quotes every identifier:

grep -c 'COPY "auth"."users"' data.sql

Worth getting right, because supabase db dump without --data-only writes no rows at all and leaves auth out entirely. It restores cleanly. You find out at the login page.

3. pg_dump 17 against a Postgres 15 project

With no supabase/config.toml next to it (the usual case in a repository that only holds a backup workflow), the CLI dumps with pg_dump 17 whatever your project runs. The backup succeeds. The data file then carries this near its top:

SET transaction_timeout = 0;

Postgres 15 has no such setting, and with ON_ERROR_STOP=1 the replay stops on that line in any Postgres 15 database, including the project the file came from. We reproduced it on 4 October with pg_dump 17.11 and Postgres 15.19.

If your project is on 15, one line in the job's environment makes the CLI use the matching client:

env:
  SUPABASE_DB_MAJOR_VERSION: '15'

4. roles.sql stops twice on Postgres 17

Replaying the files of two Postgres 17 databases, one of them a hosted Supabase project, roles.sql stopped on two lines that Supabase's guide does not mention:

ERROR: "supabase_admin" is a reserved role, only superusers can modify it
ERROR: permission denied for parameter log_min_messages

The first is on a line starting ALTER ROLE "supabase_admin", the second on one starting GRANT SET ON PARAMETER. Both configure roles Supabase creates in every project for itself, so commenting them out is safe and the replay goes through. The CLI already comments out most lines about its reserved roles; these get past it.

5. pg_cron jobs do not come back

If your project schedules work with pg_cron, the restore brings back the extension and none of the jobs. The jobs are rows in cron.job, and the CLI's data dump leaves those rows out. We confirmed it in CI on 4 October: the source had a scheduled job, and the restored database had 36 tables and no cron rows.

A restored project therefore runs nothing on a schedule until you notice. Keep your cron.schedule calls in a migration or a script you can run again.

What a restore still does not prove

Counting rows tells you the data came back. Three things it cannot tell you:

• That anyone can sign in. The rows in auth.users include the password hashes, but only a real sign-in against the restored copy tests them.
• Your uploaded files. Storage keeps the bytes outside Postgres. The dump has the storage.objects rows and none of the files, so a restore shows every bucket full and every download broken.
• Encrypted columns. Vault secrets and pgsodium columns come back as ciphertext, and the root key is never in a dump.

Doing it on every run

All of the above is what our open-source Action does after each backup. It reads the server's Postgres version, takes the three dumps, comments out the two roles.sql lines, restores into supabase db start on the runner, and compares the row count of every table with the rows the file holds. The run goes green only when every table came back:

- uses: Reeve-page/supabase-backup-action@v1
  with:
    db-url: ${{ secrets.SUPABASE_DB_URL }}

It is MIT licensed: https://github.com/Reeve-page/supabase-backup-action. Use the Session pooler connection string in the secret, since GitHub's runners reach IPv4 only and the direct host is IPv6 unless you pay for the add-on. Every run also downloads the whole database, and Supabase counts that against your egress allowance.

The full drill, with the row counts to compare and the sign-in test, is here: https://reeve.page/blog/test-your-supabase-backup

I build Reeve, and if you would rather not run this yourself, its managed Supabase backups keep the copies outside your Supabase account and restore them with one button: https://reeve.page/supabase-backups