ClickHouse Managed Postgres source setup guide

ClickHouse Managed Postgres is a fully managed Postgres service in ClickHouse Cloud. It is the fastest Postgres source to connect to ClickPipes: logical replication is enabled on every instance, the ClickPipes IP addresses are allowed by default, and the connection details live in the same console as your ClickHouse service. There are three ways to start replicating from a ClickHouse Managed Postgres instance. All of them create a regular Postgres ClickPipe, so everything in the Postgres ClickPipe documentation applies once the pipe is running.

Option 1: Sync to ClickHouse from the Postgres service

Open your ClickHouse Managed Postgres service in the ClickHouse Cloud console and click Sync to ClickHouse in the sidebar. The wizard pre-fills the source connection and lets you pick a destination ClickHouse service, the replication method, and the tables to replicate. No user, publication, or network setup is required. Follow the Sync to ClickHouse guide for a step-by-step walkthrough. For low latency replication, you can also choose WalShadow, a native replication engine that reads at file system level, currently in private preview.

Option 2: Select ClickHouse Managed Postgres as a source in ClickPipes

If you prefer to start from the destination side, open your ClickHouse service, go to Data Sources, and choose ClickHouse Managed Postgres from the list of sources. You then provide the connection details of your Postgres instance as you would for any other Postgres source.

Connection details

Open your ClickHouse Managed Postgres service and click Connect to see the host, port, database, user, and password. Use the direct Postgres connection, not the via PgBouncer toggle: ClickPipes doesn’t support replication through PgBouncer or any other connection pooler. See Connection for details. With clickhousectl, clickhousectl cloud postgres get <postgres-id> shows the hostname and username. It never returns the password: that’s shown once, by cloud postgres create, and clickhousectl cloud postgres reset-password <postgres-id> --generate sets a new one.

Creating a user with permissions

You can connect with the default postgres user. For production we recommend a dedicated, read-only replication user, exactly as for any other Postgres source. Connect to your instance with psql and run:

Create a dedicated user for ClickPipes:

CREATE USER clickpipes_user PASSWORD 'some-password';

Grant schema-level, read-only access to the user. The following example shows permissions for the public schema. Repeat these commands for each schema containing tables you want to replicate:

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

Grant replication privileges to the user:

ALTER USER clickpipes_user WITH REPLICATION;

Create a publication with the tables you want to replicate. We strongly recommend only including the tables you need in the publication to avoid performance overhead.

Any table included in the publication must either have a primary key defined or have its replica identity configured to FULL. See the Postgres FAQs for guidance on scoping.

To create a publication for specific tables:

CREATE PUBLICATION clickpipes FOR TABLE table_to_replicate, table_to_replicate2;

To create a publication for all tables in a specific schema:

CREATE PUBLICATION clickpipes FOR TABLES IN SCHEMA "public";

Logical replication

Logical replication is already enabled on every ClickHouse Managed Postgres instance. There is no wal_level setting or parameter group to change. You can confirm with:

SHOW wal_level; -- should be logical

Network access

ClickHouse Managed Postgres accepts connections from ClickPipes by default. If you have configured IP filters on your Postgres service, add the ClickPipes static IP addresses for the region of your ClickHouse service to the allowed list. With clickhousectl, add one rule per address on the direct Postgres port, and run clickhousectl cloud postgres get <postgres-id> to check the result:

clickhousectl cloud postgres update <postgres-id> \
  --add-ip-allow '<clickpipes-ip>:5432=clickpipes'

TLS

ClickHouse Managed Postgres instances present a certificate signed by a CA that is unique to the instance and not publicly trusted. ClickPipes enables certificate verification by default, so the connection check fails until you do one of the following:

Download the CA certificate bundle from the Settings tab of your Postgres service and provide it with Upload CA in the ClickPipe connection settings. This is the recommended option. See Verified TLS connection for where to find the bundle.
Turn on Skip certificate verification in the ClickPipe connection settings. The connection stays encrypted but the server identity isn’t checked.

If you create the pipe with clickhousectl, fetch the bundle and pass it with --ca-certificate, as shown in option 3.

Option 3: Create the pipe with clickhousectl

clickhousectl creates the same pipe from the command line. Set up the user and the clickpipes publication as in option 2 first. If you use the default postgres user instead, drop --publication-name and the CLI creates a publication for the mapped tables. Creating a pipe needs an API key; OAuth login is read-only. Look up the hostname, then save the instance’s CA bundle:

clickhousectl cloud postgres get <postgres-id>
clickhousectl cloud postgres certs get <postgres-id> --output pg-ca.pem

Create the pipe on the destination ClickHouse service, passing the CA bundle with --ca-certificate:

clickhousectl cloud clickpipe create postgres <clickhouse-service-id> \
  --name events-sync \
  --host <postgres-hostname> \
  --pg-database postgres \
  --username clickpipes_user \
  --password '<password>' \
  --ca-certificate pg-ca.pem \
  --publication-name clickpipes \
  --table-mapping public.events:events

The pipe creates ReplacingMergeTree tables. Read the current state with SELECT ... FROM events FINAL WHERE _peerdb_is_deleted = 0. For the full walkthrough, including waiting for the pipe to reach Running and verifying the data, see the CLI view of the Postgres ClickPipe guide.

What’s next?

You can now create your ClickPipe and start ingesting data from your ClickHouse Managed Postgres instance into ClickHouse Cloud. Native CDC from ClickHouse Managed Postgres to ClickHouse is included at no additional cost; see Pricing for details.

ClickPipes for Streaming

ClickPipes for Object Storage

ClickPipes for CDC

Programmatic access

Security and access

Networking

ClickHouse Managed Postgres source setup guide

Option 1: Sync to ClickHouse from the Postgres service

Option 2: Select ClickHouse Managed Postgres as a source in ClickPipes

Connection details

Creating a user with permissions

Logical replication

Network access

TLS

Option 3: Create the pipe with clickhousectl

What’s next?

​Option 1: Sync to ClickHouse from the Postgres service

​Option 2: Select ClickHouse Managed Postgres as a source in ClickPipes

​Connection details

​Creating a user with permissions

​Logical replication

​Network access

​TLS

​Option 3: Create the pipe with clickhousectl

​What’s next?

Option 1: Sync to ClickHouse from the Postgres service

Option 2: Select ClickHouse Managed Postgres as a source in ClickPipes

Connection details

Creating a user with permissions

Logical replication

Network access

TLS

Option 3: Create the pipe with clickhousectl

What’s next?