How to create a manual Supabase backup

The fastest manual backup of a Supabase project is one pg_dump command against the Session pooler connection string — it works on every plan, needs nothing but the Postgres client tools, and writes a file you can keep anywhere. The Supabase CLI gets you the same result in three commands. The dashboard is the one place you cannot do it: there is no Back up nowbutton, and the backups it does list are taken on Supabase’s schedule, not yours. All three routes below, then how to download, verify, and stop doing this by hand.

The one-minute answer

Get the connection string from the dashboard’s Connect button — choose Session pooler, not the transaction pooler, because pg_dump needs a session that survives the whole dump — and run:

export DB_URL="postgresql://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres"
export BACKUP_FILE="backup-$(date +%Y%m%dT%H%M%S).dump"   # one name per run, reused below

pg_dump "$DB_URL" \
  --format=custom \
  --schema=public \
  --file="$BACKUP_FILE"

That file is a complete, restorable copy of your application schema and every row in it. --format=custom is compressed, lets pg_restore load a single table or one section at a time, and is what the restore test below relies on — if you want readable SQL to open and grep, add --format=plain and a .sql name instead; the .sql export guide is built around that variant. --schema=public skips the Supabase-managed schemas that would fail to restore anyway. Ownership is not decided here: a custom-format archive records the source owners regardless of flags, and you drop them with --no-owner on pg_restore (the test block below does) — only the plain-SQL variant needs --no-owner at dump time. The timestamped name matters more than it looks: a date-only name means a second run the same day — after the migration you were backing up against — silently replaces the pre-migration copy, and an interrupted run leaves you with a partial file where a good one was. Notice what is not there: --no-privileges. Your grants and revokes are part of your security model — a function you locked down with REVOKE EXECUTE … FROM PUBLICcomes back executable by everyone if the backup does not carry that statement, so keep the ACLs in the file. They reference Supabase’s standard roles (anon, authenticated, service_role), which exist in every Supabase project, so they restore cleanly there; a plain Postgres has no such roles, which is why the restore test below creates them before it starts. If you ever restore with --no-privileges for portability, re-apply the grants before you point an app at the result.

Option 1: the dashboard — restore only, no backup on demand

Under Database → Backups a Pro, Team, or Enterprise project lists one backup per day, kept for 7, 14, or 30 days. You can restore any of them in place or clone one into a new project. What you cannot do is trigger a backup right now — before a risky migration, say. The schedule is Supabase’s.

Whether you can download a listed backup depends on which backup process your project uses. Projects still on the older logical process show a download link per backup. Projects on Postgres 15.8.1.079 or newer, and any project with PITR enabled, use physical backups — block-level snapshots that restore beautifully inside Supabase and cannot be handed to you as a file. If your Backups page says Physical backups, the manual backup has to come from one of the next two options. On the Free plan the page is empty: Supabase’s docs say it currently takes up to 7 daily backups of free projects, but you cannot restore or download those until you upgrade — the only free-plan snapshot the dashboard hands you is the one taken when a project is paused.

Option 2: the Supabase CLI

The official backup-and-restore procedure uses three dumps, because roles, schema, and data restore in that order and with different flags:

export DIR="backup-$(date +%Y%m%dT%H%M%S)" && mkdir -p "$DIR"
supabase db dump --db-url "$DB_URL" -f "$DIR/roles.sql" --role-only
supabase db dump --db-url "$DB_URL" -f "$DIR/schema.sql"
supabase db dump --db-url "$DB_URL" -f "$DIR/data.sql" --use-copy --data-only

Two things to know. The CLI runs pg_dump inside a Docker container, so Docker Desktop has to be installed and running. And a bare supabase db dump -f backup.sql is schema only — no rows. People discover this during a restore. If you want one file with everything in it, use pg_dumpdirectly; the CLI’s three-file split is designed for migrating between projects, where the roles file matters.

Option 3: pg_dump directly (no Docker)

This is the command from the top of the page. It needs the Postgres client tools installed locally; on Windows use the official installer with the server component unticked.

# macOS — libpq is keg-only, so put its bin on the PATH yourself
brew install libpq && export PATH="$(brew --prefix libpq)/bin:$PATH"
# Debian / Ubuntu
sudo apt install postgresql-client

Use a client of the samemajor version as your project (check it under Project Settings → Infrastructure): a Postgres 15 client cannot dump a Postgres 17 database, and a newer client can write statements an older target refuses to load — so a 17 dump belongs with a 17 restore, and pinning both to the project’s version keeps the file restorable where you will need it.

Two variations people ask for. One table only:

pg_dump "$DB_URL" --format=custom \
  --table=public.orders --file="orders-$(date +%Y%m%dT%H%M%S).dump"

Include Auth users as well as your own tables:

pg_dump "$DB_URL" --format=custom \
  --schema=public --schema=auth --file="with-auth-$BACKUP_FILE"

The authschema restores into a fresh project only with care — it references Supabase-managed roles and the target project’s own JWT secret — so treat that dump as a record of your users, not a one-click restore.

Where to put the file

A backup on the laptop that made it is one spilled coffee from gone. Copy the file to object storage you control — Cloudflare R2 has a free 10 GB tier and no egress fees, and the bucket setup guide walks through R2, S3, and Backblaze B2 with screenshots. One command with the AWS CLI or rclone finishes the job:

