← Vibe coding database safety

Moving your database off Supabase

Updated · DBShifts · commands tested on real PostgreSQL, MySQL and MongoDB servers

Short answer

A Supabase project is a Postgres database with services around it. pg_dump copies your tables, functions and policies; signed-up users live in the auth schema and uploaded files live in Storage, so each needs its own step. Create the roles your policies name before restoring, dump over the direct connection or session pooler, and compare row counts before you switch.

Key facts

  • Supabase databases are standard Postgres, so pg_dump and pg_restore work with them.
  • Row level security policies and grants name Supabase's roles (anon, authenticated, service_role). Restoring them into plain Postgres fails on those lines until the roles exist.
  • Policies that call auth.uid() need an auth.uid() function on the new database too; on Supabase it reads the signed-in user from the request.
  • Uploaded files are objects in Supabase Storage, not rows in your tables; the database only holds their metadata.
  • For pg_dump, use the direct connection or the session-mode pooler; the transaction-mode pooler is built for short app queries.

1. Dump your schema and data

Dump the schemas your app uses (usually public). --no-owner and --no-privileges leave out Supabase-specific owners and grants so the restore does not depend on them.

ShellDump your tables, functions and policies
pg_dump "$SUPABASE_DB_URL" --schema=public --no-owner --no-privileges --format=custom --file=public.dump

2. Prepare the new database

Your policies still name anon and authenticated, and may call auth.uid(). Create those first on the new Postgres, then decide how your new auth sets the user id (the function below reads it from a setting your API sets per request).

SQLOn the new database: the roles and auth.uid() your policies name
CREATE ROLE anon NOLOGIN;
CREATE ROLE authenticated NOLOGIN;
CREATE ROLE service_role NOLOGIN BYPASSRLS;
CREATE SCHEMA IF NOT EXISTS auth;
CREATE OR REPLACE FUNCTION auth.uid() RETURNS uuid
LANGUAGE sql STABLE AS $
  SELECT nullif(current_setting('request.jwt.claim.sub', true), '')::uuid
$;

3. Restore

Restore into the new database, then run the row-count query on both sides. Expect exactly one error on a new database: schema "public" already exists. A dump of one schema includes the schema itself, and every new database already has public; the restore carries on, and pg_restore exits with status 1 for that error alone. Any other error is a real one.

ShellRestore into the new database
pg_restore --no-owner --dbname="$NEW_DATABASE_URL" public.dump
SQLPostgreSQL: exact row count of every table
SELECT table_name,
       (xpath('/row/n/text()',
              query_to_xml(format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name),
                           false, true, '')))[1]::text::bigint AS row_count
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY table_name;

4. Users and files

Keep Supabase Auth if you only want to move the data. To move users to another auth provider, export auth.users: passwords are stored as bcrypt hashes, which most providers can import so people keep their passwords. Copy Storage files with the S3-compatible API or the CLI, and keep the paths so the metadata still matches.

ShellExport your users (to move them to another auth provider)
pg_dump "$SUPABASE_DB_URL" --data-only --table=auth.users --format=custom --file=auth-users.dump

5. Cut over without downtime

For a database that cannot stop, copy it while it keeps running and stream the changes made during the copy (logical replication, or change data capture), then switch the app's connection string when the two are level. Check row counts and content before the switch, not after.

Common mistakes

Restoring before creating the roles

The restore stops at the first policy that names anon or authenticated. Create the roles, then restore.

Forgetting the files

Storage objects are not in the dump. Without them, every image and upload link on the new setup is broken.

Dumping through the transaction pooler

Use the direct connection or session mode for pg_dump and pg_restore.

Switching on matching row counts alone

Compare content on the important tables. A replication gap can leave counts equal and rows different.

How DBShifts helps

  • DBShifts copies Postgres to Postgres (and between 11 engines) while the source keeps running, streams the changes made during the copy, and checks row counts, checksums and foreign keys before you switch.
  • Every one of the 49 pairs between the seven core engines passes a live test harness.

Questions people ask

How do I export my Supabase database?

Run pg_dump against the project's direct connection string (or the session pooler). Dump the public schema, plus auth.users if you are moving users, with --no-owner so the restore does not need Supabase's roles.

Can I move from Supabase to Neon or AWS RDS?

Yes. Both are Postgres: create the roles your policies use, restore the dump, and point the app at the new connection string. Keep or replace Supabase Auth and Storage separately.

Will my users have to reset their passwords?

Not if your new auth provider can import bcrypt hashes, which is how Supabase Auth stores passwords in auth.users.

Move Postgres without downtime, and prove it

Copy while live, stream the changes, verify every row. Free to start.

7 guides in this series. See the checklist.