Postgres to ClickHouse CDC Without Kafka: Attach a Polaris Catalog as a Database

ClickHouse is where the heavy questions go: the scans and group-bys you would rather not run on the Postgres that takes your orders. The rows are still written in Postgres, so something has to carry every insert, update and delete across. That something decides how stale the numbers are and how many services you keep alive.

The common ways to get from Postgres to ClickHouse:

• ClickPipes for Postgres. Change data capture (CDC) through logical replication into ReplacingMergeTree tables, with nothing to host. ClickHouse Cloud only, billed per ClickPipe.
• PeerDB, self-hosted. The same approach on a ClickHouse you operate. You run PeerDB, Temporal and a Postgres database for PeerDB's own state.
• Debezium, Kafka and the ClickHouse Kafka engine. Changes arrive within seconds. You operate Kafka, Kafka Connect and Debezium, keep a materialized view per table, and write the merge logic for updates and deletes yourself.
• Managed extract, load, transform (ELT) tools such as Fivetran, Estuary or Airbyte Cloud. Nothing to host, with freshness in minutes by plan. Pricing is per row, per GB or per connector, and update and delete handling varies by tool.

All four land the rows in native ClickHouse tables. This tutorial builds a different shape. OLake Go, an open source (Apache 2.0) replication tool, reads the Postgres write-ahead log (WAL) and commits the changes as Apache Iceberg tables into Apache Polaris.

Polaris is an open source Iceberg Representational State Transfer (REST) catalog that you run yourself, with the files in your own bucket. ClickHouse attaches the whole catalog as one database through its DataLakeCatalog engine. DuckDB and Trino read the same tables through the same endpoint.

I ran this on one laptop, everything in Docker: Postgres 16, Polaris 1.8.0 on MinIO, OLake Go v1.1.0 and the ClickHouse 26.8 long-term support (LTS) release. The first sync copied 48 records in 14 seconds, and a second sync replayed 12 changes in 11 seconds. Every number below is from that run.

What you need

• Docker, plus curl, jq and the AWS command line tool for step 2.
• Postgres 12 or later. Step 1 starts a local Postgres 16.
• ClickHouse 26.1 or later, where DataLakeCatalog reads REST catalogs.
• About 40 minutes.

Step 1: Prepare Postgres

If your tables already exist, skip the container and the sample rows and start at the role. For a throwaway test, this runs Postgres 16 with the replication settings on the command line:

docker run -d --name pg-shop -p 5432:5432 \ -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=shop \ postgres:16 \ -c wal_level=logical -c max_replication_slots=10 \ -c max_wal_senders=10 -c wal_sender_timeout=0 

On a server you manage, wal_level is an ALTER SYSTEM SET statement followed by a restart. Hosted Postgres on Amazon Relational Database Service (RDS), Aurora, Cloud SQL, Azure, Neon or Supabase puts it behind a parameter group or a dashboard toggle.

Connect with psql -h localhost -U postgres -d shop and create two tables with 8 customers and 40 orders:

CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(200), city VARCHAR(100), tier VARCHAR(20), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW()); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), product VARCHAR(100), amount NUMERIC(10,2), status VARCHAR(20), created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW()); INSERT INTO customers (name, email, city, tier) VALUES ('Aarav Mehta','aarav@example.com','Mumbai','gold'), ('Priya Sharma','priya@example.com','Bengaluru','silver'), ('Liam Carter','liam@example.com','Austin','bronze'), ('Sofia Rossi','sofia@example.com','Milan','gold'), ('Kenji Tanaka','kenji@example.com','Tokyo','silver'), ('Emma Schulz','emma@example.com','Berlin','bronze'), ('Noah Kim','noah@example.com','Seoul','gold'), ('Zara Ali','zara@example.com','Dubai','silver'); INSERT INTO orders (customer_id, product, amount, status) SELECT 1 + (n % 8), (ARRAY['Laptop','Headphones','Monitor','Keyboard','Mouse','Webcam'])[1 + n % 6], ROUND(20 + ((n * 7919) % 120000) / 100.0, 2), (ARRAY['placed','shipped','delivered'])[1 + n % 3] FROM generate_series(1, 40) AS n; 

OLake Go reads changes with the built-in pgoutput plugin. It needs a role that can replicate, a publication for the tables and a replication slot:

CREATE ROLE olake WITH LOGIN REPLICATION PASSWORD '<password>'; GRANT USAGE ON SCHEMA public TO olake; GRANT SELECT ON ALL TABLES IN SCHEMA public TO olake; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO olake; CREATE PUBLICATION olake_pub FOR TABLE customers, orders; SELECT pg_create_logical_replication_slot('olake_slot', 'pgoutput'); -- check SHOW wal_level; -- logical SELECT slot_name, plugin, active FROM pg_replication_slots; SELECT pubname, puballtables FROM pg_publication; 