aws s3 cp "$BACKUP_FILE" s3://my-backups/supabase/ \
  --endpoint-url "https://<account-id>.r2.cloudflarestorage.com"

Check that it restores

Restore the file into a throwaway Postgres in Docker and compare table counts with the source. The procedure below is the one BackupDrill’s own drill engine uses, for reasons that matter: a plain postgres image of the same major version as your project, not a Supabase one — so there are no default grants to anon waiting to silently open a table your dump left private — and the restore split into two passes, because a public dump references things only a Supabase project has (the auth schema behind auth.uid() policies and auth.users foreign keys, roles like authenticated). The cheap ones — the roles and the auth.uid() family of functions — are stubbed before the restore, because a column default of auth.uid() or a grant to authenticated is part of a perfectly healthy table definition and must not read as a broken backup. The expensive one — the auth.users table with your users in it — is not, so anything that references it is expected to fail in the second pass.

docker run -d --name restore-test -e POSTGRES_PASSWORD=pw \
  -p 127.0.0.1:5433:5432 postgres:17          # same major version as the project
until pg_isready -h 127.0.0.1 -p 5433 -U postgres; do sleep 1; done
export TEST_DB="postgresql://postgres:pw@127.0.0.1:5433/postgres"

# stubs: what a public dump references that plain Postgres lacks (no auth.users on purpose)
psql "$TEST_DB" --set ON_ERROR_STOP=1 -c "
  create role anon; create role authenticated; create role service_role;
  create role supabase_admin nologin;   -- named in the schema's default privileges
  create schema auth;
  create function auth.uid()   returns uuid  language sql stable as 'select null::uuid';
  create function auth.role()  returns text  language sql stable as 'select null::text';
  create function auth.email() returns text  language sql stable as 'select null::text';
  create function auth.jwt()   returns jsonb language sql stable as 'select null::jsonb';
  drop schema public;"

# pass 1 — schema and rows, strict: any error here means the backup is broken
pg_restore --section=pre-data --section=data --exit-on-error \
  --no-owner --dbname "$TEST_DB" "$BACKUP_FILE" \
  && psql "$TEST_DB" -c "select count(*) from information_schema.tables where table_schema='public';"

# pass 2 — indexes, constraints, triggers, policies: read every error line
pg_restore --section=post-data --no-owner --dbname "$TEST_DB" "$BACKUP_FILE" 2>&1 \
  | grep "^pg_restore: error" || echo "all post-data objects restored"

What each piece is for. The stub block creates the Supabase roles a dump can name — the three API roles plus supabase_admin, which the schema’s default privileges reference — and a no-op auth schema with the helper functions column defaults and policies call, so that healthy table definitions load; it deliberately leaves out auth.users. It also drops the throwaway database’s empty public schema, because pg_dump --schema=public recreates it and --exit-on-errorwould otherwise stop on “schema already exists” before a single table loads — do that only here; a real project keeps its schema. Pass 1 has to be clean: it is your tables, their defaults, and your rows. Pass 2 is where the remaining Supabase-shaped objects land, so read the errors rather than counting them: a line that says relation "auth.users" does not exist is a foreign key to your users and is expected in a sandbox — the same constraint validates in a real recovery once you restore Auth users first, from the --schema=auth variant above — while an error naming one of your own indexes, constraints, or triggers is real. The port is bound to 127.0.0.1 so a copy of your production data with a throwaway password is not reachable from the network, and pg_isready waits until the container accepts connections.

One warning for the day you restore into a real Supabase project rather than a sandbox. Supabase projects carry default privileges that grant new tables in public to anon and authenticated automatically, and pg_dumpwrites no statement for a table whose access matches Postgres’s own default — so a table you had locked down can come back readable by anonafter a restore. Before you point an app at a restored project, compare this query’s output with the source and revoke what differs:

select grantee, table_name, string_agg(privilege_type, ', ' order by privilege_type) as privileges
from information_schema.role_table_grants
where table_schema = 'public' and grantee in ('anon', 'authenticated')
group by grantee, table_name
order by grantee, table_name;

If pass 1 completes and a couple of spot-checked tables have rows, the file is real. The restore-testing guide turns this into a repeatable drill, and the restore guide covers putting the file back into a real Supabase project, flags and all.

What a manual database backup does not contain

Three things, and they surprise people at restore time. Storage files: the uploaded files live in a separate object store that no dump touches — and a --schema=public dump does not even carry the storage.objects rows that point at them — so they need their own backup. Auth users: the auth schema is Supabase-managed and outside a --schema=public dump unless you add it explicitly, as above. And project configuration — Edge Function source, Auth provider settings, secret values — is not in the database at all.

Stop doing it by hand

A manual backup is the right move before a migration. As the only backup, it fails the way every manual process fails: it stops happening. The free fix is a schedule — the GitHub Actions workflow runs this same pg_dump weekly and pushes the file to your bucket. The hosted fix is BackupDrill: connect the project with Supabase OAuth (no database password to paste), point it at your bucket, and it backs up the database — plus Storage files once you add their S3 keys — on a schedule, then runs restore drills that prove the backup comes back. The free plan covers one project with weekly backups; the quickstart takes about five minutes.

FAQ