Database Dump & Load¶

This page covers full backups and restores of the App-name PostgreSQL database on Supabase — roles, schema, data, and auth users — plus migrating object storage, environment-specific procedures, and troubleshooting. This is the manual backup/clone path; for day-to-day schema changes use Migrations instead.

Never load production data into shared or test environments without scrubbing PII

Production dumps contain real customer records, email addresses, and auth password hashes. Loading them unscrubbed into a shared, test, or local environment exposes that data to anyone with access to that environment. Before restoring production data anywhere outside production, anonymise or remove PII (emails, names, tokens, payment references) — or use a synthetic dataset instead.

Prerequisites¶

Tool	Purpose	Install (macOS)
Docker	Runs the Supabase CLI consistently	Docker Desktop
Supabase CLI	Dumps roles, schema, data, auth	`brew install supabase/tap/supabase`
`psql` (v15+)	Loads dumps into a target database	`brew install postgresql@17 && brew link postgresql@17 --force`

Verify with supabase --version and psql --version. Gather the target's direct connection URL from the Supabase Dashboard (Settings → Database):

postgresql://postgres.[PROJECT_ID]:[PASSWORD]@aws-0-[REGION].supabase.co:5432/postgres

Pooler vs. direct connection¶

Connection	Port	Use for
Transaction pooler	`6543`	Application runtime queries
Direct connection	`5432`	All dump and load operations

Warning

Always run dump/load over the direct connection (port 5432), never the transaction pooler (6543). The pooler does not support the session state these operations require. The same rule applies to Migrations.

Managed vs. self-hosted¶

Whether you restore roles depends on which kind of Supabase you target.

	Managed (`*.supabase.co`)	Self-hosted / local
Database URL	Ends in `.supabase.co`	`localhost` or a custom VPS IP
Superuser access	Restricted	Full control of the `postgres` role
Restore `roles.sql`?	No — Supabase manages roles	Only for custom DB-level roles missing on the target

Managed Supabase pre-configures system roles (anon, authenticated, service_role). Trying to overwrite them from a roles.sql file will fail.

Backup (dump)¶

A complete, recoverable backup is four separate exports. Set DB_URL to the source direct connection string first.

# 1. Roles / permissions
supabase db dump --db-url "$DB_URL" -f roles.sql --role-only

# 2. Schema / structure
supabase db dump --db-url "$DB_URL" -f schema.sql --schema public

# 3. Data / records
supabase db dump --db-url "$DB_URL" -f data.sql --data-only --schema public

# 4. Auth users (emails, password hashes)
supabase db dump --db-url "$DB_URL" -f users-auth.sql --data-only --schema auth

Note

The auth schema dump contains password hashes and user emails. Handle users-auth.sql as highly sensitive — see Security best practices.

Restore (load)¶

Set TARGET_DB_URL to the target direct connection string. Always load schema.sql before data.sql.

psql --variable ON_ERROR_STOP=1 \
  --file schema.sql \
  --file data.sql \
  --dbname "$TARGET_DB_URL"

Situation	Guidance
Restoring to managed Supabase	Skip `roles.sql` — Supabase manages roles
Restoring between self-hosted instances	Load `roles.sql` first if the target lacks your custom DB roles
Restoring auth users	Load `users-auth.sql` after `data.sql`, and only when intentionally cloning accounts
Order	`schema.sql` → `data.sql` (→ `users-auth.sql`)

Migrating S3 storage¶

Cloning an environment that uses object storage means copying the files too, not just the database rows that reference them.

aws s3 sync s3://source-bucket-name s3://target-bucket-name --copy-props none

Run this with credentials scoped to both buckets. --copy-props none avoids copying source-only metadata/ACLs that the target account may reject. Confirm the target bucket name matches the new environment's configuration before syncing.

Environment-specific operations¶

There are two environments — test and production. Treat the direction of any copy carefully.

Production → local / test (cloning down)¶

This is the common case for reproducing a bug locally. Dump from production, then load into the target. Because real foreign keys may be loaded out of order, disable replication-role triggers during the data load:

psql --variable ON_ERROR_STOP=1 \
  --file schema.sql \
  --command "SET session_replication_role = replica;" \
  --file data.sql \
  --dbname "$LOCAL_DATABASE_URL"

Danger

Before cloning production down, re-read the PII warning at the top of this page. A test or local environment is not an acceptable home for unscrubbed production customer data.

Test → production (promoting up)¶

Avoid wholesale data loads into production. Schema changes should reach production through Migrations during deploy, not via raw data.sql restores. Reserve full restores into production for genuine disaster recovery.

Managed backups & PITR¶

Manual dumps are deliberate, portable snapshots — ideal for clones, pre-migration safety copies, and off-platform archives. They are not a substitute for Supabase's managed backups. Supabase takes automated backups and, on supported plans, offers point-in-time recovery (PITR) that lets you restore production to a specific moment without a pre-existing manual dump.

Use both: rely on managed backups / PITR as the production safety net, and take a manual dump before any risky operation (a destructive migration, a large data fix). See Supabase (Hosted) for enabling and using managed backups, and Rollback & Incidents for restore decisions during an incident.

Security best practices¶

Practice	Why
Never commit `.sql` dumps to Git	They contain customer data and auth hashes
Scrub PII before non-production restores	Test/shared environments have broader access
Rotate the DB password after large migrations	Third-party tools may have seen the credential
Verify backups with a test restore monthly	An untested backup is not a backup
Store dumps encrypted, delete when done	Limits exposure window for sensitive exports

Troubleshooting¶

Symptom	Cause	Fix
`Permission denied to change default privileges`	Restoring `roles.sql` to managed Supabase	Skip `roles.sql`; Supabase manages roles
`SSL connection is required`	Missing `sslmode` in the connection string	Append `?sslmode=require` to the URL
Dump contains schema but no data	`supabase db dump` defaults to schema-only	Add `--data-only` for records
`Disk quota exceeded`	Target database out of space	Check the Supabase storage limit; clean up before importing
Hung connection / `prepared statement` errors	Using the pooler (6543) for dump/load	Switch to the direct connection (5432)