/infra-postgres

Migrate an existing Postgres to ClickHouse Cloud Postgres

Move a database from another Postgres provider (Neon, Supabase, RDS, Aurora, Cloud SQL, self-hosted, ...) into a ClickHouse Cloud Postgres service with pg_dump and pg_restore.

Do not use ClickPipes for this. clickhousectl cloud clickpipe create postgres replicates Postgres into a ClickHouse service for analytics; it cannot target a Postgres service.

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:

psql "$SOURCE_URL" -c "SHOW server_version;"
psql "$SOURCE_URL" -c "SELECT pg_size_pretty(pg_database_size(current_database()));"
psql "$SOURCE_URL" -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"
psql "$SOURCE_URL" -c "\dn"

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:

psql "$TARGET_URL" -c "SELECT name, default_version FROM pg_available_extensions WHERE name IN ('<ext1>', '<ext2>');"

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:

pg_dump --version
pg_restore --version

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

pg_dump "$SOURCE_URL" \
  --format directory \
  --jobs 4 \
  --no-owner \
  --no-privileges \
  --file dump

• --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:

psql "$TARGET_URL" -c "CREATE DATABASE <db>;"
TARGET_DB_URL=$(sed -E 's#(:[0-9]+)/postgres([?]|$)#\1/<db>\2#' <<<"$TARGET_URL")

pg_restore \
  --dbname "$TARGET_DB_URL" \
  --jobs 4 \
  --no-owner \
  --no-privileges \
  dump

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:

psql "$TARGET_DB_URL" -c "ANALYZE;"

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:

SELECT table_schema, table_name,
       (xpath('/row/c/text()',
              query_to_xml(format('SELECT count(*) AS c FROM %I.%I', table_schema, table_name),
                           false, true, '')))[1]::text::bigint AS row_count
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

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).