> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Migrate PostgreSQL data using Data sources in ClickPipes
> Learn how to migrate your PostgreSQL database to ClickHouse Managed Postgres using ClickPipes.
export const Image = ({img, alt, size}) => {
return
;
};
export const galaxyOnClick = eventName => () => {
try {
if (typeof window !== "undefined" && window.galaxy && eventName) {
window.galaxy.track(eventName, {
interaction: "click"
});
}
} catch (e) {}
};
export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
if (link) {
return
Beta
;
}
return ;
};
ClickHouse Cloud now offers ClickPipes to migrate your external PostgreSQL database into a Managed Postgres service. This built-in integration provides a streamlined experience to connect to your source database, export the schema, import it into Managed Postgres, and set up continuous replication.
Prerequisites
* Access to your source PostgreSQL database with a user that has replication privileges. Follow the setup guide for your source:
* [Amazon RDS Postgres](/integrations/clickpipes/postgres/source/rds)
* [Amazon Aurora Postgres](/integrations/clickpipes/postgres/source/aurora)
* [Supabase Postgres](/integrations/clickpipes/postgres/source/supabase)
* [Google Cloud SQL Postgres](/integrations/clickpipes/postgres/source/google-cloudsql)
* [Azure Flexible Server for Postgres](/integrations/clickpipes/postgres/source/azure-flexible-server-postgres)
* [Neon Postgres](/integrations/clickpipes/postgres/source/neon-postgres)
* [Crunchy Bridge Postgres](/integrations/clickpipes/postgres/source/crunchy-postgres)
* [TimescaleDB](/integrations/clickpipes/postgres/source/timescale)
* [Generic Postgres Source](/integrations/clickpipes/postgres/source/generic) for any other provider or self-hosted instance
* 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.
If you run into issues during migration, check the [Managed Postgres Migrations FAQ](/products/managed-postgres/migrations/faq) for common errors and solutions.
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('');
```
Next steps
* [Managed Postgres quickstart](/products/managed-postgres/quickstart)
* [Managed Postgres connection details](/products/managed-postgres/connection)
* [ClickPipes Postgres FAQ](/integrations/clickpipes/postgres/faq)