Fabric

Guide

Postgres or MySQL → ClickHouse

The fastest combination end to end: source in, MergeTree history out, rows arriving within seconds.

The fastest combination to stand up end to end: a Postgres or MySQL database in, ClickHouse out. Every change becomes a new row in a plain MergeTree table within seconds, so you can query the history as soon as replication starts.

Both source families take the same path through the product — only the connection form differs. Pick yours under PostgreSQL or MySQL; the destination is ClickHouse. What it costs is on Pricing.

Before you start

On the source database, once:

  • Postgres: wal_level = logical (a restart), a login with REPLICATION and SELECT on the tables you sync, and a publication named dbz_publication — create it as the administrator ( CREATE PUBLICATION dbz_publication FOR ALL TABLES;), or let Fabric create it if the login is a superuser. For tables with large text, jsonb, or bytea columns, also set REPLICA IDENTITY FULL (see the Postgres destination): an unchanged large column is otherwise read back from the source, one query per table per commit.
  • MySQL (8.0+): binlog_format = ROW, binlog_row_image = FULL, binlog_row_metadata = FULL, a unique server_id, and a login with REPLICATION SLAVE, REPLICATION CLIENT, and SELECT.

The exact grants, and what stays with your administrator, are on Least-privilege logins. On the ClickHouse side you need a user with CREATE DATABASE, CREATE TABLE, ALTER TABLE, INSERT, and SELECT on system.columns — the ClickHouse page has the three GRANT statements.

Network access and TLS

Fabric's runners connect out to your Postgres or MySQL database and your destination, so nothing needs to be opened inbound. Outbound, the database firewall must allow the runner's egress address: TODO(E6-egress) — the fixed address is not published yet; it lands here and on the security page when it is. Until then, a connection that times out rather than being refused is usually the firewall.

On the hosted service a new source starts at Require TLS: the server must offer TLS, the certificate is not verified, and disable/prefer are refused. Choose Verify full when you want the certificate checked against a CA you paste into the form — managed services publish theirs (RDS, Cloud SQL, Azure). Two real messages and their fixes:

  • TLS to db.example.com failed: the server does not offer TLS and this source requires it (require). Turn TLS on at the database, or change the mode to disable or prefer (self-hosted installs only — the hosted service refuses those two).
  • TLS to db.example.com failed: the server's certificate did not validate against the CA this source carries (verify-full). The CA is missing, wrong, or the wrong one for this server. Paste the issuing CA, or drop back to require to skip verification.

A certificate that is valid but does not carry the host name you dialled gets its own sentence, and points at verify-ca (the CA check without the name check) — the full mode list, and what each one asserts, is on Sources.

The five steps

  1. Add the destination. Destinations → Add destination → ClickHouse. Host, port (8123 for HTTP, 8443 for HTTPS, 9000 for native), protocol, username, password, and the database to connect to. Pick the protocol your server speaks, and flip Secure connection if the HTTP interface terminates TLS; Test connection before you create it.
  2. Add the source. Syncs → New sync, pick the PostgreSQL or MySQL card, and fill the connection. On the hosted service the mode starts at Require TLS; switch to a verifying mode if you have the CA. Test connection proves reachability and auth before you create anything. A PostgreSQL source also asks for a replication slot name — any name will do, and the wizard will not continue without one; Fabric creates that slot on the server when the pipeline starts. A MySQL source asks for Server ID instead — any number that is not the server's own server_id.
  3. Pick the tables. The wizard scans the source and lists its tables. Select the ones to sync (or Select all) and check the two per-row defaults: identifier columns (id — change it if the table has no id) and the target table name. Target namespace is the ClickHouse database the tables land in; Fabric creates it.
  4. Create the sync. Transforms are optional; on the Sink step pick your ClickHouse destination — the picker starts on Iceberg, and a sync writes to exactly one destination. Press Test connection there (the step will not unlock without it), then Review & save, then Save sync. Saving creates the pipeline; it does not start it.
  5. Start replication and watch rows arrive. Open the sync from Syncs and press Start. The badge turns snapshotting while the initial load runs, then running; the stats row starts reporting rows and lag.

See the rows arrive

clickhouse-client --query "SELECT count(), max(__source_ts_ms) FROM raw.orders"

raw is the namespace you chose, orders the source table. The table holds append-only history — one row per change — so current state is a read-side query; the ClickHouse page has the argMax form and the metadata columns explain __op and __deleted.

When it does not work

  • The run refuses to start with a publication error. The message names the table and the fix: publication dbz_publication is missing 1 synced table(s): public.orders … run: ALTER PUBLICATION dbz_publication ADD TABLE public.orders; Run that on the source, then start again.
  • The pipeline runs but nothing lands. Check the destination's grants first: the writer creates the namespace database and the table on the first commit, so a user without CREATE DATABASE fails at the first write and the error lands in the sync's logs. The Logs tab on the sync page is the runner's own stdout.
  • Rows arrive but the snapshot seems to miss a table. A table that is not in the publication snapshots fine (the snapshot is a plain SELECT) and then never sees a change — which is exactly why Fabric checks the publication before the snapshot. Add the table to the publication as above.