Postgres to ClickHouse: The Complete Replication Guide

Filament is the best way to replicate Postgres to ClickHouse as of September 2026. It is one Apache 2.0 binary that checksums every batch with CRC32-C on both sides of the write and resumes from checkpoints instead of restarting. It reads Postgres in up to 64 parallel shards and lands typed ReplacingMergeTree tables with no per-row or per-GB meter.

What is Postgres to ClickHouse replication?

Postgres to ClickHouse replication keeps a copy of transactional tables in ClickHouse so analytics run on columnar storage instead of the production database. The copy is refreshed by a full snapshot, an incremental read through a timestamp cursor, or change data capture (CDC) from the write-ahead log. ClickHouse's own migration guide calls "bulk loading followed by periodic updates" a viable option.

How do you replicate Postgres to ClickHouse with Filament?

Install one binary, declare a source, a sink, and a pipeline, and run it. The route below backfills once, then upserts only changed rows on a schedule.

Takeaway. One YAML file produces a typed, deduplicating ClickHouse table that resumes from its last checkpoint after any failure.

Install and connect

The installation page offers a shell installer, Homebrew, Docker, and Helm, and the local CLI "does not require Postgres or NATS."

curl -fsSL https://getgalaxy.io/filament/install | sh

filament source create production \
  --source-connector postgres \
  --source-connection-method url \
  --source-dsn-env POSTGRES_DSN

filament sink create analytics \
  --sink-connector clickhouse \
  --sink-host ch.internal.example.com \
  --sink-username default \
  --sink-password-env

curl -fsSL https://getgalaxy.io/filament/install | sh

filament source create production \
  --source-connector postgres \
  --source-connection-method url \
  --source-dsn-env POSTGRES_DSN

filament sink create analytics \
  --sink-connector clickhouse \
  --sink-host ch.internal.example.com \
  --sink-username default \
  --sink-password-env

curl -fsSL https://getgalaxy.io/filament/install | sh

filament source create production \
  --source-connector postgres \
  --source-connection-method url \
  --source-dsn-env POSTGRES_DSN

filament sink create analytics \
  --sink-connector clickhouse \
  --sink-host ch.internal.example.com \
  --sink-username default \
  --sink-password-env

The ClickHouse sink defaults to the secure native port 9440 with TLS and LZ4 compression, the setup ClickHouse Cloud requires. It authenticates against the default database and creates the destination if missing, so grant the user both.

Declare the pipeline

Filament's CLI page accepts the same declaration as YAML. Here it is for an orders table with an updated_at column.

version: 1

sources:
  production:
    type: postgres
    config:
      connection_method: url
      dsn: env:POSTGRES_DSN
      schema: public

sinks:
  analytics:
    type: clickhouse
    config:
      host: ch.internal.example.com
      username: default
      password: env:CLICKHOUSE_PASSWORD

pipelines:
  orders-to-clickhouse:
    source:
      ref: production
    sink:
      ref: analytics
    resources:
      - orders
    sync_mode: incremental
    write_mode

version: 1

sources:
  production:
    type: postgres
    config:
      connection_method: url
      dsn: env:POSTGRES_DSN
      schema: public

sinks:
  analytics:
    type: clickhouse
    config:
      host: ch.internal.example.com
      username: default
      password: env:CLICKHOUSE_PASSWORD

pipelines:
  orders-to-clickhouse:
    source:
      ref: production
    sink:
      ref: analytics
    resources:
      - orders
    sync_mode: incremental
    write_mode

version: 1

sources:
  production:
    type: postgres
    config:
      connection_method: url
      dsn: env:POSTGRES_DSN
      schema: public

sinks:
  analytics:
    type: clickhouse
    config:
      host: ch.internal.example.com
      username: default
      password: env:CLICKHOUSE_PASSWORD

pipelines:
  orders-to-clickhouse:
    source:
      ref: production
    sink:
      ref: analytics
    resources:
      - orders
    sync_mode: incremental
    write_mode

