Ingesting data from Postgres to ClickHouse (using CDC)

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/postgres/index.md)

You can use ClickPipes to ingest data from your source Postgres database into ClickHouse Cloud. The source Postgres database can be hosted on-premises or in the cloud including Amazon RDS, Google Cloud SQL, Azure Database for Postgres, Supabase and others.

## Prerequisites[​](#prerequisites "Direct link to Prerequisites")

To get started, you first need to make sure that your Postgres database is set up correctly. Depending on your source Postgres instance, you may follow any of the following guides:

1. [Amazon RDS Postgres](/docs/integrations/clickpipes/postgres/source/rds)
2. [Amazon Aurora Postgres](/docs/integrations/clickpipes/postgres/source/aurora)
3. [Supabase Postgres](/docs/integrations/clickpipes/postgres/source/supabase)
4. [Google Cloud SQL Postgres](/docs/integrations/clickpipes/postgres/source/google-cloudsql)
5. [Azure Flexible Server for Postgres](/docs/integrations/clickpipes/postgres/source/azure-flexible-server-postgres)
6. [Neon Postgres](/docs/integrations/clickpipes/postgres/source/neon-postgres)
7. [Crunchy Bridge Postgres](/docs/integrations/clickpipes/postgres/source/crunchy-postgres)
8. [Generic Postgres Source](/docs/integrations/clickpipes/postgres/source/generic), if you're using any other Postgres provider or using a self-hosted instance.
9. [TimescaleDB](/docs/integrations/clickpipes/postgres/source/timescale), if you're using the TimescaleDB extension on a managed service or self-hosted instance.

Note

Postgres Proxies like PgBouncer, RDS Proxy, Supabase Pooler, etc., aren't supported for CDC based replication. Please make sure to NOT use them for the ClickPipes setup and instead add connection details of the actual Postgres database.

Once your source Postgres database is set up, you can continue creating your ClickPipe.

## Creating your ClickPipe[​](#creating-your-clickpipe "Direct link to Creating your ClickPipe")

Make sure you're logged in to your ClickHouse Cloud account. If you don't have an account yet, you can sign up [here](https://cloud.clickhouse.com/).

1. In the ClickHouse Cloud console, navigate to your ClickHouse Cloud Service.

2. Select the `Data Sources` button on the left-side menu and click on "Set up a ClickPipe"

3. Select the `Postgres CDC` tile

### Adding your source Postgres database connection[​](#adding-your-source-postgres-database-connection "Direct link to Adding your source Postgres database connection")

4. Fill in the connection details for your source Postgres database which you configured in the prerequisites step.

   References

   Before you start adding your connection details make sure that you have whitelisted ClickPipes IP addresses in your firewall rules. You can find the list of ClickPipes IP addresses [here](/docs/integrations/clickpipes#list-of-static-ips).
   For more information refer to the source Postgres setup guides linked at [the top of this page](#prerequisites).

#### (Optional) Setting up AWS Private Link[​](#optional-setting-up-aws-private-link "Direct link to (Optional) Setting up AWS Private Link")

You can use AWS Private Link to connect to your source Postgres database if it is hosted on AWS. This is useful if you
want to keep your data transfer private.
You can follow the [setup guide to set up the connection](/docs/integrations/clickpipes/aws-privatelink).

#### (Optional) Setting up SSH tunneling[​](#optional-setting-up-ssh-tunneling "Direct link to (Optional) Setting up SSH tunneling")

You can specify SSH tunneling details if your source Postgres database isn't publicly accessible.

1. Enable the "Use SSH Tunnelling" toggle.
2. Fill in the SSH connection details.
3. To use Key-based authentication, click on "Revoke and generate key pair" to generate a new key pair and copy the generated public key to your SSH server under `~/.ssh/authorized_keys`.
4. Click on "Verify Connection" to verify the connection.

Note

Make sure to whitelist [ClickPipes IP addresses](/docs/integrations/clickpipes#list-of-static-ips) in your firewall rules for the SSH bastion host so that ClickPipes can establish the SSH tunnel.

Once the connection details are filled in, click on "Next".

### Configuring the replication settings[​](#configuring-the-replication-settings "Direct link to Configuring the replication settings")

5. Make sure to select the replication slot from the dropdown list you created in the prerequisites step.

#### Advanced settings[​](#advanced-settings "Direct link to Advanced settings")

You can configure the Advanced settings if needed. A brief description of each setting is provided below:

- **Sync interval**: This is the interval at which ClickPipes will poll the source database for changes. This has implication on the destination ClickHouse service, for cost-sensitive users we recommend to keep this at a higher value (over `3600`).
- **Parallel threads for initial load**: This is the number of parallel workers that will be used to fetch the initial snapshot. This is useful when you have a large number of tables and you want to control the number of parallel workers used to fetch the initial snapshot. This setting is per-table.
- **Pull batch size**: The number of rows to fetch in a single batch. This is a best effort setting and may not be respected in all cases.
- **Snapshot number of rows per partition**: This is the number of rows that will be fetched in each partition during the initial snapshot. This is useful when you have a large number of rows in your tables and you want to control the number of rows fetched in each partition.
- **Snapshot number of tables in parallel**: This is the number of tables that will be fetched in parallel during the initial snapshot. This is useful when you have a large number of tables and you want to control the number of tables fetched in parallel.

### Configuring the tables[​](#configuring-the-tables "Direct link to Configuring the tables")

6. Here you can select the destination database for your ClickPipe. You can either select an existing database or create a new one.
7. You can select the tables you want to replicate from the source Postgres database. While selecting the tables, you can also choose to rename the tables in the destination ClickHouse database as well as exclude specific columns.

   Note

   If you're defining an ordering key in ClickHouse differently than from the primary key in Postgres, don't forget to read all the [considerations](/docs/integrations/clickpipes/postgres/ordering_keys) around it

### Review permissions and start the ClickPipe[​](#review-permissions-and-start-the-clickpipe "Direct link to Review permissions and start the ClickPipe")

8. Select the "Full access" role from the permissions dropdown and click "Complete Setup".

## What's next?[​](#whats-next "Direct link to What's next?")

Once you've set up your ClickPipe to replicate data from PostgreSQL to ClickHouse Cloud, you can focus on how to query and model your data for optimal performance. See the [migration guide](/docs/migrations/postgresql/overview) to assess which strategy best suits your requirements, as well as the [Deduplication strategies (using CDC)](/docs/integrations/clickpipes/postgres/deduplication) and [Ordering Keys](/docs/integrations/clickpipes/postgres/ordering_keys) pages for best practices on CDC workloads.

For common questions around PostgreSQL CDC and troubleshooting, see the [Postgres FAQs page](/docs/integrations/clickpipes/postgres/faq).

- [Prerequisites](#prerequisites)- [Creating your ClickPipe](#creating-your-clickpipe)
    - [Adding your source Postgres database connection](#adding-your-source-postgres-database-connection)- [Configuring the replication settings](#configuring-the-replication-settings)- [Configuring the tables](#configuring-the-tables)- [Review permissions and start the ClickPipe](#review-permissions-and-start-the-clickpipe)- [What's next?](#whats-next)

Was this page helpful?