Skip to content

Backup and restore

The full runbook with every detail lives at docs/BACKUP_AND_RESTORE.md. This page is a condensed operations view — enough to act in an incident, with pointers back for specifics.

What runs automatically

.github/workflows/supabase-backup.yml runs daily at 02:00 UTC (07:30 IST — low-traffic for India ops). Each run produces:

  • schema-<UTC-timestamp>.sql.gz — DDL only
  • data-<UTC-timestamp>.sql.gz — row data only
  • checksums.txt — SHA-256 of both .gz files

The dump runs pg_dump inside a Postgres container that the Supabase CLI pulls. The workflow pulls it from Amazon's public mirror (SUPABASE_INTERNAL_IMAGE_REGISTRY: public.ecr.aws), not ghcr.io, because GitHub's runners share one anonymous ghcr.io rate limit and three nightly runs on 2026-09-23/24 failed with toomanyrequests before dumping anything. If a run fails at "Dump roles" with a pull error, that is the place to look; the CI database job uses the same setting.

These are uploaded as a GitHub Actions artifact supabase-backup-YYYY-MM-DD with 30-day retention. If the optional AWS_S3_BUCKET secret is configured, the same files are copied to s3://$AWS_S3_BUCKET/supabase-backups/YYYY-MM-DD/ (S3 lifecycle governs long-term retention).

RPO / RTO

  • RPO (Recovery Point Objective): up to 24 hours of data loss from these artifacts, since dumps are daily. For tighter recovery, rely on Supabase PITR (Point-in-Time Recovery) on the Pro plan — it is the primary recovery mechanism for recent corruption. This artifact workflow is the offsite, version-controlled backup of last resort.
  • RTO (Recovery Time Objective): dominated by restore time. A psql < data-*.sql on a fresh Supabase project typically takes tens of minutes; schema-only restore is minutes.

Warning

Supabase PITR is separate from this workflow and lives in the Supabase dashboard under Database → Backups. PITR on Pro plans retains 7 days of WAL. Both mechanisms are complementary: PITR for recent bugs, artifact backups for long-horizon disaster recovery.

Prerequisites

A single GitHub Actions secret: SUPABASE_DB_URL — the direct connection Postgres URI (not pgbouncer). Set up steps in the parent doc.

Optional S3 archival secrets: AWS_S3_BUCKET, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION.

Triggering a manual backup

Before any risky migration or mass edit, kick off an on-demand backup.

UI: Actions → Supabase Backup → Run workflow → Run workflow

CLI:

gh workflow run supabase-backup.yml

Watch:

gh run watch

Downloading a backup

  1. GitHub → Actions tab → select the Supabase Backup workflow.
  2. Click the run for the date you want.
  3. Scroll to Artifacts → download supabase-backup-YYYY-MM-DD.zip.
  4. Unzip — you will have schema-*.sql.gz, data-*.sql.gz, checksums.txt.

Verifying integrity

Always run this before restoring:

sha256sum -c checksums.txt

Both lines must report OK. If either fails, do not restore from that artifact — fetch another day or trigger a manual backup.

Restore walkthrough

Danger

Never restore into the production database unless this is an active disaster-recovery scenario authorised by CEO/GM. Set TARGET_DB_URL carefully; a typo pointing at prod will overwrite live data.

  1. Decompress:

    gunzip schema-*.sql.gz
gunzip data-*.sql.gz

  2. Set the target DB URL (use staging or a scratch Supabase project unless this is real DR):

    export TARGET_DB_URL="postgres://..."

  3. Pick a restore mode:

    Full restore (DR scenario):

    # A new Supabase project grants anon / authenticated / service_role
# everything on objects postgres creates in public. The dump records
# production's grants relative to the built-in default only, so without
# this every restored table and function gets grants production never had.
psql "$TARGET_DB_URL" -c "
  ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES    FROM anon, authenticated, service_role;
  ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM anon, authenticated, service_role;
  ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON SEQUENCES FROM anon, authenticated, service_role;"
psql "$TARGET_DB_URL" --single-transaction -v ON_ERROR_STOP=1 \
     -f roles.sql -f schema.sql \
     -c 'SET session_replication_role = replica' -f data.sql

    The dump ends by setting production's own default privileges, so the revoke above only affects objects created during the restore. A local Supabase stack whose auth or storage service is older than production's will refuse a few auth/storage tables and columns; the 2026-09-19 rehearsal lists them and how they were handled.

    Schema-only (test a migration against last night's structure):

    psql "$TARGET_DB_URL" < schema-*.sql

    Data-only (re-seed a DB whose schema already matches):

    psql "$TARGET_DB_URL" < data-*.sql

  4. Sanity checks after restore:

    • Row counts on critical tables (bookings, payments, customers, partners).
    • RLS policies are present (SELECT polname FROM pg_policies WHERE schemaname='public';).
    • Verify the access model — see below. Not optional.
    • A smoke test login as a real user succeeds.
    • Trigger a Sentry release in the frontend pointing at the restored backend to verify end-to-end.

Verifying the access model after a restore

A restored database can look perfectly healthy — every table, every row, every RLS policy — and still hand nobody any permissions. Roles and grants live in Role, Permission and RolePermission, and those tables have their own failure mode: every grant migration seeds with

INSERT ... SELECT ... JOIN public."Role" r ON r.name::text = g.role_name

so a missing Role row makes the join match nothing and the grant is skipped without an error. Until 20260921110000 only two of the twenty roles were created by a migration at all, and a restore into an empty project produced 2 roles and 204 grants instead of 20 and ~949. Row counts and policy checks do not catch that; a login smoke test only catches it for the one user you tried.

Run the access-model suite against the restored database:

DB_URL="$TARGET_DB_URL" scripts/db-test.sh supabase/tests/access_model.sql

It must print ALL ACCESS MODEL TESTS PASSED. It asserts that every RoleType value has a Role row, that every permission documented in PERMISSIONS.md §4 exists, that SUPER_ADMIN holds all of them, that the CEO/GM, IT_ADMIN, ADMIN_HR and CASHIER bundles still match §5, that a role with no grants passes no check, and that the row counts have not fallen off a cliff.

If it fails, do not hand the restored environment to users. Re-run the migrations (supabase db push) so 20260921110000 seeds the roles and grants, then run the suite again.

access_model.sql does not check table and function grants. It passed on a restore that let anonymous callers run get_customer_360 and let a partner login rename a permission. Run the grant suites too, and treat any failure as a stop:

DB_URL="$TARGET_DB_URL" scripts/db-test.sh supabase/tests/security_lockdown.sql \
  supabase/tests/data_isolation.sql supabase/tests/journey.sql \
  supabase/tests/list_paging.sql supabase/tests/finance_controls.sql \
  supabase/tests/audit_trail.sql

Security notes

  • GitHub Actions artifacts are scoped to repository collaborators with read access. Treat their contents as production credentials.
  • data-*.sql contains every customer record, payment detail, partner rate. Do not download to a personal machine; restore directly into a controlled environment.
  • Rotate SUPABASE_DB_URL whenever a collaborator with admin access leaves the team (see security.md).
  • The dump itself does not contain the database password — only the workflow run's environment does, and GitHub redacts it from logs.

See docs/BACKUP_AND_RESTORE.md for the full source of truth (first-time secret setup, S3 rotation, retention rationale).