> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-sync-clickhouse-operator-docs-7e82242.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Migrate PostgreSQL data using Data sources in ClickHouse Cloud
> Learn how to migrate your PostgreSQL database to ClickHouse Managed Postgres using the built-in Data sources import wizard in ClickHouse Cloud.
export const Image = ({img, alt, size}) => {
return
;
};
export const PrivatePreviewBadge = () => {
return
{'Private preview in ClickHouse Cloud'}
;
};
Migrate to Managed Postgres using ClickHouse Cloud
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](/products/managed-postgres/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.
Step 1: Connect to your source database
Open the [ClickHouse Cloud console](https://clickhouse.cloud) 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:
```shell theme={null}
pg_dump \
-h \
-U \
-d \
--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:
```shell theme={null}
psql \
-h \
-p 5432 \
-U \
-d \
-f pg.sql
```
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:
| Setting | Default | Description |
| ------------------------------------- | ------- | --------------------------------------------- |
| Sync interval (seconds) | 10 | How frequently the replication slot is polled |
| Parallel threads for initial load | 4 | Number of threads for the bulk copy phase |
| Pull batch size | 100,000 | Rows fetched per replication batch |
| Snapshot number of rows per partition | 100000 | Partition size for large table snapshots |
| Snapshot number of tables in parallel | 1 | Tables 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:
```sql theme={null}
SELECT COUNT(*) FROM public.orders;
```
**Stop writes on the source.** Pause application writes. To enforce read-only mode during cutover:
```sql theme={null}
ALTER DATABASE SET default_transaction_read_only = on;
```
**Confirm replication is caught up.** Compare the latest row on source and target:
```sql theme={null}
-- Run on both source and target
SELECT MAX(id), MAX(updated_at) FROM public.orders;
```
**Reset sequences.** Align sequences with the current maximum values in each table:
```sql theme={null}
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:
```sql theme={null}
SELECT pg_drop_replication_slot('');
```