Notebook · code/module-06/01-change-data-capture.ipynb

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)