⚠️ Never point two jobs at one slot. Each reads the other's changes and both lose data, so every job gets its own slot and publication.

Step 2: Run Polaris and MinIO, then create the catalog

OLake Go needs three things before it can write: a Polaris server, a catalog inside it that points at a bucket, and a principal with the right to manage that catalog. A principal is a client id and a secret. Save this as docker-compose.yml and run docker compose up -d:

services: minio: image: minio/minio hostname: lake-minio command: server /data --console-address ":9001" environment: { MINIO_ROOT_USER: minioadmin, MINIO_ROOT_PASSWORD: minioadmin } ports: ["9000:9000", "9001:9001"] polaris: image: apache/polaris:latest depends_on: [minio] ports: ["8181:8181", "8182:8182"] environment: AWS_REGION: us-east-1 AWS_ACCESS_KEY_ID: minioadmin AWS_SECRET_ACCESS_KEY: minioadmin POLARIS_BOOTSTRAP_CREDENTIALS: POLARIS,root,<root-secret> polaris.realm-context.realms: POLARIS 

This Polaris keeps its state in memory and comes up empty after a restart, so production points it at Postgres. Now create the bucket, the catalog, the principal and its grant. The aws call needs the MinIO keys (minioadmin here) as its credentials:

P=http://localhost:8181 # 1. A bucket for the tables aws --endpoint-url http://localhost:9000 s3 mb s3://lakehouse # 2. A root token TOKEN=$(curl -s $P/api/catalog/v1/oauth/tokens -H 'Polaris-Realm: POLARIS' \ -d grant_type=client_credentials -d client_id=root -d client_secret=<root-secret> \ -d scope=PRINCIPAL_ROLE:ALL | jq -r .access_token) H=(-H "Authorization: Bearer $TOKEN" -H 'Polaris-Realm: POLARIS' -H 'Content-Type: application/json') # 3. The catalog: where the files go, and how Polaris and the engines reach the store curl -s "${H[@]}" $P/api/management/v1/catalogs -d '{"catalog": { "name": "olake", "type": "INTERNAL", "readOnly": false, "properties": {"default-base-location": "s3://lakehouse/olake"}, "storageConfigInfo": {"storageType": "S3", "allowedLocations": ["s3://lakehouse/olake"], "endpoint": "http://<minio-host>:9000/", "endpointInternal": "http://lake-minio:9000", "pathStyleAccess": true, "region": "us-east-1"}}}' # 4. A principal for OLake Go and the engines. The response holds clientId and clientSecret: keep them. curl -s "${H[@]}" $P/api/management/v1/principals -d '{"principal": {"name": "olake"}}' # 5. Roles and the grant that lets the principal create namespaces and tables in the catalog curl -s "${H[@]}" $P/api/management/v1/principal-roles -d '{"principalRole": {"name": "olake_role"}}' curl -s "${H[@]}" $P/api/management/v1/catalogs/olake/catalog-roles -d '{"catalogRole": {"name": "olake_catalog_role"}}' curl -s -X PUT "${H[@]}" $P/api/management/v1/principals/olake/principal-roles -d '{"principalRole": {"name": "olake_role"}}' curl -s -X PUT "${H[@]}" $P/api/management/v1/principal-roles/olake_role/catalog-roles/olake -d '{"catalogRole": {"name": "olake_catalog_role"}}' curl -s -X PUT "${H[@]}" $P/api/management/v1/catalogs/olake/catalog-roles/olake_catalog_role/grants -d '{"type": "catalog", "privilege": "CATALOG_MANAGE_CONTENT"}' 

The catalog block carries two store addresses, and they do different jobs:

• endpoint is what Polaris hands to the engines together with short-lived credentials. In my run <minio-host> was host.docker.internal.
• endpointInternal is what Polaris itself uses to reach MinIO. On AWS S3 you leave both out.

⚠️ Keep the trailing slash on endpoint. ClickHouse builds the object path from it, and step 6 shows what my first query returned without it.

Step 3: Start OLake UI and connect Postgres

curl -sSL https://raw.githubusercontent.com/datazip-inc/olake-ui/master/docker-compose-v1.yml | docker compose -f - up -d 

Open http://localhost:8000 and sign in with admin / password. Go to Sources, click Create Source and pick Postgres.

Under Change data capture (CDC), leave Enable CDC on and enter olake_slot as the Replication Slot and olake_pub as the Publication. Under Connection details my host was host.docker.internal, because Postgres ran in another container, with database shop and user olake.

Click Set up source. Before it saves anything, OLake Go tests the connection and checks wal_level, the replication privilege, the slot and the publication. A failed check names the setting and the value it should have.

