Download 01-change-data-capture.ipynb
Open it in JupyterLab or VS Code from the lab folder to run it. This page is a read-only view.
Change data capture from PostgreSQL — practical
Brightlane's tariffs live in PostgreSQL. You register a Debezium connector with Kafka Connect, read the snapshot and the change events it writes, flatten them into a compacted "latest tariff" topic, and then watch a replication slot hold on to the database's log while nothing seems wrong.
Lab services: kafka-1..3, schema-registry, postgres, connect (docker compose --profile connect up -d --wait)
· Lesson: modules/06-connect-and-cdc/01-change-data-capture.md
# Setup: find the lab's shared helpers and check the lab is running import sys, pathlib lab_root = next(p for p in [pathlib.Path.cwd(), *pathlib.Path.cwd().parents] if (p / "common").is_dir()) sys.path.insert(0, str(lab_root)) import json, time from common import settings, lab lab.require_kafka() for name, url in [("Schema Registry", settings.SCHEMA_REGISTRY_URL + "/subjects"), ("Kafka Connect", settings.CONNECT_URL)]: lab.require_http(name, url, start_hint="docker compose --profile connect up -d --wait") lab.print_versions()
Step 1: The Connect worker
Kafka Connect has a REST API. Ask the worker for its version and the connector plug-ins it has.
print(lab.connect_api("GET", "/")) for plugin in lab.connect_api("GET", "/connector-plugins"): if "debezium" in plugin["class"]: print(f"{plugin['type']:<7} {plugin['class']} {plugin['version']}")
Step 2: The pricing database
36 tariffs, one per site. The notebook resets the database to its starting state first.
lab.remove_connectors("m06l01-") lab.reset_pricing_db() for row in lab.pg("SELECT tariff_id, site_id, price_per_kwh, idle_fee_per_min FROM tariffs ORDER BY tariff_id LIMIT 3"): print(row) print(lab.pg("SELECT count(*) FROM tariffs")[0][0], "tariffs")
Step 3: Register a CDC connector
A connector is created by sending its configuration to the REST API. The topics it writes are
named <topic.prefix>.<schema>.<table>. The brokers don't create topics automatically, so the
connector creates them itself (topic.creation.default.*).
PREFIX = "m06l01_pricing" cdc = { "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "plugin.name": "pgoutput", # PostgreSQL's built-in logical decoding "database.hostname": "postgres", "database.port": "5432", "database.dbname": "pricing", "database.user": "brightlane", "database.password": "brightlane-lab", "topic.prefix": PREFIX, "table.include.list": "public.tariffs", "slot.name": "m06l01_pricing_cdc", "publication.name": "m06l01_pricing_cdc", "publication.autocreate.mode": "filtered", "time.precision.mode": "connect", # dates as Avro dates "topic.creation.default.replication.factor": "3", "topic.creation.default.partitions": "1", } lab.connect_api("PUT", "/connectors/m06l01-pricing-cdc/config", cdc) status = lab.wait_connector("m06l01-pricing-cdc") print(status["connector"]["state"], [t["state"] for t in status["tasks"]])
Step 4: Read the snapshot
The first thing the connector does is a snapshot: it reads every existing row and writes one event per row. Keys and values are Avro, so the module 5 deserializer reads them.
from confluent_kafka import Consumer, TopicPartition from confluent_kafka.schema_registry import SchemaRegistryClient from confluent_kafka.schema_registry.avro import AvroDeserializer from confluent_kafka.serialization import SerializationContext, MessageField TOPIC = f"{PREFIX}.public.tariffs" registry = SchemaRegistryClient({"url": settings.SCHEMA_REGISTRY_URL}) deserialize = AvroDeserializer(registry) def read(topic, start=0): """Every record of a 1-partition topic from `start`, as (offset, key, value) with Avro decoded.""" consumer = Consumer({"bootstrap.servers": settings.KAFKA_BOOTSTRAP, "group.id": "m06l01-reader", "enable.auto.commit": False}) end = consumer.get_watermark_offsets(TopicPartition(topic, 0), timeout=10)[1] consumer.assign([TopicPartition(topic, 0, start)]) records = [] while start + len(records) < end: msg = consumer.poll(10) if msg is None: break decode = lambda data, field: deserialize(data, SerializationContext(topic, field)) if data else None records.append((msg.offset(), decode(msg.key(), MessageField.KEY), decode(msg.value(), MessageField.VALUE))) consumer.close() return records def show(event): """The parts of a change event worth looking at.""" source = {k: event["source"][k] for k in ("snapshot", "table", "lsn", "txId")} return {"op": event["op"], "before": event["before"], "after": event["after"], "source": source} lab.wait_until(lambda: len(read(TOPIC)) >= 36, timeout=60, what="the snapshot") snapshot = read(TOPIC) print(len(snapshot), "events; the first one:") offset, key, value = snapshot[0] print("key:", key) print(json.dumps(show(value), indent=1, default=str))
Step 5: Change a price, delete a tariff
Now the pricing team raises the price at site-N03 and closes site-V10's tariff.
lab.pg("UPDATE tariffs SET price_per_kwh = 0.5900 WHERE tariff_id = 'tar-site-N03-1'") lab.pg("DELETE FROM tariffs WHERE tariff_id = 'tar-site-V10-1'") lab.wait_until(lambda: len(read(TOPIC)) >= 39, timeout=30, what="the change events") for offset, key, value in read(TOPIC, start=36): print(f"offset {offset}, key {key}:") print(" ", json.dumps(show(value), default=str) if value else "None (a tombstone)")
Change it: the latest tariff per id, in a compacted topic
Billing (module 7) only wants each tariff's current row, not the change history. A second
connector applies two single message transforms: ExtractNewRecordState keeps only the
after row (and turns deletes into tombstones), and RegexRouter renames the topic.
from confluent_kafka.admin import AdminClient STATE_TOPIC = f"{PREFIX}.tariffs" admin = AdminClient({"bootstrap.servers": settings.KAFKA_BOOTSTRAP}) lab.recreate_topic(admin, STATE_TOPIC, partitions=1, config={"cleanup.policy": "compact"}) state = {**cdc, "topic.prefix": "m06l01_state", "slot.name": "m06l01_pricing_state", "publication.name": "m06l01_pricing_state", "transforms": "unwrap,route", "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState", "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter", "transforms.route.regex": r"m06l01_state\.public\.(.*)", "transforms.route.replacement": f"{PREFIX}.$1"} lab.connect_api("PUT", "/connectors/m06l01-pricing-state/config", state) lab.wait_connector("m06l01-pricing-state") lab.wait_until(lambda: len(read(STATE_TOPIC)) >= 35, timeout=60, what="the state snapshot") lab.pg("UPDATE tariffs SET price_per_kwh = 0.6100 WHERE tariff_id = 'tar-site-N03-1'") lab.pg("DELETE FROM tariffs WHERE tariff_id = 'tar-site-V09-1'") lab.wait_until(lambda: len(read(STATE_TOPIC)) >= 37, timeout=30, what="the flattened changes") for offset, key, value in [read(STATE_TOPIC)[0]] + read(STATE_TOPIC, start=35): print(offset, key, value)
Break it: a busy table nobody captures
The pricing service also keeps an audit table. It's busy, and no connector captures it. Predict first: the connectors are running and healthy. How much log does PostgreSQL keep for them?
A replication slot is PostgreSQL's bookmark for a connector: the database keeps every part of its write-ahead log (WAL) the slot's reader hasn't confirmed yet.
def slots(): return lab.pg("SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) " "FROM pg_replication_slots WHERE slot_name LIKE 'm06l01%' ORDER BY slot_name") lab.pg("CREATE TABLE price_audit (id bigserial PRIMARY KEY, entry text)") lab.pg("INSERT INTO price_audit (entry) SELECT repeat('price check ', 40) FROM generate_series(1, 100000)") time.sleep(15) print("connectors:", {n: lab.connect_api("GET", f"/connectors/{n}/status", echo=False)["connector"]["state"] for n in ("m06l01-pricing-cdc", "m06l01-pricing-state")}) for slot in slots(): print(slot)
Both slots hold on to the WAL the audit inserts wrote, although both connectors run. They only confirm a position when an event from a captured table reaches them, and none does. Debezium's fix is a heartbeat: every few seconds the connector writes to a small table that it does capture, and confirms the position that event carries. Add it to the first connector only:
lab.pg("CREATE TABLE debezium_heartbeat (id integer PRIMARY KEY, ts timestamptz NOT NULL)") cdc.update({ "table.include.list": "public.tariffs,public.debezium_heartbeat", "heartbeat.interval.ms": "5000", "heartbeat.action.query": "INSERT INTO debezium_heartbeat VALUES (1, now()) " "ON CONFLICT (id) DO UPDATE SET ts = excluded.ts"}) lab.connect_api("PUT", "/connectors/m06l01-pricing-cdc/config", cdc) lab.wait_connector("m06l01-pricing-cdc") time.sleep(30) for slot in slots(): print(slot)
Clean up
Delete the connectors, their slots and publications, the topics and the schemas, and reset the
database. If anything gets stuck, docker compose down -v in the code/ folder resets the lab.
lab.remove_connectors("m06l01-") topics = [t for t in admin.list_topics(timeout=10).topics if t.startswith(("m06l01", "__debezium-heartbeat.m06l01"))] lab.delete_topics(admin, topics) for subject in [s for s in registry.get_subjects() if s.startswith("m06l01")]: registry.delete_subject(subject) registry.delete_subject(subject, permanent=True) lab.pg("DROP TABLE IF EXISTS price_audit, debezium_heartbeat") lab.reset_pricing_db() admin.delete_consumer_groups(["m06l01-reader"]) print("cleaned up:", topics)