Run it with filament run orders-to-clickhouse. For a nightly rebuild, set sync_mode to full and write_mode to replace. Filament then swaps in a staging table with an atomic EXCHANGE TABLES at commit, so "failed replace runs leave the destination exactly as it was," per the sink docs.

What the first run produces

The Postgres source captures a high watermark and backfills the table through its primary key. It then promotes the route to steady-state incremental "so changes committed during the backfill are replayed rather than lost." Large tables split into as many as 64 parallel ranges. The ranges are frozen in the checkpoint, so a resumed run reads the same plan.

In ClickHouse you get a ReplacingMergeTree table ordered by the source primary key, typed columns instead of JSON blobs, and two nullable _filament_ audit columns. New source columns arrive through ADD COLUMN IF NOT EXISTS, add-only.

Every batch of about 10,000 rows carries a CRC32-C checksum that the sink recomputes at its final in-memory boundary. A mismatch "fails the run" instead of landing bad data, per the integrity docs.

Schedule the catch-up

Attach one cron schedule to the pipeline through the pipelines API with a timezone and a SKIP overlap policy. Each run reads rows past the durable cursor with a 300-second lookback, and the duplicates converge through the ReplacingMergeTree key.

The ClickHouse sink is alpha today, per its docs, with the Postgres source at beta, and it lists full and incremental reads with replace, append, and upsert writes. A table whose hard deletes must land in ClickHouse is the one narrow case for another tool.

How do ClickPipes, PeerDB, Airbyte, and Fivetran handle this route?

Each one runs the route and adds infrastructure, a meter, or a license that Filament does not. Their shared strength is log-based CDC with deletes today.

Takeaway. Reach for one of these only when a table's hard deletes must land in ClickHouse today.

ClickPipes for Postgres

ClickPipes is the first-party, generally available CDC path into ClickHouse Cloud and the strongest managed option. It bills per GB loaded and per GB replicated plus hourly compute, cannot target self-hosted ClickHouse, and "primary key updates in PostgreSQL can't be properly replayed in ClickHouse by default," per its FAQ.

PeerDB open source

PeerDB is the engine behind ClickPipes and is free to self-host, which is its strength. Its LICENSE file is AGPLv3 despite the README badge. Its docker-compose runs ten services including Temporal and MinIO, since "PeerDB stages PostgreSQL data in MinIO within the Docker stack," per the PeerDB README.

Airbyte

Airbyte's Postgres source is certified and its ClickHouse destination now writes typed columns. Its standard and xmin modes track only inserts and updates, so deletes need CDC and a slot, per the source docs. The core is ELv2 licensed, and on the public Postgres to Postgres cohort it needed 10,393 seconds where Filament needed 115.

Fivetran

Fivetran's connector breadth and GA maturity are real, and its ClickHouse Cloud destination uses SharedReplacingMergeTree. It ships only as SaaS, so rows leave your network, and it bills by monthly active rows. Its query-based delete capture "does not preserve details about deleted rows," per the Postgres connector docs.

The rest of the field

Estuary Flow lands deletes but is BSL licensed and bills per GB plus per connector. Debezium needs Kafka and Kafka Connect, which the Altinity Sink Connector folds into one process. dlt merges over HTTP with no ReplacingMergeTree option, per its ClickHouse docs, ingestr is FSL 1.1 licensed, and Sling gates CDC behind a paid tier.

What are the gotchas on Postgres to ClickHouse?

Most problems come from ClickHouse's merge model and from Postgres replication slots. Filament's defaults handle the first, and the second only applies to log-streaming tools.

• ReplacingMergeTree "removes duplicate entries with the same sorting key" only during background merges, so read with FINAL until parts merge, per the ClickHouse docs. Filament keys the table on the source primary key.

• ClickHouse recommends "inserting data in batches of at least 1,000 rows" in the native format, per its insert guide. Filament defaults to 10,000-row native LZ4 batches.

