Migrate

pg_dump/pg_restore is a one-shot copy: writes that land on the source after the dump starts are not migrated. Confirm with the user that writes can be paused for the duration of the dump and restore (or that the source is not yet in production) before starting.

Step 1: Inspect the source

Get a direct (non-pooled) connection string for the source. Transaction-mode poolers (PgBouncer, Supavisor) can break pg_dump.

• Neon: neonctl connection-string <branch> --project-id <project-id> --database-name <db> (non-pooled by default; the pooled host contains -pooler)
• Supabase: the “Direct connection” string, not the pooler
• Others: the provider’s primary endpoint

Store it in .env as SOURCE_URL, then record what you are moving:

Step 2: Create the target

Follow cloud.md Steps 1–3 to authenticate and create a service, with two migration-specific choices:

• --pg-version must be the same or newer major version than the source.
• Pick the region closest to the source to keep dump/restore time down.

create returns the password and connection string once — write the connection string to .env immediately as TARGET_URL. It connects as postgres to the postgres database with sslmode=require. Wait until cloud postgres get <id> reports state=running.

Check that every extension from Step 1 is available on the target:

Provider-specific extensions won’t exist on the target; exclude them in Step 4. If an extension the application uses is missing, stop and tell the user before continuing.

Step 3: Check client versions

pg_dump must be the same or newer major version than the source server:

If the local client is older, run the tools from the official image instead, e.g. docker run --rm -v "$PWD:/work" -w /work postgres:18 pg_dump ....

Step 4: Dump the source

• --format directory + --jobs dumps tables in parallel; dump/ must not already exist.
• --no-owner --no-privileges — the source’s roles (e.g. neon_superuser, Supabase’s authenticated) don’t exist on the target, so ownership and grants are dropped; everything is owned by the restoring user.
• Exclude provider-managed objects that only work on the source platform:
  • --exclude-schema neon_auth on Neon, if the project has Neon Auth enabled
  • --exclude-extension <name> (pg_dump 17+) for each provider-specific extension found in Step 2

Step 5: Restore into the target

Create a database with the same name as the source, then restore into it. Build TARGET_DB_URL by swapping only the database path segment — the username is also postgres, so a plain /postgres substitution corrupts the URL:

pg_restore keeps going on errors and prints errors ignored on restore: N at the end. Any non-zero count must be explained to the user before continuing. Then refresh planner statistics, which are not carried over:

Step 6: Validate

Compare exact row counts for every table on both sides. Run this against the source and the target database and diff the output:

Schemas excluded in Step 4 (e.g. neon_auth) appear only on the source side of the diff; every other row must match.

Also compare \di (indexes), \dv (views), and \ds (sequences) — pg_dump sets sequence values, so the next nextval() on the target should continue from the source.

Step 7: Cut over

1. Recreate application roles on the target (CREATE ROLE ... LOGIN PASSWORD ... plus grants) — pg_dump doesn’t migrate roles.
2. Point the application’s DATABASE_URL at the target and restart it.
3. Leave the source running until the user confirms the application is healthy. Never delete the source database or project unless the user explicitly asks.

What is not migrated

Roles and role memberships, grants (dropped by --no-owner --no-privileges), replication slots and publications, server configuration, and planner statistics. Apply runtime settings with clickhousectl cloud postgres config patch (see cloud.md).