Step 4: Add the Polaris destination

Go to Destinations, click Create Destination, and pick Apache Iceberg with Polaris as the catalog type and OAuth2 as the authentication type. Polaris and MinIO ran in other containers, so both addresses use host.docker.internal.

Click Create. OLake Go exchanges the credentials for a token, creates a namespace and a test table through Polaris, writes to the bucket and drops the test table again.

Step 5: Create the job and run the first sync

1. Open Jobs and click Create Job. Name it postgres_to_clickhouse and give it a schedule.
2. Select the source and the destination you just made.
3. Under Advanced Settings, set Target Query Engines to ClickHouse, plus DuckDB and Trino if they will read the tables too.
4. Click Next. On the Streams page, tick customers and orders.
5. Keep the sync mode on Full Refresh + CDC and the ingestion mode on Upsert. Leave the Upsert Type as OLake Go resolved it.
6. Click Create Job, then open the job's menu and choose Sync Now.

That engine list is the only format decision you make. OLake Go resolves an Iceberg delete format every listed engine can read, and for my three it chose equality deletes.

My first sync finished in 14 seconds, container start included. It records the WAL position through the slot, then copies each table in parallel chunks, so rows written during the copy are not lost.

Step 6: Query the tables in ClickHouse

In clickhouse-client, attach the catalog once, replacing <polaris-host> with the address ClickHouse reaches Polaris on:

SET allow_database_iceberg = 1; CREATE DATABASE polaris ENGINE = DataLakeCatalog('http://<polaris-host>:8181/api/catalog') SETTINGS catalog_type = 'rest', warehouse = 'olake', catalog_credential = '<client_id>:<client_secret>', oauth_server_uri = 'http://<polaris-host>:8181/api/catalog/v1/oauth/tokens', auth_scope = 'PRINCIPAL_ROLE:ALL', vended_credentials = true, region = 'us-east-1'; SHOW TABLES FROM polaris; SELECT id, name, city, tier FROM polaris.`postgres_to_clickhouse_shop_public.customers` ORDER BY id; 

OLake Go names the namespace after the job, the source database and the schema, which gave me postgres_to_clickhouse_shop_public. ClickHouse has one level of namespace, so that name and the table name sit together inside one pair of backticks.

The database is read-only, and with vended_credentials = true no store keys live in ClickHouse.

