Restore a Supabase dump by hand

capydb import runs the managed path and is what most migrations should use. To keep your RLS policies, capydb import --from-supabase-dump <file> runs the recipe on this page for you (see Migrate from Supabase). Reach for this page when you need to control the order yourself - for example to apply migrations newer than the dump between the steps.

A Supabase pg_dump is not portable as-is. It references extensions.geography, auth.role(), publications for supabase_realtime, ACLs for roles that do not exist outside Supabase, and policies on schemas you do not own. Restoring it into plain Postgres takes a filtered table of contents and a specific order.

Before you startCopy Anchor Link

Two things that are not obvious and cost hours if you get them wrong.

Use a pg_dump that matches the source major. A newer pg_dump can emit syntax an older server rejects. Check the source with select version() and pin the client to it:

docker run --rm postgres:17 pg_dump --version

The direct host may be unreachable. On a modern Supabase project db.<ref>.supabase.co resolves to an IPv6 address only. Without IPv6 egress (most CI runners, many laptops) you cannot reach it at all. Use the session-mode pooler on port 5432 - it is a full session and pg_dump works through it. Do not use port 6543: that is transaction mode and it breaks pg_dump.

Read the pooler host from the management API rather than guessing - the aws-0 / aws-1 prefix varies per project:

curl -s -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \
  "https://api.supabase.com/v1/projects/$PROJECT_REF/config/database/pooler"

Take the dumpCopy Anchor Link

Custom format, so the table of contents can be filtered later. Include every schema your application owns - public is the natural filter and it is usually wrong, because app schemas hide behind it.

docker run --rm -e PGPASSWORD -v "$PWD:/out" postgres:17 \
  pg_dump -h aws-1-<region>.pooler.supabase.com -p 5432 -U postgres.<project-ref> \
          -d postgres --format=custom --no-owner --no-privileges \
          --schema=public --schema=<your-other-schemas> \
          -f /out/source.dump

Verify it is restorable before trusting it - the file existing is not the same as the archive being intact:

docker run --rm -v "$PWD:/out" postgres:17 pg_restore --list /out/source.dump | wc -l

Convert the policiesCopy Anchor Link

capydb migrate rls --source-url "$SOURCE_URL" \
  --mode supabase-compat --role-model single \
  --out ./rls-bundle

--role-model single is what applies FORCE ROW LEVEL SECURITY to every table. It is not optional: your application connects as the database owner, and an owner bypasses row security unless the table is FORCEd. Without it your policies are decorative and nothing tells you so. The alternative is --role-model split, where request traffic runs as the project's runtime role app_user instead of the owner - it needs that role enabled first; see role models.

The bundle includes a claims-based service escape: a transaction whose verified claims carry "role": "service_role" skips the row filters, which is where your service-key call sites go. Set that role only from server code that used the service key. If nothing in your app used the service key, add --no-service-escape: the report then lists a policy that applied only to service_role as access that is now denied.

Restore, in this orderCopy Anchor Link

The order is load-bearing. Each step exists because the one before it is a prerequisite.

1. The compat shim (capyrls_01_prelude.sql) - creates the auth schema. SQL-language function bodies referencing auth.* are validated at CREATE, so this must exist before any schema restores.
2. Pre-data, with POLICY, ACL and PUBLICATION entries filtered out.
3. Data.
4. Post-data - indexes, constraints, triggers.
5. capyrls_02_force_rls.sql, then capyrls_03_policies.sql - they need the tables to exist.

Filtering the table of contents:

pg_restore --list source.dump > full.list
grep -vE '^;|^$' full.list \
  | grep -vE ' (POLICY|ACL|PUBLICATION|EVENT TRIGGER|SUBSCRIPTION|SCHEMA) ' \
  > keep.list

for section in pre-data data post-data; do
  pg_restore --section=$section -L keep.list --no-owner --no-privileges \
             -f $section.sql source.dump
done

Then apply the five steps with psql -v ON_ERROR_STOP=1.

The six trapsCopy Anchor Link

1. --no-privileges does not drop policies. It drops grants. Policies are separate TOC entries and must be filtered explicitly, or the dump's CREATE POLICY ... TO authenticated fails against roles that do not exist. It also drops schema USAGE grants, which you may need to re-add.
2. public is not the only schema you own. Any app schema outside public is silently missing otherwise, and you will not notice until a query fails. List them explicitly at dump time.
3. Extension schema qualification. Supabase installs extensions into a schema named extensions; if you enable them on the cell with capydb extensions enable they land in public. Every extensions. reference in the dump then fails. Either create the extensions in a matching schema before restoring, or rewrite extensions. to public. in the emitted SQL.
4. Identity sequences cannot be dropped on their own. When wiping a cell between rehearsals, drop tables before sequences and materialise the object names first - a lazily evaluated regclass cast over an already-dropped OID degrades to a bare number and the DROP becomes a syntax error.
5. Extensions that are installed but unreferenced. cube, earthdistance, pg_net and the dashboard-only hypopg / index_advisor are frequently present with zero call sites. Filter them from the dump rather than trying to install them. capydb migrate scan --source-url reports dependent-object counts so you can tell "load-bearing" from "installed once".
6. Realtime publications travel in the dump. The supabase_realtime publication entries and any trigger calling realtime.send() reference a schema that will not exist. Drop them, and re-home the fan-out into your application after the write commits.

VerifyCopy Anchor Link

Count rows on both sides. With FORCE ROW LEVEL SECURITY applied, the owner is subject to policies too, so a naive count(*) under-reports - lift FORCE inside a transaction and roll it back:

begin;
  alter table public.<table> no force row level security;
  select count(*) from public.<table>;
rollback;

Then run the check that actually matters: a two-user denial test. Set claims for user A, read a row belonging to user B, and assert you get nothing. Run it against a table that was not already FORCEd on the source. Reading the policy list is not a substitute - the failure mode here is silent, and this is the only thing that catches it.