CDC — real-time MySQL → Postgres replication

Streams inserts, updates and deletes from a source MySQL orders table into a target Postgres order_summary table in near-real-time, using log-based CDC.

SOURCE MySQL ──binlog(ROW)──► Debezium MySQL source ──► Kafka ──► Debezium JDBC sink ──upsert/DELETE──► TARGET Postgres
   (orders)                     (reads binlog)          (src.ordersdb.orders)                            (order_summary)

Config-only pipeline (no application code): two Kafka Connect connectors on the Debezium image.

Why CDC here (and not native replication)

• Source is MySQL, target is Postgres — heterogeneous, so native DB replication is impossible.
• Even for Postgres→Postgres, native logical replication can filter rows/columns (PG15+) but cannot transform; CDC over Kafka can (SMTs / ksqlDB) and decouples capture from load.
• Log-based CDC captures deletes and is correct by construction (no updated_at polling, no boundary/commit-skew hazards).

Components (docker/docker-compose.yml)

Service	Purpose	Port
source-mysql	System of record (orders), binlog_format=ROW, GTID	3306
target-postgres	Replicated store (order_summary)	5434
kafka	KRaft single-node broker (the backbone)	9092
connect	Kafka Connect w/ Debezium MySQL source and JDBC sink	8083

Scripts

scripts/e2e-test.sh          # functional: snapshot + insert + update + DELETE propagation
scripts/loadtest.sh [N] [B]  # perf: generate N rows (default 1,000,000, batch B=10,000) + stats
scripts/reconcile.sh         # correctness: counts + exact key-set diff + DLQ check
scripts/e2e-test.sh --down   # tear everything down

Typical run: e2e-test.sh → loadtest.sh → reconcile.sh.

Measured results (local: Colima, 8 vCPU / 16 GB, single partition, tasks.max=1)

1,000,000 rows, MySQL → Postgres:

Metric	Value
Source insert rate	~205,000 rows/s
End-to-end catch-up (first insert → target fully caught up)	~39 s
Effective replication rate	~25,600 rows/s
Tail lag after last insert	~34 s
Missing rows / DLQ	0 / 0 (reconciled identical)
Projection to 1B @ this rate	~10.8 h (single partition/task)

Scaling to 1 billion

The numbers above are a single-partition, single-task baseline. 1B rows on a laptop is not realistic (~120 GB per side + hours). To run 1B on adequate hardware and cut the time roughly linearly:

• Increase source topic partitions and sink tasks.max (parallel apply; keys route by order_id so per-row order is preserved).
• Raise sink batch.size; keep reWriteBatchedInserts=true on the JDBC URL.
• Give Kafka/MySQL/Postgres real disk + memory; provision Kafka retention for the backlog.
• Generate with a bigger BATCH and multiple concurrent writers.

loadtest.sh prints the linear projection so you can size a target environment.

Reconciliation — finding & preventing missing rows

Prevent (correctness by construction):

• Idempotent upsert on order_id → no duplicates; replays after restart are no-ops.
• At-least-once delivery (Kafka offsets + binlog position) → crashes re-read, never drop.
• DLQ on the sink (errors.tolerance=all + dlq.order_summary) → a poison row is captured, never silently lost. Alerting on DLQ depth is the primary missing-row guard.
• Keep MySQL binlog retention > max downtime; monitor connector state/lag.

Detect (scripts/reconcile.sh):

• Row-count check (source vs target).
• Exact key-set diff — lists any missing/extra order_ids.
• DLQ check — any rejected rows.

For huge tables, reconcile against a watermark where CDC has caught up, and scale the diff with chunked counts → per-chunk checksums (drill into mismatched chunks only). Cross-engine value hashing (MySQL vs Postgres) requires identical type/format normalization; a key-set + version diff avoids that pitfall. Tools like data-diff do cross-engine row-level reconciliation.

Connector configs

• connectors/source-mysql.json — Debezium MySQL source (binlog, schema-history topic, tombstones.on.delete=true).
• connectors/sink-postgres.json — Debezium JDBC sink (insert.mode=upsert, delete.enabled=true, primary.key.mode=record_key, DLQ).

Production notes

• Deletes need binlog_row_image=FULL on MySQL (set in compose) so tombstones carry the key.
• Monitor MySQL binlog disk and Kafka consumer lag; a stalled sink backs pressure up the chain.
• Source failover: point Debezium at a stable endpoint; GTID makes re-attach after failover safe.
• Scale throughput via partitions + tasks.max; keys route by order_id (per-row order preserved).