== [0s] postgres 16: logical WAL, reachable from docker on 172.17.0.1 == [5s] seed 15 tables CREATE TABLE INSERT 0 20000 CREATE TABLE INSERT 0 500 CREATE TABLE INSERT 0 2000 CREATE TABLE INSERT 0 10000 CREATE TABLE INSERT 0 30000 CREATE TABLE INSERT 0 25000 CREATE TABLE INSERT 0 8000 CREATE TABLE INSERT 0 40000 CREATE TABLE INSERT 0 300 CREATE TABLE INSERT 0 50000 CREATE TABLE INSERT 0 50000 CREATE TABLE INSERT 0 100000 CREATE TABLE INSERT 0 50000 CREATE TABLE INSERT 0 20000 CREATE TABLE INSERT 0 100000 DO public.audit_log|50000 public.carts|10000 public.coupons|300 public.events|100000 public.inventory|2000 public.invoices|30000 public.notifications|40000 public.order_items|100000 public.orders|50000 public.payments|50000 public.products|500 public.reviews|8000 public.sessions|20000 public.shipments|25000 public.users|20000 == [8s] install ClickHouse + Keeper + PeerDB on this runner warning: one clickhouse host: no replica, a host loss is an outage generated CHLIFT_CH_PASSWORD, CHLIFT_CLUSTER_SECRET, CHLIFT_S3_SECRET, CHLIFT_PEERDB_CATALOG_PASSWORD in /tmp/tmp.yRN6exWckS/demo.env (mode 0600; values not shown) wrote demo.yaml ClickHouse build for every host: 26.3.39.7 d1: ClickHouse 26.3.39.7 installed d1: wrote /etc/clickhouse-server/config.d/chlift.xml d1: wrote /etc/clickhouse-server/users.d/chlift.xml d1: wrote /etc/chlift/client.xml d1: clickhouse-server restarted d1: wrote /opt/chlift/peerdb/compose.yml d1: wrote /opt/chlift/peerdb/.env d1: wrote /opt/chlift/peerdb/s3.json d1: wrote /opt/chlift/peerdb/dynamicconfig.yaml d1: wrote /opt/chlift/peerdb/search-attr.sh d1: peerdb stable-v0.37.10 running (api on a localhost port of compose project chlift-peerdb), staging http://172.17.0.1:8333/peerdb ok d1: keeper answers: state standalone ok keeper quorum: 1 member(s), 1 leader(s), 0/0 followers synced ok d1: clickhouse answers: 26.3.39.7 ok d1: no read-only replicas: read-only replicas: 0 ok replication: insert on d1, read on every replica: row seen on d1 ok d1: peerdb api answers: ok d1: clickhouse reads/writes the peerdb staging bucket: http://172.17.0.1:8333 ok d1: keeper answers: state standalone ok keeper quorum: 1 member(s), 1 leader(s), 0/0 followers synced ok d1: clickhouse answers: 26.3.39.7 ok d1: no read-only replicas: read-only replicas: 0 ok replication: insert on d1, read on every replica: row seen on d1 ok d1: peerdb api answers: ok d1: clickhouse reads/writes the peerdb staging bucket: http://172.17.0.1:8333 == [101s] plan + start: snapshot all 15 tables migrate public.events 100000 rows ORDER BY (user_id, at, id) toYYYYMM(at) note: sorted by user_id first: an UPDATE that changes a row's user_id leaves the old row live in ClickHouse (migrate check reports it); don't migrate tables whose rows move between user_ids note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with user_id, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.order_items 100000 rows ORDER BY (created_at, id) toYYYYMM(created_at) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with created_at, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.audit_log 50000 rows ORDER BY (at, id) toYYYYMM(at) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with at, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.payments 50000 rows ORDER BY (created_at, id) toYYYYMM(created_at) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with created_at, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.orders 50000 rows ORDER BY (user_id, created_at, id) toYYYYMM(created_at) note: sorted by user_id first: an UPDATE that changes a row's user_id leaves the old row live in ClickHouse (migrate check reports it); don't migrate tables whose rows move between user_ids note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with user_id, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.notifications 40000 rows ORDER BY (user_id, created_at, id) toYYYYMM(created_at) note: sorted by user_id first: an UPDATE that changes a row's user_id leaves the old row live in ClickHouse (migrate check reports it); don't migrate tables whose rows move between user_ids note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with user_id, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.invoices 30000 rows ORDER BY (issued_on, id) toYYYYMM(issued_on) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with issued_on, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.shipments 25000 rows ORDER BY (shipped_at, id) toYYYYMM(shipped_at) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with shipped_at, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.sessions 20000 rows ORDER BY (id) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now migrate public.users 20000 rows ORDER BY (created_at, id) toYYYYMM(created_at) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with created_at, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.reviews 8000 rows ORDER BY (user_id, created_at, id) toYYYYMM(created_at) note: sorted by user_id first: an UPDATE that changes a row's user_id leaves the old row live in ClickHouse (migrate check reports it); don't migrate tables whose rows move between user_ids note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with user_id, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.carts 10000 rows ORDER BY (user_id, created_at, id) toYYYYMM(created_at) note: sorted by user_id first: an UPDATE that changes a row's user_id leaves the old row live in ClickHouse (migrate check reports it); don't migrate tables whose rows move between user_ids note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with user_id, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.inventory 2000 rows ORDER BY (id) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now migrate public.products 500 rows ORDER BY (created_at, id) toYYYYMM(created_at) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with created_at, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse migrate public.coupons 300 rows ORDER BY (valid_until, id) toYYYYMM(valid_until) note: sort key and partitioning cannot change after the table is created (PeerDB #4604): review them now note: needs REPLICA IDENTITY FULL: its sort key starts with valid_until, and a DELETE (retention cleanup included) would otherwise leave the row live in ClickHouse postgres: ALTER TABLE "public"."events" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."order_items" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."audit_log" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."payments" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."orders" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."notifications" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."invoices" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."shipments" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."users" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."reviews" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."carts" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."products" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."coupons" REPLICA IDENTITY FULL postgres: CREATE PUBLICATION hot_tables_pub FOR TABLE "public"."events", "public"."order_items", "public"."audit_log", "public"."payments", "public"."orders", "public"."notifications", "public"."invoices", "public"."shipments", "public"."sessions", "public"."users", "public"."reviews", "public"."carts", "public"."inventory", "public"."products", "public"."coupons" wrote plan.yaml: review it, then run `chlift migrate start` postgres: ALTER TABLE "public"."events" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."order_items" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."audit_log" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."payments" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."orders" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."notifications" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."invoices" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."shipments" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."users" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."reviews" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."carts" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."products" REPLICA IDENTITY FULL postgres: ALTER TABLE "public"."coupons" REPLICA IDENTITY FULL postgres: CREATE PUBLICATION hot_tables_pub FOR TABLE "public"."events", "public"."order_items", "public"."audit_log", "public"."payments", "public"."orders", "public"."notifications", "public"."invoices", "public"."shipments", "public"."sessions", "public"."users", "public"."reviews", "public"."carts", "public"."inventory", "public"."products", "public"."coupons" clickhouse: database demo (Replicated) on 1 replica(s) peerdb: mirror hot_tables created ({"workflowId":"hot_tables-peerflow"}); snapshot then CDC. Follow with `chlift migrate status` mirror hot_tables: RUNNING, 0 rows synced by CDC, replication slot holds 0 MB of WAL public.events postgres ~100000 d1 100000 public.order_items postgres ~100000 d1 100000 public.audit_log postgres ~50000 d1 50000 public.payments postgres ~50000 d1 50000 public.orders postgres ~50000 d1 50000 public.notifications postgres ~40000 d1 40000 public.invoices postgres ~30000 d1 30000 public.shipments postgres ~25000 d1 25000 public.sessions postgres ~20000 d1 20000 public.users postgres ~20000 d1 20000 public.reviews postgres ~8000 d1 8000 public.carts postgres ~10000 d1 10000 public.inventory postgres ~2000 d1 2000 public.products postgres ~500 d1 500 public.coupons postgres ~300 d1 300 == [125s] load: pgbench on the 5 hot tables, 100 tx/s target for 300 s (7 row changes per tx) t+156s mirror_up=1 slot_retained_wal_bytes=2250560 t+188s mirror_up=1 slot_retained_wal_bytes=365440 t+219s mirror_up=1 slot_retained_wal_bytes=526064 t+251s mirror_up=1 slot_retained_wal_bytes=725496 t+282s mirror_up=1 slot_retained_wal_bytes=825200 t+314s mirror_up=1 slot_retained_wal_bytes=15207320 t+345s mirror_up=1 slot_retained_wal_bytes=4958616 t+377s mirror_up=1 slot_retained_wal_bytes=3295344 t+408s mirror_up=1 slot_retained_wal_bytes=5165664 t+440s mirror_up=1 slot_retained_wal_bytes=3649696 pgbench (16.15 (Ubuntu 16.15-1.pgdg24.04+2)) progress: 30.0 s, 100.6 tps, lat 24.283 ms stddev 68.960, 0 failed, lag 16.375 ms progress: 60.0 s, 100.0 tps, lat 12.218 ms stddev 27.591, 0 failed, lag 5.459 ms progress: 90.0 s, 102.6 tps, lat 13.319 ms stddev 37.540, 0 failed, lag 7.232 ms progress: 120.0 s, 100.9 tps, lat 17.423 ms stddev 42.782, 0 failed, lag 9.665 ms progress: 150.0 s, 100.3 tps, lat 13.728 ms stddev 36.901, 0 failed, lag 7.010 ms progress: 180.0 s, 100.1 tps, lat 7.559 ms stddev 28.505, 0 failed, lag 3.909 ms progress: 210.0 s, 98.9 tps, lat 16.157 ms stddev 43.458, 0 failed, lag 9.692 ms progress: 240.0 s, 99.6 tps, lat 17.052 ms stddev 49.064, 0 failed, lag 10.353 ms progress: 270.0 s, 99.5 tps, lat 22.214 ms stddev 46.319, 0 failed, lag 12.397 ms progress: 300.0 s, 101.3 tps, lat 6.916 ms stddev 20.092, 0 failed, lag 2.764 ms transaction type: /tmp/hot-load.sql scaling factor: 1 query mode: simple number of clients: 4 number of threads: 2 maximum number of tries: 1 duration: 300 s number of transactions actually processed: 30112 number of failed transactions: 0 (0.000%) latency average = 15.073 ms latency stddev = 42.473 ms rate limit schedule lag: avg 8.475 (max 523.957) ms initial connection time = 4.664 ms tps = 100.372060 (without initial connection time) == [440s] check: row counts, per-day counts, PK-range checksums on all 15 tables, retried while CDC catches up ok public.events: postgres 104171 rows; replicas map[d1:104171] ok public.order_items: postgres 130112 rows; replicas map[d1:130112] ok public.audit_log: postgres 50000 rows; replicas map[d1:50000] ok public.payments: postgres 80112 rows; replicas map[d1:80112] ok public.orders: postgres 80112 rows; replicas map[d1:80112] ok public.notifications: postgres 40000 rows; replicas map[d1:40000] ok public.invoices: postgres 30000 rows; replicas map[d1:30000] ok public.shipments: postgres 25000 rows; replicas map[d1:25000] ok public.sessions: postgres 20000 rows; replicas map[d1:20000] ok public.users: postgres 20000 rows; replicas map[d1:20000] ok public.reviews: postgres 8000 rows; replicas map[d1:8000] ok public.carts: postgres 10000 rows; replicas map[d1:10000] ok public.inventory: postgres 2000 rows; replicas map[d1:2000] ok public.products: postgres 500 rows; replicas map[d1:500] ok public.coupons: postgres 300 rows; replicas map[d1:300] checksum public.events: 64 buckets x 6 columns (count, id, user_id, kind, props, at), 0 differ checksum public.order_items: 64 buckets x 7 columns (count, id, order_id, sku, qty, price_cents, created_at), 0 differ checksum public.audit_log: 64 buckets x 6 columns (count, id, actor, action, detail, at), 0 differ checksum public.payments: 64 buckets x 6 columns (count, id, order_id, amount_cents, provider, created_at), 0 differ checksum public.orders: 64 buckets x 7 columns (count, id, user_id, status, total_cents, created_at, updated_at), 0 differ checksum public.notifications: 64 buckets x 6 columns (count, id, user_id, channel, sent, created_at), 0 differ checksum public.invoices: 64 buckets x 5 columns (count, id, order_id, amount_cents, issued_on), 0 differ checksum public.shipments: 64 buckets x 6 columns (count, id, order_id, carrier, shipped_at, delivered_at), 0 differ checksum public.sessions: 64 buckets x 6 columns (count, id, user_id, hits, ua, last_seen), 0 differ checksum public.users: 64 buckets x 5 columns (count, id, email, country, created_at), 0 differ checksum public.reviews: 64 buckets x 7 columns (count, id, product_id, user_id, stars, body, created_at), 0 differ checksum public.carts: 64 buckets x 5 columns (count, id, user_id, items, created_at), 0 differ checksum public.inventory: 64 buckets x 6 columns (count, id, product_id, warehouse, on_hand, updated_at), 0 differ checksum public.products: 64 buckets x 7 columns (count, id, sku, name, price_cents, attrs, created_at), 0 differ checksum public.coupons: 64 buckets x 5 columns (count, id, code, pct, valid_until), 0 differ == [442s] the catch: break the mirror, see what PeerDB says and what chlift says 1 d1 OK 0 0 INSERT 0 50 mirror hot_tables: RUNNING, 206663 rows synced by CDC, replication slot holds 0 MB of WAL public.events postgres ~103113 d1 134173 public.order_items postgres ~125790 d1 130112 public.audit_log postgres ~50000 d1 50000 public.payments postgres ~79873 d1 0 public.orders postgres ~80112 d1 80112 public.notifications postgres ~40000 d1 40000 public.invoices postgres ~30000 d1 30000 public.shipments postgres ~25000 d1 25000 public.sessions postgres ~20000 d1 24770 public.users postgres ~20000 d1 20000 public.reviews postgres ~8000 d1 8000 public.carts postgres ~10000 d1 10000 public.inventory postgres ~2000 d1 2000 public.products postgres ~500 d1 500 public.coupons postgres ~300 d1 300 ok public.events: postgres 104171 rows; replicas map[d1:104171] ok public.order_items: postgres 130112 rows; replicas map[d1:130112] ok public.audit_log: postgres 50000 rows; replicas map[d1:50000] FAIL public.payments: postgres 80162 rows; replicas map[] d1: d1: exit status 60: Received exception from server (version 26.3.39): | Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Unknown table expression identifier 'demo.payments' in scope SELECT toString(toDate32(created_at, 'UTC')) AS d, count() FROM demo.payments FINAL WHERE _peerdb_is_deleted = 0 GROUP BY d. (UNKNOWN_TABLE) | (query: SELECT toString(toDate32(`created_at`, 'UTC')) AS d, count() FROM demo.`payments` FINAL WHERE _peerdb_is_deleted = 0 GROUP BY d FORMAT TabSeparated) ok public.orders: postgres 80112 rows; replicas map[d1:80112] ok public.notifications: postgres 40000 rows; replicas map[d1:40000] ok public.invoices: postgres 30000 rows; replicas map[d1:30000] ok public.shipments: postgres 25000 rows; replicas map[d1:25000] ok public.sessions: postgres 20000 rows; replicas map[d1:20000] ok public.users: postgres 20000 rows; replicas map[d1:20000] ok public.reviews: postgres 8000 rows; replicas map[d1:8000] ok public.carts: postgres 10000 rows; replicas map[d1:10000] ok public.inventory: postgres 2000 rows; replicas map[d1:2000] ok public.products: postgres 500 rows; replicas map[d1:500] ok public.coupons: postgres 300 rows; replicas map[d1:300] chlift: 1 difference(s) found == [457s] teardown peerdb: mirror hot_tables dropped peerdb: peer hot_tables_src dropped peerdb: peer hot_tables_ch dropped clickhouse: database demo dropped on 1 replica(s) postgres: publication hot_tables_pub dropped; mirror slots left: 0 teardown proof: replication slots 0, publications 0, containers left 0, clickhouse inactive # chlift demo: 15 Postgres tables, 5 hot, to ClickHouse Run on a throwaway GitHub Actions runner, 2026-10-12 20:37 UTC, chlift built from commit a403720d7b047230b55445d08ad48201589f4594. - **Seeded:** 15 tables, 505800 rows. Hot: orders, order_items, payments, sessions, events. - **Snapshot:** all 15 tables copied and the mirror RUNNING after 23 s. - **Load:** pgbench, 30112 transactions in 300 s (100.372060 tx/s, average latency 15.073 ms), each one 3 inserts, 2 updates, 1 more insert and 1 delete across the hot tables. - **Slot WAL held at most:** 14.5 MiB during the load. - **Equal:** `migrate check --checksums` passed on all 15 tables 2 s after the load stopped. ``` ok public.events: postgres 104171 rows; replicas map[d1:104171] ok public.order_items: postgres 130112 rows; replicas map[d1:130112] ok public.audit_log: postgres 50000 rows; replicas map[d1:50000] ok public.payments: postgres 80112 rows; replicas map[d1:80112] ok public.orders: postgres 80112 rows; replicas map[d1:80112] ok public.notifications: postgres 40000 rows; replicas map[d1:40000] ok public.invoices: postgres 30000 rows; replicas map[d1:30000] ok public.shipments: postgres 25000 rows; replicas map[d1:25000] ok public.sessions: postgres 20000 rows; replicas map[d1:20000] ok public.users: postgres 20000 rows; replicas map[d1:20000] ok public.reviews: postgres 8000 rows; replicas map[d1:8000] ok public.carts: postgres 10000 rows; replicas map[d1:10000] ok public.inventory: postgres 2000 rows; replicas map[d1:2000] ok public.products: postgres 500 rows; replicas map[d1:500] ok public.coupons: postgres 300 rows; replicas map[d1:300] checksum public.events: 64 buckets x 6 columns (count, id, user_id, kind, props, at), 0 differ checksum public.order_items: 64 buckets x 7 columns (count, id, order_id, sku, qty, price_cents, created_at), 0 differ checksum public.audit_log: 64 buckets x 6 columns (count, id, actor, action, detail, at), 0 differ checksum public.payments: 64 buckets x 6 columns (count, id, order_id, amount_cents, provider, created_at), 0 differ checksum public.orders: 64 buckets x 7 columns (count, id, user_id, status, total_cents, created_at, updated_at), 0 differ checksum public.notifications: 64 buckets x 6 columns (count, id, user_id, channel, sent, created_at), 0 differ checksum public.invoices: 64 buckets x 5 columns (count, id, order_id, amount_cents, issued_on), 0 differ checksum public.shipments: 64 buckets x 6 columns (count, id, order_id, carrier, shipped_at, delivered_at), 0 differ checksum public.sessions: 64 buckets x 6 columns (count, id, user_id, hits, ua, last_seen), 0 differ checksum public.users: 64 buckets x 5 columns (count, id, email, country, created_at), 0 differ checksum public.reviews: 64 buckets x 7 columns (count, id, product_id, user_id, stars, body, created_at), 0 differ checksum public.carts: 64 buckets x 5 columns (count, id, user_id, items, created_at), 0 differ checksum public.inventory: 64 buckets x 6 columns (count, id, product_id, warehouse, on_hand, updated_at), 0 differ checksum public.products: 64 buckets x 7 columns (count, id, sku, name, price_cents, attrs, created_at), 0 differ checksum public.coupons: 64 buckets x 5 columns (count, id, code, pct, valid_until), 0 differ ``` ## The catch The ClickHouse table `demo.payments` was dropped and 50 payments were written in Postgres. - PeerDB mirror state afterwards: **RUNNING** (`chlift_mirror_up` = 1). It sends no alert for this. - chlift exporter: `chlift_mirror_errors` 0 -> 1, 11 s after the write (polled every 10 s). - `migrate check` exit code: 1 (non-zero = not equal). - **Teardown:** replication slots 0, publications 0, containers left 0, clickhouse inactive. - **Total run:** 489 s. DEMO: PASS