• Upsert tables need a primary key, and a cursor version needs a NOT NULL timestamp column. Keyless tables can only be read in full.

• Filament's incremental mode needs no replication slot, so an idle slot cannot pin WAL. Log-streaming tools inherit the Postgres warning that slots "will prevent removal of required resources even when there is no connection using them," per the logical decoding docs.

Which tool fits which job?

Filament fits every reader who runs their own software.

Engine speed has been measured in public, but not on this route. Filament moved 298,270,427 rows in 114.83 seconds on the Postgres to Postgres cohort. No public run exists for Postgres to ClickHouse, so this guide makes no speed claim for it, and the Debezium figure describes its initial snapshot, not steady-state streaming.

Which tool should you pick for Postgres to ClickHouse?

Pick by the constraint that breaks first. If it is data leaving your network, a per-GB or per-row bill, a ten-service stack, or a copyleft license, Filament is the pick because it clears all four. If it is proof that every batch arrived intact, Filament is the pick because it checksums both sides of the write.

If it is resuming a 300 million row backfill after a network blip, Filament is the pick because checkpoints and frozen shard plans let the run continue. The one narrow case is a table whose hard deletes must land in ClickHouse today. Send that table through ClickPipes or PeerDB and keep the rest on Filament.

Filament is maintained by Galaxy and documented at the Filament docs site. Related guides cover Postgres to Snowflake, Postgres to S3, Postgres to Apache Iceberg, database replication, change data capture, and the Postgres to Postgres benchmark.

Frequently asked questions

What is the best way to replicate Postgres to ClickHouse?

Filament is the best way for snapshot and incremental replication. It runs as one Apache 2.0 binary inside your network, verifies every batch with a CRC32-C checksum on both sides of the write, resumes from checkpoints, and lands typed ReplacingMergeTree tables with no per-row meter.

How do I sync Postgres to ClickHouse with Filament?

Create a Postgres source and a ClickHouse sink, then a pipeline with your tables, an incremental read mode, and an upsert write mode. Filament backfills each table through its primary key, promotes the route to incremental, and re-runs on the cron schedule you attach.

Does Filament need a staging bucket for ClickHouse?

No. Filament converts Arrow batches to typed ClickHouse columns and inserts over the native protocol with LZ4 compression, about 10,000 rows at a time. There is no external bucket or storage integration to configure, only a run-scoped staging table in replace mode.

How does Filament handle duplicate rows in ClickHouse?

Upsert modes write to a ReplacingMergeTree keyed on the source primary key. ClickHouse deduplicates at background merge time, so query with FINAL to read the current state before parts merge. The version is insert order or your incremental cursor.

Does Postgres to ClickHouse replication need wal_level logical?

Only for log-based CDC. Filament's incremental mode reads through a timestamp cursor over a normal connection, so it needs a primary key and an updated_at column, not logical replication. ClickPipes and PeerDB require wal_level logical, a publication, and a replication slot.

Can ClickPipes replicate to self-hosted ClickHouse?

No. ClickPipes for Postgres is a ClickHouse Cloud feature, billed at $0.10 per GB for the initial load and $0.20 per GB for continuous CDC, plus hourly compute. For self-hosted ClickHouse, run Filament, or PeerDB if you need log-based deletes today.

What license is PeerDB under?

The LICENSE file in the PeerDB repository is the GNU Affero General Public License version 3, even though the README badge says ELv2. The open source stack runs ten Docker services including Temporal and a MinIO staging bucket. Filament is Apache 2.0 and runs as one process.

Is Fivetran or Airbyte better for Postgres to ClickHouse?

Neither beats Filament for a team that runs its own software. Fivetran bills by monthly active rows and ships only as SaaS, so data leaves your network. Airbyte tracks only inserts and updates in its standard mode and took 10,393 seconds on the Postgres to Postgres cohort where Filament took 115.