Supabase pg_dump and pg_restore: pitfalls and fixes

You can use plain pg_dump and pg_restore with Supabase, but only if you connect on port 5432 (direct or session pooler, never the transaction pooler on 6543), exclude the Supabase-managed schemas (auth, storage, realtime, extensions, and friends), and dump with --no-owner --no-privileges so the restore doesn't trip over roles like supabase_admin that you don't control. Restore into a fresh, empty project, then verify tables, RLS policies, and functions actually arrived — pg_restore keeps going past errors by default, so a "finished" restore is not a complete restore.

This is the full manual path, including the exact error messages you'll hit and why. It's also, transparently, the workflow that SupaClone automates end to end — but you should understand the manual version even if you never run it by hand.

Which connection string should you use for pg_dump?

Use the direct connection or the session pooler, both on port 5432. Never point pg_dump or pg_restore at the transaction pooler on port 6543.

Supabase exposes three ways in (see Connect to your database):

The transaction pooler hands each statement to a potentially different backend connection and doesn't support session state the way native Postgres tools expect — Supabase's own docs flag that transaction mode doesn't support prepared statements. pg_dump needs one stable session for its snapshot, so long-running dumps against 6543 fail in confusing ways. Supabase's guidance for migrations and backup/restore is explicit: use the direct connection or session mode.

Practical rule: if your machine has IPv6 (or the project has the IPv4 add-on), use the direct string. On IPv4-only networks (most CI runners, many home ISPs), use the session pooler string — note the username there is postgres.[project-ref], not just postgres.

One more prerequisite that bites people immediately:

pg_dump: error: server version: 15.8; pg_dump version: 14.11
pg_dump: error: aborting because of server version mismatch

pg_dump refuses to talk to a server on a newer major version than itself (real report against Supabase). Install client tools at least as new as your project's Postgres version before you start.

Which schemas should you dump — and which must you exclude?

Dump your schemas (usually public, plus any custom ones). Exclude everything Supabase manages: auth, storage, realtime, extensions, graphql, graphql_public, pgbouncer, pgsodium, vault, and supabase_functions.

Why: every Supabase project is provisioned with those schemas already in place, owned by internal roles (supabase_admin, supabase_auth_admin, supabase_storage_admin). A naive full pg_dump captures all of them — and the restore then tries to recreate objects that already exist, owned by roles your postgres user cannot act as. That's precisely why supabase db dump (the CLI wrapper) runs pg_dump with a long exclusion list under the hood (CLI reference).

If a table in public has a foreign key to auth.users, that's fine: the auth schema already exists in the target project, so the constraint creates cleanly. Just remember the target's auth.users is empty — a data restore that includes rows referencing users will fail FK checks until those users exist.

How do you dump a Supabase database with pg_dump?

A schema dump of public that restores cleanly into another Supabase project looks like this:

pg_dump "postgresql://postgres.[project-ref]:[password]@aws-0-eu-central-1.pooler.supabase.com:5432/postgres" \
  --schema public \
  --no-owner \
  --no-privileges \
  --format custom \
  --file backup.dump

The flags that matter:

• --schema public — repeat for each custom schema; this implicitly skips the managed schemas without a 10-line exclusion list.
• --no-owner — otherwise the dump is full of ALTER ... OWNER TO statements referencing roles you can't become on the target.
• --no-privileges — skips GRANT/REVOKE replay (more on the consequences below).
• --format custom — required for pg_restore, enables selective restore and --list.

Add --schema-only if you only want structure, or --data-only --use-copy-style separation like the official backup/restore flow does (it dumps roles, schema, and data as three files and restores them with psql --single-transaction --variable ON_ERROR_STOP=1).

How do you restore into a new Supabase project with pg_restore?

Restore into a fresh, empty project with:

pg_restore \
  --dbname "postgresql://postgres.[target-ref]:[password]@aws-0-eu-central-1.pooler.supabase.com:5432/postgres" \
  --no-owner \
  --no-privileges \
  backup.dump

Two behaviors to know:

1. pg_restore does not stop on errors by default. It logs each failure, keeps going, and ends with a line like pg_restore: warning: errors ignored on restore: 37. If you don't read the output, you'll believe the restore succeeded when a third of your functions silently failed. Add --exit-on-error if you'd rather fail fast, or --single-transaction to make it all-or-nothing.
2. Repeat --no-owner --no-privileges at restore time. Even if the dump omitted them, it costs nothing and protects you when reusing older dump files that didn't.

Fresh project vs non-empty target: restoring into a project that already has objects in public produces a wall of ERROR: relation "..." already exists failures. The tempting fix — --clean --if-exists — issues DROP statements for every object first, which is destructive and still fails on anything owned by an internal role. The reliable pattern is: always restore into a project that has never had migrations applied. This matters even more when the restore is step one of a bigger move, like migrating a Supabase project to another region or organization.

Why does pg_restore fail with "must be owner of ..."?

Because on the Supabase platform, your postgres role is not a superuser, and many pre-provisioned objects are owned by supabase_admin or other internal roles. Any statement in the dump that touches those objects fails with SQLSTATE 42501. Real examples from Supabase's issue tracker:

