Replicate data to ClickHouse

Neon's logical replication feature allows you to replicate data from your Neon Postgres database to external destinations.

ClickHouse is an open-source column-oriented database management system designed for real-time analytical queries on large volumes of data. Using ClickPipes, ClickHouse Cloud's native continuous data ingestion service, you can easily set up Change Data Capture (CDC) from your Neon Postgres database to stream real-time changes directly into ClickHouse.

In this guide, you will learn how to prepare your Neon Postgres database and configure a Postgres CDC ClickPipe to replicate data to ClickHouse Cloud.

Prerequisites

• A ClickHouse Cloud account
• A Neon account
• Read the important notices about logical replication in Neon before you begin
• Neon API key (for API or Terraform) with permissions to modify project settings.
• ClickHouse Cloud credentials - Key ID, Key Secret, and Organization ID obtained from the ClickHouse Cloud console and organization settings (for API or Terraform)

Prepare your source Neon database

Before setting up the ClickPipe in ClickHouse Cloud, you need to prepare your source Neon Postgres database for logical replication. This involves enabling logical replication, creating a publication, and ensuring your tables are properly configured for replication.

Enable logical replication in Neon

To enable logical replication in Neon:

You can verify that logical replication is enabled by running the following query from the Neon SQL Editor:

SHOW wal_level;
 wal_level
-----------
 logical

Create a Postgres role for replication

It's recommended that you create a dedicated Postgres role for replicating data. The role must have the REPLICATION privilege. The default Postgres role created with your Neon project and roles created using the Neon CLI, Console, or API are granted membership in the neon_superuser role, which has the required REPLICATION privilege.

Grant schema access to your Postgres role

If your replication role does not own the schemas and tables you are replicating from, make sure to grant the required access. For example, the following commands grant access to all tables in the public schema to Postgres role replication_user:

GRANT USAGE ON SCHEMA "public" TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO replication_user;

Granting SELECT ON ALL TABLES IN SCHEMA and using ALTER DEFAULT PRIVILEGES ensures that your replication user can read all existing tables and any future tables added to the schema.

Configure replica identity for tables

For a table to be replicated using ClickPipes, it must have either a PRIMARY KEY or a REPLICA IDENTITY defined.

If you have tables without a primary key, you must set the replica identity to FULL so that UPDATE and DELETE operations work correctly:

ALTER TABLE your_table_name REPLICA IDENTITY FULL;

Create a publication

Create a Postgres publication that includes the tables you want to replicate. It is recommended to create a publication that includes only the tables you want to replicate, rather than using FOR ALL TABLES, to minimize unnecessary data replication and reduce the amount of WAL data generated.

To create a publication named clickpipes_pub for specific tables, run the following command:

CREATE PUBLICATION clickpipes_pub FOR TABLE tb1, tb2;

Replace tb1 and tb2 with the actual names of the tables you want to replicate. You can include as many tables as needed in the publication.

ClickPipes will use this publication to capture changes from your specified tables. Refer to the Postgres docs if you need to add or remove tables from your publication.

Allow inbound traffic (optional)

If you are using Neon's IP Allow feature to limit IP addresses that can connect to Neon, add ClickHouse's IPs to your allowlist in Neon.

For instructions, see Configure IP Allow. You can find the list of IPs that need to be allowlisted in the ClickHouse Cloud documentation.

Create a ClickPipe in ClickHouse Cloud

Now that your Neon source database is prepared, you can create the CDC integration in ClickHouse.

References

• ClickHouse: Ingesting data from Postgres to ClickHouse (using CDC)
• ClickHouse: Neon Postgres source setup guide
• ClickHouse: ClickPipes for Postgres FAQ
• ClickHouse Cloud API documentation
• ClickHouse Terraform provider documentation