> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-vortex-format.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 = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return ;
};
export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
if (link) {
return
Beta
;
}
return
Beta feature
;
};
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](/cloud/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**.
Automated schema migration
When this option is opted for, the ClickPipe will automatically pull the schema of your source database and apply it to your Managed Postgres service, during the [Setup phase](/integrations/clickpipes/postgres/lifecycle#setup) of the ClickPipe after it is created.
This feature assumes an **empty target database**, as it pulls every database object from the source database regardless of what tables you select later in the wizard. If you already have existing data in your target database, or are aiming for a more customized setup, you must opt for the [**Manual**](#manual-schema-migration) mode instead.
Select the destination database from the dropdown, or click **Create a new database** to provision one.
Monitoring
You can track the progress of the schema migration in the ClickPipes detail view. The **Logs** will indicate the status of the schema migration, and any errors encountered will be displayed there as well.
This mode has the following limitations:
* Pipes using SSH tunneling cannot use automated schema migration. The schema must be [exported and imported manually](#manual-schema-migration).
Manual schema migration
For cases where you already have existing data in your target database, or are aiming for a more customized setup versus the clean slate which the automated mode expects, you can opt for the **Manual** mode here.
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**.
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)