How to Sync Data From Postgres to ClickHouse With Airbyte
Airbyte is an open-source data integration platform with about 600 prebuilt connectors. It moves data from a database or SaaS API into a warehouse or analytical store on a schedule, using the same connector code whether you self-host or pay for Airbyte Cloud.
This walkthrough goes from nothing to a working Postgres → ClickHouse sync in about 25 minutes, self-hosted. The sync mode choice at the end is where people get burned, so that part gets its own section.
How do you self-host Airbyte?
The old docker-compose path is deprecated. abctl is the supported local installer and it uses a kind-based Kubernetes cluster under Docker.
Requirements: Docker with 8 GB allocated (4 GB crashes on first connector install), 4 CPUs, and kubectl.
curl -LsfS https://get.airbyte.com | bash -
abctl local install --port 8000
The install takes 5-10 minutes and pulls roughly 3 GB of images. When it finishes, open http://localhost:8000 and set an email and password — it’s the local admin account, no verification email is sent.
Two things that go wrong: on a Mac with Docker Desktop under 8 GB, the install fails partway with pods stuck in Pending; run abctl local status to see which. And abctl writes its kubeconfig to ~/.airbyte/abctl/abctl.kubeconfig — if you have a project-local kubectl config, you’ll be looking at the wrong cluster.
How do you connect Postgres as a source?
Pick the Postgres source connector (Sources → New source → Postgres), then fill in host, port, database, username and password.
For a database on your host machine from inside the Airbyte cluster, use host.docker.internal rather than localhost. localhost resolves to the Airbyte pod itself and the connection times out with a misleading error.
Critical setting: Update Method. Options are Detect Changes with Xmin (default) and Read Changes using Write-Ahead Log (CDC). Xmin is simple and needs no database config; CDC needs wal_level = logical and a replication slot, and it’s the only one that catches hard deletes. Pick Xmin for the first sync — CDC setup is a separate project.
Run Test and save. It takes 20-60 seconds and checks the user can read information_schema.
How do you connect ClickHouse as a destination?
Add the ClickHouse destination connector. Connection details:
- Host:
http://host.docker.internal, port8123for HTTP - Database: your database name
- Username / password: a user with
CREATE TABLEandINSERTon the target database
Enable SSL only if your ClickHouse actually terminates TLS — clicking it reflexively is a common cause of “Connection refused” against a local instance.
On save, Airbyte creates tables from scratch on first sync using the source schema. For ClickHouse it defaults to MergeTree with the primary key taken from the source’s primary key. That works, but ordering by an ID rather than a timestamp means your analytical queries won’t use the index. If the sync is for dashboards rather than mirroring, change the ordering key afterwards — Airbyte won’t manage that for you.
Which sync mode should you pick?
This is the decision that determines whether your destination has duplicate rows.
| Mode | Behaviour | Use when |
|---|---|---|
| Full Refresh / Overwrite | Truncates destination table, rewrites all rows | Small tables under a few million rows |
| Full Refresh / Append | Appends every sync | Almost never — produces duplicates by design |
| Incremental / Append | Only new rows since last cursor value | Append-only event streams where history never changes |
| Incremental / Deduplicated + History | New rows plus changes, with dedupe in the destination | Transactional tables with updates |
Airbyte’s default for new connections is Full Refresh / Overwrite, chosen for safety. It’s also the slowest and most expensive mode. A 4-million-row table resynced hourly means 96 million rows written per day and a destination that’s constantly being truncated — dashboards reading mid-truncate show zero rows.
For most operational tables, Incremental with a cursor on updated_at is correct. Add Deduplicated + History if the source updates rows, and make sure the destination has a primary key defined — plain MergeTree won’t dedupe, ReplacingMergeTree will.
The incremental cursor needs updated_at to be maintained properly. Rows updated by a bulk SQL statement that forgets the column are lost silently. Check the source before trusting the sync.
How do you verify a sync worked?
Run Sync now and watch the job log. Success shows records emitted and records committed. Those two numbers should match.
Then verify in the destination:
SELECT count() FROM postgres_public_orders;
SELECT max(updated_at) FROM postgres_public_orders;
Compare with the source. A small gap is normal — the sync reads at a point in time. A large gap means the cursor is wrong.
Set the Sync frequency to something honest. Hourly syncs of a table that changes twice a day waste compute and create a false sense of freshness. Daily is fine for reporting tables and much cheaper in Airbyte Cloud, where compute is billed per sync.
Self-hosted or Airbyte Cloud?
Self-hosted Community edition is free and unlimited. You pay in operations: upgrades, connector version pinning, and the memory footprint of 4 GB+ of containers. Every few months a connector update breaks a schema, and you’re the one debugging it.
Cloud starts around $10/month for the standard plan plus per-connector and data-volume charges. For a team without infrastructure people, that’s usually money well spent — the connector maintenance alone is a half-day a quarter.
Self-host if you can’t send data to a third party, or if you’re running many high-volume connectors where cloud data pricing gets ugly.
Quick Summary
- Install with
abctl local install --port 8000; you need Docker with 8 GB for connectors to run. - Use
host.docker.internalfor host services —localhostinside the container fails. - Default Full Refresh/Overwrite re-reads everything; switch operational tables to Incremental.
- Use Deduplicated + History with a real destination primary key, or expect duplicates.
- Verify by comparing
count()andmax(cursor)between source and destination. - Self-hosted is free but you own upgrades; Cloud is roughly $10/month plus usage and saves that maintenance.</}
