Skip to main content
Skip to main content
Edit this page

Migrate to Managed Postgres using ClickHouse Cloud

Private preview

ClickHouse Cloud includes a built-in import wizard that migrates your external PostgreSQL database into a Managed Postgres service. The wizard handles the source connection, schema export and import, replication settings, and table selection in five guided steps.

Prerequisites​

  • Access to your source PostgreSQL database with a user that has replication privileges.
  • A ClickHouse Managed Postgres service as the migration target. If you don't have one yet, see the quickstart.
  • pg_dump and psql installed on your local machine. Both ship with the standard PostgreSQL client tools.

Considerations before migrating​

  • DDL propagation: continuous replication (CDC) captures DML operations and ADD COLUMN. Other DDL changes such as DROP COLUMN and ALTER COLUMN aren't propagated and must be applied manually on the target.
  • Foreign key constraints: to prevent ingestion from being blocked by foreign key checks, you'll temporarily set session_replication_role = replica on the target role. This is covered in step 3 below.

Step 1: Connect to your source database​

Open the ClickHouse Cloud console and select your Managed Postgres service.

In the left sidebar, click Data sources.

Click Start import.

Fill in the connection details for your source PostgreSQL database: host, port, username, password, and database name. Enable TLS if your source requires it.

If you require a private connection to your source database, you can opt for SSH tunneling and provide the necessary SSH details. This allows the migration to securely connect to databases that aren't publicly accessible.

Choose an ingestion method:

  • Initial load + CDC — copies existing data, then keeps the target in sync with ongoing changes.
  • Initial load only — one-time copy, no ongoing replication.
  • CDC only — skips the initial copy and replicates only new changes from this point forward.

Click Next.

Step 2: Export your database schema​

The wizard displays a pg_dump command pre-filled with your source connection details. Run it in a terminal:

pg_dump \
  -h <source_host> \
  -U <source_user> \
  -d <source_database> \
  --schema-only \
  -f pg.sql

This creates pg.sql in your current directory.

Click Next.

Step 3: Import the schema into your Managed Postgres service​

Select the destination database from the dropdown, or click Create a new database to provision one.

The wizard displays a psql command to apply the schema dump to your Managed Postgres service. Run it in a terminal:

psql \
  -h <target_host> \
  -p 5432 \
  -U <target_user> \
  -d <target_database> \
  -f pg.sql

After the schema is applied, set session_replication_role to replica on the target role so that foreign key constraints don't block ingestion:

ALTER ROLE <target_role> SET session_replication_role TO 'replica';

Click Next.

Step 4: Configure ingestion settings​

Specify the publication to use for logical replication. If you leave this blank, a publication is created automatically.

Expand Advanced replication settings to tune throughput:

SettingDefaultDescription
Sync interval (seconds)10How frequently the replication slot is polled
Parallel threads for initial load4Number of threads for the bulk copy phase
Pull batch size100,000Rows fetched per replication batch
Snapshot number of rows per partition100000Partition size for large table snapshots
Snapshot number of tables in parallel1Tables snapshotted concurrently

Click Next.

Step 5: Select tables​

Select the tables you want to replicate. Tables are grouped by schema. Select individual tables or expand a schema to pick all of them.

Click Create migration.

Monitor the migration​

After creating the migration, you'll see it listed in Data sources with a Running status.

Click the migration to open the detail view. The Tables tab shows the initial load progress for each table, including rows processed, partitions, and average time per partition. The Metrics tab shows replication lag and throughput once CDC begins.

Post-migration tasks​

Once the initial load is complete and, if using CDC, replication lag is near zero:

Validate row counts. Spot-check critical tables on both source and target before switching traffic:

SELECT COUNT(*) FROM public.orders;

Stop writes on the source. Pause application writes. To enforce read-only mode during cutover:

ALTER DATABASE <source_db> SET default_transaction_read_only = on;

Confirm replication is caught up. Compare the latest row on source and target:

-- Run on both source and target
SELECT MAX(id), MAX(updated_at) FROM public.orders;

Re-enable constraints and restore the replication role. Apply any indexes, constraints, and triggers you deferred during import, then reset the target role:

ALTER ROLE <target_role> SET session_replication_role TO 'origin';

Reset sequences. Align sequences with the current maximum values in each table:

DO $$
DECLARE r RECORD;
BEGIN
    FOR r IN
        SELECT
            n.nspname AS schema_name,
            c.relname AS table_name,
            a.attname AS column_name,
            pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        JOIN pg_attribute a ON a.attrelid = c.oid
        WHERE c.relkind = 'r'
            AND a.attnum > 0
            AND NOT a.attisdropped
            AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        IF r.seq_name IS NOT NULL THEN
            EXECUTE format(
                'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 0) + 1, false)',
                r.seq_name, r.column_name, r.schema_name, r.table_name
            );
        END IF;
    END LOOP;
END $$;

Cut over application traffic. Point reads and writes to your Managed Postgres service and monitor for errors, constraint violations, and replication health.

Clean up. Once you've cut over and confirmed the new service is healthy, delete the migration from Data sources. If you used CDC, drop the replication slot from the source to free resources:

SELECT pg_drop_replication_slot('<slot_name>');

Next steps​