How to download a Supabase backup as a .sql file

Short version: most projects cannot download the backups Supabase takes — newer projects use physical backups, which restore in place or not at all. But producing an equivalent .sqlfile yourself is one command, works on every plan including Free, and the result restores into any Postgres that has the extensions your schema uses. Both paths are below, plus a trap in the official CLI that leaves people holding a “backup” with no data in it.

Check the dashboard first — sometimes the button exists

Dashboard → Database → Backups. What you find depends on which backup process your project is on. Older projects still on logical backups can download each daily backup from that page, and you are done. Projects on Postgres 15.8.1.079 or newer — which includes essentially every recently created project — use physical backups, which cannot be downloaded. Enabling PITR does the same thing and additionally replaces the daily backups entirely. The official docs’ advice for both cases: take a manual logical backup with the CLI or pg_dump. Which is the rest of this page.

The one-command answer: pg_dump to .sql

Copy the Session pooler connection string from the Connect button at the top of the dashboard and fill in your database password. Not the Direct connection string — that one runs over IPv6 unless the project has the paid IPv4 add-on, and dies with a network error on most home and CI networks.

export DB_URL="postgresql://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres"

pg_dump "$DB_URL" \
  --format=plain \
  --schema=public \
  --no-owner --no-privileges \
  --file="backup-$(date +%F).sql"

Three flags carry the weight here:

• --format=plain writes readable SQL — CREATE TABLE statements followed by COPY blocks with your rows. You can open it, grep it, diff it, and replay it with stock psql.
• --schema=public captures your application schema. Supabase-managed schemas — auth (your users), storage (file metadata) — stay out; dumping them as a non-superuser is restricted anyway. Know what the file does not contain before you need it.
• --no-owner --no-privileges keeps the file portable: without them the dump hard-codes Supabase-specific roles and grants, and replaying it into a local Postgres drowns in ownership errors.

One version check before trusting the output: pg_dump --versionmust be at least your server’s major version (Supabase projects run PG 15 or 17 — select version(); in the SQL editor tells you which). An older client refuses to dump a newer server.

The supabase db dump trap: schema only, no rows

The official docs’ free-tier advice names supabase db dump, so plenty of people start there. It produces clean SQL — and its default output contains no data. Schema definitions only. If you glance at the file, see your CREATE TABLE statements, and file it away as a backup, you have backed up an empty database. Your rows need a second run with --data-only:

supabase db dump --db-url "$DB_URL" -f schema.sql
supabase db dump --db-url "$DB_URL" --data-only -f data.sql

(The --db-url value must be percent-encoded — a password containing @ or # breaks the URL otherwise. Inside a repo linked with supabase link, pass --linked instead.) Restoring means replaying schema.sql first, then data.sql. If you keep only one command from this page, keep the pg_dump one: one file, schema and data together.

.sql or .dump — pick by what you’ll do with it

Plain .sql is the right call when a human will look at the file: one-off exports, moving data between environments, checking what a table looked like last Tuesday. For a recurring backup, custom format (--format=custom, conventionally .dump) is the better default: it is compressed, it can restore a single table instead of everything, and ownership decisions move to restore time where they belong. The backup options guide uses custom format for exactly that reason. The two are not rivals — a scheduled custom-format backup plus the occasional .sql export when you need eyes on the data is a normal setup.

Restoring the .sql file

psql "$TARGET_DB_URL" -f backup-2026-08-09.sql

Point it at an empty database — a fresh Supabase project or a local Postgres. And read the output, not the exit code: psql keeps going after errors and exits 0 anyway. One line of noise is expected if the target already has a public schema (schema "public" already exists — harmless); errors naming your own tables are real and mean the restore is incomplete. Restoring into Supabase specifically, with all its gotchas, has its own guide.

“Auto download”: schedule it instead

If what brought you here is wanting the download to happen every week without you, that is a scheduler’s job. The GitHub Actions guide sets up a free weekly workflow that dumps your database into a bucket you own; the hosted service does the same on a schedule and then restore-tests the result, because a file nobody has ever restored is a hope, not a backup — here is how to run that test yourself.

FAQ