My very first SELECT failed with Code: 499 and an HTTP 404. SHOW TABLES worked, but the object key in the message contained the table path twice. The catalog endpoint had no trailing slash, and adding it (http://<minio-host>:9000/) fixed every read.

Step 7: Change rows and sync again

Back in psql, make 12 changes: 7 inserts and 5 updates.

INSERT INTO customers (name, email, city, tier) VALUES ('Maya Patel', 'maya@example.com', 'Pune', 'silver'), ('Lucas Silva', 'lucas@example.com', 'São Paulo', 'bronze'); INSERT INTO orders (customer_id, product, amount, status) VALUES (9, 'Laptop', 1299.00, 'placed'), (9, 'Mouse', 25.50, 'placed'), (10, 'Monitor', 349.99, 'placed'), (3, 'Keyboard', 89.00, 'placed'), (7, 'Webcam', 79.90, 'placed'); UPDATE customers SET tier = 'gold', updated_at = NOW() WHERE name = 'Liam Carter'; UPDATE customers SET city = 'Abu Dhabi', updated_at = NOW() WHERE name = 'Zara Ali'; UPDATE orders SET status = 'delivered', updated_at = NOW() WHERE id IN (1, 4, 7); 

Choose Sync Now again. This time OLake Go read 12 records (594 bytes) from the slot and was done in 11 seconds.

For customers it wrote one data file with 4 rows, the 2 inserts and the new versions of the 2 updated rows. A small equality delete file retires the 2 old versions by key. The orders table got the same treatment with 8 rows and 3 deletes.

Re-run the SELECT in ClickHouse without refreshing anything. Liam Carter is gold, Zara Ali has moved to Abu Dhabi, and Maya Patel and Lucas Silva are there.

ClickHouse returns 10 rows, one per key, because it applies the equality deletes while reading. Add _op_type to the column list to see how each row arrived: r for the snapshot, c for an insert and u for an update.

Read the same tables from DuckDB and Trino

Neither engine needs an export. DuckDB attaches Polaris with the same principal, and on a laptop it also wants an S3 secret for the store:

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs; CREATE SECRET polaris_secret ( TYPE iceberg, CLIENT_ID '<client_id>', CLIENT_SECRET '<client_secret>', OAUTH2_SERVER_URI 'http://<polaris-host>:8181/api/catalog/v1/oauth/tokens', OAUTH2_SCOPE 'PRINCIPAL_ROLE:ALL' ); CREATE SECRET store_secret ( TYPE s3, KEY_ID '<access key>', SECRET '<secret key>', ENDPOINT '<minio-host>:9000', URL_STYLE 'path', USE_SSL false, REGION 'us-east-1' ); ATTACH 'olake' AS polaris ( TYPE iceberg, ENDPOINT 'http://<polaris-host>:8181/api/catalog', SECRET polaris_secret, ACCESS_DELEGATION_MODE 'none' ); SELECT id, name, city, tier FROM polaris.postgres_to_clickhouse_shop_public.customers ORDER BY id; 

Trino takes one catalog file, etc/catalog/polaris.properties:

connector.name=iceberg iceberg.catalog.type=rest iceberg.rest-catalog.uri=http://<polaris-host>:8181/api/catalog iceberg.rest-catalog.warehouse=olake iceberg.rest-catalog.security=OAUTH2 iceberg.rest-catalog.oauth2.credential=<client_id>:<client_secret> iceberg.rest-catalog.oauth2.scope=PRINCIPAL_ROLE:ALL iceberg.rest-catalog.oauth2.server-uri=http://<polaris-host>:8181/api/catalog/v1/oauth/tokens iceberg.rest-catalog.vended-credentials-enabled=true fs.native-s3.enabled=true s3.region=us-east-1 s3.endpoint=http://<minio-host>:9000 s3.path-style-access=true 

DuckDB 1.5.5 and Trino 484 both returned the same 10 customers as ClickHouse. A roll-up of orders by status matched Postgres to the cent in all three: 16 delivered for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.

Tips from the run

• No underscores in the MinIO hostname. Polaris is a Java service, and Java's address parser rejects them. A container named lab_minio fails every table creation with host must not be null, hence lake-minio above.
• DuckDB may not resolve the vended store address. An address such as host.docker.internal only resolves inside Docker, so on a laptop DuckDB stops with Could not resolve hostname. The S3 secret and ACCESS_DELEGATION_MODE 'none' above are the fix.
• Expect three type changes. numeric arrives as Nullable(Float64), so round at query time when you need exact decimal arithmetic. timestamptz becomes DateTime64(6, 'UTC'), and json and jsonb arrive as strings for JSONExtract.

How this Postgres to ClickHouse route compares

ClickPipes is the simpler choice when you are on ClickHouse Cloud, ClickHouse is the only reader and you want MergeTree query speed. The route in this post earns its setup when a second engine needs the same rows, or when you want to own the files and the catalog.

Its limits, stated plainly:

• It is not MergeTree speed. A DataLakeCatalog query reads Parquet over the network with no primary index. For a dashboard that needs sub-second answers, load the hot slice into a MergeTree table on a schedule.
• Tables need a primary key. Without one, inserts replicate but updates and deletes are not applied, unless you set REPLICA IDENTITY FULL on the table.
• No deletion vectors on ClickHouse 26.8. The LTS release reads positional and equality deletes only, so keep the upsert type OLake Go resolves.
• The slot pins WAL. Postgres keeps the log until OLake Go has read it. Keep the job on a schedule, alert on pg_replication_slots lag, and drop the slot when you retire a job.
• Nothing compacts the tables for you. Every sync adds a small data file and a delete file per table.

FAQ

Can I use Amazon S3 in place of MinIO?

Yes. Use a plain S3 bucket and an Identity and Access Management (IAM) role, and leave endpoint and endpointInternal out of the catalog in step 2. On the destination form in step 4, the S3 Endpoint field stays empty.

How tight can the sync schedule be?

The interval goes down to one minute. The schedule sets how often the change stream is drained, not how often a full copy runs. Each sync ends with one commit to Polaris, and ClickHouse sees it on its next query.

What are the extra columns in every table?

OLake Go adds _olake_id, _op_type, _olake_timestamp and _cdc_timestamp to every table, plus _cdc_lsn on tables that come from Postgres. _olake_id is a hash of the primary key, and _cdc_lsn is the WAL position each change came from. Filter them out of dashboards, or use them to see when a row last changed.

How do I compact the tables once the small files pile up?

OLake Fusion compacts tables in the catalogs OLake Go writes to. Spark's rewrite_data_files is the other option, and it works through the same Polaris endpoint.

Where to go from here

• Add a column in Postgres and OLake Go adds it to the Iceberg table on the next sync. A narrowing type change fails the sync instead.
• ClickHouse Cloud uses the same DataLakeCatalog engine, as long as it can reach the Polaris server and the bucket.
• The full guide, with the command line configuration, the complete data type table and the schema change rules, is on olake.io.

If a step returns something different on your versions, paste the exact message in the comments and I will dig into it.