Replicate data to a ClickHouse database on DoubleCloud

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 that allows you to query billions of rows in milliseconds. Its architecture is designed to handle analytical queries efficiently, which makes it ideal for data warehousing and analytics applications. Thanks to the columnar storage format, data can be compressed and retrieved more efficiently, allowing some analytical queries to execute 100 times faster compared to traditional databases like Postgres.

DoubleCloud is a managed data platform that helps engineering teams build data infrastructure with zero-maintenance open-source technologies.

In this guide, you will learn how to replicate data from a Neon Postgres database to a managed ClickHouse cluster with DoubleCloud Transfer — a real-time data replication tool. It natively supports ClickHouse data types, data mutations, automated migrations (adding columns), as well as emulating insertions and deletions. With Transfer, you can replicate your data to both managed ClickHouse clusters on DoubleCloud and on-premise ClickHouse instances.

Prerequisites

• A DoubleCloud account

• A Neon account

• The ClickHouse client installed on your local machine.

  tip

  If you don't have the ClickHouse client, you can install it with the following command:

  curl https://clickhouse.com/ | sh

  It downloads the official binary for your operating system and installs both the ClickHouse client and ClickHouse itself.

Enable logical replication in Neon

To enable logical replication in Neon:

1. Select your project in the Neon Console.
2. On the Neon Dashboard, select Project settings.
3. Select Beta.
4. Click Enable to enable logical replication.

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

We recommend using 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 Console, CLI, 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 access. Run these commands for each schema:

GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <role_name>;

Granting SELECT ON ALL TABLES IN SCHEMA instead of naming the specific tables avoids having to add privileges later if you add tables to your publication.

Unlike replicating to other destinations, you don't need to configure a publication and replication slot manually. DoubleCloud Transfer does that for you automatically.

Add DoubleCloud Transfer's IPs to the allowlist

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

# IPv6
2a05:d014:e78:3500::/56

# IPv4
3.77.1.232
3.74.181.206
3.78.156.2
3.77.29.32
3.125.212.122

For instructions, see Configure IP Allow. You'll need to do this before you can validate your connection in the next step. If you are not using Neon's IP Allow feature, you can skip this step.

Create a managed ClickHouse cluster on DoubleCloud

1. Log in to the DoubleCloud console.
2. In the left menu, select Clusters, click Create cluster, and select ClickHouse.
3. Select cluster parameters.

1. Under Basic settings, enter the cluster name, for example clickhouse-dev.

2. Click Submit at the bottom of the page. Creating a cluster takes around five minutes depending on the provider, region, and settings.

3. After the cluster status changes from Creating to Alive, select it in the cluster list.

4. On the Overview tab, find the Connection strings section. Copy the Native interface connection string.

5. Paste the connection string to ClickHouse client and connect to the cluster.

   The output should look as follows:

   Connected to ClickHouse server version 23.8.9.
   ach-euc1-az2-s1-1.<cluster_name>.at.double.cloud :)

   :) means that the cluster is ready to receive commands.

6. Create a database:

   CREATE DATABASE IF NOT EXISTS <database_name>

7. Make sure that the database has been created:

   SHOW DATABASES

   ┌─name───────────────┐
   │ INFORMATION_SCHEMA │
   │ _system            │
   │ default            │
   │ <database_name>    │  // your database
   │ information_schema │
   │ system             │
   └────────────────────┘

Create endpoints in DoubleCloud

Before you create a transfer in DoubleCloud, you need to create a source endpoint that fetches data from Neon and a target endpoint that writes the data to ClickHouse.

To create a source endpoint:

1. In the left menu in the console, select Transfer and navigate to the Endpoints tab.

2. Click Create endpoint → Source.

3. Under Basic settings, select PostgreSQL as the source type.

4. Enter a name for your source endpoint, for example neon.

5. Under Endpoint parameters, enter connection details for your Neon database. You can get these details from your Neon connection string, which you'll find in the Connection Details widget on the Dashboard of your Neon project. For example, let's say this is your connection string:

   postgres://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require

   From this string, the values would show as below. Your actual values will differ, with the exception of the port number.

   • Host: ep-cool-darkness-123456.us-east-2.aws.neon.tech
   • Port: 5432
   • Username: alex
   • Password: AbC123dEf
   • Database Name: dbname

6. Click Test connection and if it's successful, click Submit.

To create a target endpoint:

1. In the left menu in the console, select Transfer and navigate to the Endpoints tab.

2. Click Create endpoint → Target.

3. Under Basic settings, select ClickHouse as the target type.

4. Enter a name for your source endpoint, for example clickhouse.

5. If you created a managed ClickHouse cluster in DoubleCloud, select it as the target endpoint in Connection settings → Managed cluster.

   If you want to transfer data to a ClickHouse instance elsewhere, select On-premise in Connection settings → Connection type and specify the connection details.

6. Enter the database name.

7. Click Test connection and if it's successful, click Submit.

Create a transfer in DoubleCloud

1. In the left menu in the console, select Transfer and click Create transfer.
2. Under Endpoints, select the source and target endpoints you created in the previous step.
3. Enter the transfer name, for example neon-to-clickhouse.
4. Under Transfer settings, select Snapshot and replication as the transfer type and specify transfer parameters if needed.

1. Click Submit to create the transfer.

2. On the transfer page, click Activate.

   When the data has transferred, the transfer status changes to Done.

References

• DoubleCloud get started with ClickHouse guide
• DoubleCloud get started with Transfer guide