ERROR: must be owner of table subscription
ERROR: must be owner of relation users (SQLSTATE 42501)
ERROR: 42501: permission denied for schema auth

The first hits when a dump includes the realtime schema (supabase/cli#3532); the second when it tries to alter auth.users (supabase/cli#3549); the third when it tries to create objects inside auth (supabase/cli#3613).

Fixes, in order of preference:

1. Don't dump those schemas in the first place (--schema public).
2. Dump with --no-owner so no OWNER TO "supabase_admin" statements exist to fail. Supabase's own migration guide tells you to comment those lines out of a schema SQL file if they slipped in.
3. If a table in your schema somehow ended up owned by supabase_admin (it happens on older projects), check with select tablename, tableowner from pg_tables where schemaname = 'public'; and reassign with ALTER TABLE public.<table> OWNER TO postgres; on the source before dumping.

What does not work on hosted Supabase: ALTER ROLE postgres SUPERUSER. You can't grant yourself superuser on the platform, so plans that assume it (common in generic Postgres restore advice) dead-end here.

What happens to RLS policies, grants, and ownership?

RLS travels well; grants do not.

• RLS policies: pg_dump emits ALTER TABLE ... ENABLE ROW LEVEL SECURITY and every CREATE POLICY, so a schema dump of public carries your policies along. Policies referencing auth.uid() or auth.jwt() work in the target because the auth schema and its functions are pre-provisioned there. If policies are the only thing you want to move, there's a lighter-weight path — see copying RLS policies between Supabase projects.
• Grants: with --no-privileges (which you need, see above), your custom GRANT/REVOKE statements are not replayed. New tables still end up API-accessible because Supabase projects define default privileges granting anon, authenticated, and service_role access to objects created in public (Postgres roles and privileges). The dangerous side effect: if you had revoked access on a sensitive table in the source, the restored copy is back to defaults — re-apply your revokes manually and treat this as a required post-restore checklist item.
• Ownership: with --no-owner, everything is owned by the role you connect as (postgres). That's what you want on Supabase.

How do you verify the restore actually worked?

Never trust the exit banner. Compare source and target explicitly:

-- run on both projects and diff the results
select count(*) as tables from information_schema.tables
  where table_schema = 'public';
select count(*) as policies from pg_policies
  where schemaname = 'public';
select count(*) as functions from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  where n.nspname = 'public';
select count(*) as triggers from information_schema.triggers
  where trigger_schema = 'public';

Then spot-check the things that fail silently most often: RLS enabled flags (select relname, relrowsecurity from pg_class), views, and enum types. If you separated schema and data, also confirm sequences were advanced (setval lines live in the data dump — restore schema-only and every serial insert starts at 1).

Manual pg_dump vs SupaClone

Honest framing: everything above is exactly what SupaClone automates. It uses the same native pg_dump/pg_restore under the hood, with a baseline-aware plan that skips the Supabase-managed schemas correctly, and it ends every run with a field-by-field verification report (cloned / skipped / failed / manual steps) instead of a scrollback full of ignored errors. It clones structure only today — schemas, tables, RLS policies, functions, triggers, indexes, views, enums, extensions, with optional Storage, Edge Functions, and Auth-config add-ons; data clones are coming soon.

If you need a one-off dump with data, or you're restoring to self-hosted/local Postgres, manual pg_dump is the right tool and this guide is the map. If what you actually want is a repeatable prod→staging structure clone, that's the job SupaClone was built for — there's a 14-day free trial that includes one successful clone (see pricing; $7/month, $70/year, or $84 lifetime). For the broader decision tree, start with how to clone a Supabase project, and if the goal is a permanent second environment, the staging environment guide covers what comes after the restore.

FAQ

Can I use pg_dump with the Supabase connection pooler?

Yes, but only in session mode on port 5432. The transaction pooler on port 6543 doesn't support the stable session semantics native Postgres tools need — Supabase recommends the direct connection or session mode for pg_dump and migrations.

Why does pg_restore say "must be owner of table" on Supabase?

Your dump contains objects owned by internal roles like supabase_admin, and the postgres role on hosted Supabase isn't a superuser. Re-dump with --schema public --no-owner --no-privileges so those statements never appear.

Should I dump the auth and storage schemas?

No. Every Supabase project ships with auth, storage, and realtime pre-provisioned and owned by internal roles. Dumping them causes ownership and "already exists" errors on restore. Auth configuration (providers, settings) lives in the Management API, not the database, and must be moved separately.

Does pg_dump copy RLS policies?

Yes — CREATE POLICY and ENABLE ROW LEVEL SECURITY statements are part of a schema dump. Custom GRANT/REVOKE statements are not replayed if you use --no-privileges, so re-apply any revokes on sensitive tables after restoring.

Why did my pg_restore "succeed" but objects are missing?

By default pg_restore continues past errors and only prints a summary like errors ignored on restore: 37 at the end. Use --exit-on-error or --single-transaction, and verify with SQL counts of tables, policies, functions, and triggers on both sides.