ClickHouse integration

Replicate your Postgres data to ClickHouse using built-in CDC capabilities

Every Managed Postgres instance comes with built-in CDC capabilities to any of your ClickHouse services. This allows you to move some or all of the data on your Postgres instance to ClickHouse and have changes in data on Postgres be reflected on ClickHouse continuous and nearly real-time. This is powered by ClickPipes under the hood.

To access this, click on ClickHouse Integration in the sidebar of your Postgres instance.

Before you proceed, ensure that your Postgres service is accessible to the ClickPipes service. This should be the case by default, but if you’ve restricted IP access you may need to grant access to some source IPs from this list based on the region where your ClickHouse service is located.

Click Replicate data in ClickHouse to start setting up your ClickPipe.

1. Configure the replication service

   Fill in the replication settings:

   • Integration name: A name for this ClickPipe
   • ClickHouse service: Select an existing ClickHouse Cloud service or create a new one
   • Postgres database: The source database to replicate from
   • Replication method: Choose one of:
     • Initial load + CDC: Import existing data and keep tables updated with new changes (recommended)
     • Initial load only: One-time snapshot of existing data with no ongoing updates
     • CDC only: Skip the initial snapshot and only capture new changes going forward

   Click Next to proceed.

2. Configure replication settings

   Fine-tune how data is replicated:

   • Sync interval (seconds): How often changes are pulled from Postgres (default: 60)
   • Parallel threads for initial load: Number of threads used during the initial snapshot (default: 4)
   • Pull batch size: Number of rows fetched per batch during replication (default: 100000)
   • Snapshot number of rows per partition: Rows per partition during the initial snapshot (default: 100000)
   • Snapshot number of tables in parallel: How many tables are snapshotted concurrently (default: 1)

   Click Next to proceed.

3. Select tables to replicate

   Choose a destination database and select which tables to replicate:

   • Destination database: Select an existing ClickHouse database or create a new one
   • Prefix default destination table names with schema name: Adds the Postgres schema as a prefix to avoid naming conflicts
   • Preserve NULL values from source: Maintains NULL values instead of converting to defaults
   • Remove deleted rows during merges: For ReplacingMergeTree tables, physically removes deleted rows during background merges

   Expand schemas and select individual tables to replicate. You can also customize destination table names and column settings.

   Click Replicate data to ClickHouse to start the replication.

4. Monitor your ClickPipe

   Once the ClickPipe starts, you’ll see it listed in the same menu. The initial snapshot of all data may take some time depending on the size of your tables.

   Click on the integration name to view detailed status, monitor progress, view errors, and manage the ClickPipe. See Lifecycle of a Postgres ClickPipe to understand the different states your ClickPipe may be in.