2. The log, per database

Chapter 1’s idea was “read the log”. Every database has one, and no two are alike — in what they contain, what they require, and how they can hurt you.

This chapter is the source-side detail. It is the most database-specific chapter in the guide and the one that determines most of your operational risk.

PostgreSQL

The most commonly used and the one with the sharpest edge.

The mechanism

PostgreSQL’s WAL is a physical log — page images and byte offsets, meaningless outside the server that wrote it. Logical decoding turns it into row-level changes.

   WAL (physical)
     "page 4291 of relation 16384, bytes
      112–186 became this"
        │
        │  LOGICAL DECODING plugin
        ▼
   logical change stream
     "table public.orders, UPDATE,
      id=4471, status 'pending'→'shipped'"

plugin.name=pgoutput

pgoutput is built into PostgreSQL since version 10 and is the right default — nothing to install, maintained with the server. The older decoderbufs and wal2json plugins still exist and are rarely the right choice now.

What you must configure

# postgresql.conf
wal_level = logical            # ← requires a RESTART
max_replication_slots = 10
max_wal_senders = 10

CREATE ROLE debezium WITH REPLICATION LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO debezium;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium;

wal_level = logical needs a restart, which on a production database is a scheduled maintenance window. Plan for it; it is often the longest lead time in a CDC project.

It also increases WAL volume, because logical decoding needs more information recorded than physical replication does. Budget disk and network for it.

The replication slot

The mechanism, and the danger.

   A SLOT is a bookmark the DATABASE keeps
   on behalf of a consumer.

   "this consumer has confirmed up to LSN
    0/1A2B3C4D"

   PostgreSQL WILL NOT DELETE WAL SEGMENTS
   NEWER THAN THE OLDEST SLOT'S POSITION.

That last sentence is the whole risk. The slot is a promise the database keeps unconditionally.

   Debezium stops — crash, deploy, a bad
   config, a paused connector
     │
     ├─ WAL keeps being written
     ├─ the slot does not advance
     ├─ PostgreSQL cannot recycle segments
     ├─ pg_wal/ grows
     └─ THE DISK FILLS
        → the DATABASE stops accepting writes
        → your application is down

A stopped pipeline takes down the source database. Not the pipeline — the database. This is the single most important operational fact in this guide, and it is why chapter 13 spends so long on it.

-- the query to alert on
SELECT slot_name, active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS retained_wal
FROM pg_replication_slots;

   ALERT ON:
     active = false        ← nothing is
                             consuming it
     retained_wal growing  ← the fuse is lit

Modern PostgreSQL offers a safety valve:

max_slot_wal_keep_size = 64GB

Past that, the database invalidates the slot rather than filling its disk. Set this. It converts a database outage into a pipeline outage that needs a re-snapshot, which is a vastly better failure.

Heartbeats

A subtle version of the same problem.

   You capture only public.orders.
   orders gets one write a day.
   The rest of the database is very busy.

   → Debezium sees nothing relevant to emit
   → it does not advance the slot
   → WAL accumulates from OTHER tables'
     activity
   → the disk fills, from a table you are
     not even watching

heartbeat.interval.ms=10000
heartbeat.action.query=INSERT INTO debezium_heartbeat (ts) VALUES (now())

The heartbeat makes Debezium acknowledge its position even when it has nothing to emit. The heartbeat.action.query writes to a table you are capturing, which forces WAL activity Debezium will see and confirm.

Set both on any connector with a filtered table list. This failure is common, confusing, and entirely preventable.

REPLICA IDENTITY

The one that silently produces useless events.

ALTER TABLE orders REPLICA IDENTITY FULL;

   DEFAULT   the primary key only appears in
             the `before` image of updates
             and deletes.
             → `before` is nearly empty.
             → you cannot see what changed,
               only what it became.

   FULL      the ENTIRE old row is logged.
             → `before` is complete.
             → costs more WAL.

   INDEX ix  a chosen unique index.

   NOTHING   nothing. deletes carry no
             identifying information at all.

With the default, a delete event tells you the primary key and nothing else. If a consumer needs the deleted row’s contents — to remove it from a search index by some other field, say — it cannot.

FULL is what most CDC deployments want, and it increases WAL volume. Decide deliberately, per table, rather than discovering it downstream.

Publications

publication.autocreate.mode=filtered

   all_tables   one publication for everything.
                simple; captures tables you
                did not intend to.
   filtered     a publication matching your
                include list. ← usually right.
   disabled     you manage it yourself.
                ← right when the Debezium
                  role should not have DDL
                  rights

MySQL

Simpler, and with a different set of traps.

The mechanism

MySQL’s binary log is what replicas read. Debezium is, effectively, a replica that publishes to Kafka instead of applying.

# my.cnf
log_bin           = mysql-bin
binlog_format     = ROW          # ← MANDATORY
binlog_row_image  = FULL         # ← you want this
gtid_mode         = ON           # ← strongly recommended
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 604800   # 7 days

GRANT SELECT, RELOAD, SHOW DATABASES,
      REPLICATION SLAVE, REPLICATION CLIENT
  ON *.* TO 'debezium'@'%';

binlog_format = ROW is not optional. STATEMENT logs the SQL text, which tells you an UPDATE ran but not which rows it touched. MIXED switches between them unpredictably, which is worse.

binlog_row_image = FULL is MySQL’s equivalent of REPLICA IDENTITY FULL — with MINIMAL, the before-image contains only changed columns plus the key.

Each connector needs a unique database.server.id, distinct from every real replica. Two connectors sharing one will fight and disconnect each other repeatedly.

The trap

MySQL has no slot. The binlog is retained by time or size and then deleted, whether or not anyone has read it.

   POSTGRESQL   the slot protects the log
                → your disk fills
   MySQL        the log expires regardless
                → your CONNECTOR breaks

   Debezium stops for longer than
   binlog_expire_logs_seconds
     → its recorded position no longer exists
     → the connector CANNOT RESUME
     → you must re-snapshot

This is the better failure of the two — the database survives — but it means binlog retention is a hard bound on how long the pipeline may be down. Seven days is a common, sane setting, and it should be longer than your worst realistic outage plus the time to notice it.

GTIDs make failover survivable. Without them, a primary failover leaves Debezium holding a file-and-offset position that is meaningless on the new primary.

MongoDB

   OLD:  read the oplog directly
   NOW:  CHANGE STREAMS

Debezium uses change streams, which are MongoDB’s own supported abstraction over the oplog — available on replica sets and sharded clusters, and stable across failover.

   ✓ resume tokens survive primary elections
   ✓ works with sharding
   ✓ no oplog parsing
   ⚠ the resume token must still be within
     the OPLOG WINDOW — same bound as MySQL's
     binlog retention

The sharp edge is update events. By default a change stream reports what changed, not the resulting document.

capture.mode=change_streams_update_full

Without this, an update event carries a patch description rather than a full document, and most consumers want the document. There is a cost — MongoDB must look up the full document — and it is almost always worth paying.

Pre-images (the document before the change) require enabling changeStreamPreAndPostImages on the collection, and are what you need for a meaningful before field.

SQL Server

The odd one out: Debezium does not read the transaction log directly.

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo', @source_name = 'orders',
  @role_name = NULL;

SQL Server’s own CDC feature reads the transaction log and writes changes into capture tables. Debezium polls those.

   ✓ supported, documented, stable
   ✓ no special replication protocol

   ✗ requires SQL Server AGENT to be running
     ← the capture job is an Agent job. no
       Agent, no CDC, and nothing obvious
       tells you.
   ✗ changes are stored in your database
     → disk, and a cleanup job with a
       retention setting
   ✗ higher latency — polling, not streaming
   ✗ Enterprise-edition history (now more
     widely available; verify your version)

The Agent dependency is the classic SQL Server CDC incident. Agent stops, capture tables stop filling, Debezium reports healthy because it is polling successfully and finding nothing new.

Oracle

The most operationally involved.

   LOGMINER   Oracle's built-in log reader.
              no extra licence.
              ✓ the default choice
              ~ CPU cost on the database
              ~ mining lag under heavy load

   XSTREAM    much lower overhead.
              ✗ REQUIRES A GOLDENGATE LICENCE

Both need supplemental logging enabled, at minimum for the primary keys of captured tables and often at the ALL COLUMNS level to get a useful before image:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Oracle CDC is a project, not a configuration change. Budget DBA time, expect to tune LogMiner’s session behaviour, and test under production-like write volume — the mining cost is proportional to redo volume, not to the tables you capture.

Comparison

	PostgreSQL	MySQL	MongoDB	SQL Server	Oracle
Mechanism	logical decoding	binlog	change streams	capture tables	LogMiner/XStream
Streaming?	yes	yes	yes	polled	yes
Restart needed	yes (`wal_level`)	maybe	no	no	maybe
Position held by	a slot	file+offset / GTID	resume token	LSN	SCN
If Debezium stops	DB disk fills	connector breaks	connector breaks	capture tables grow	archive logs grow
Full before-image	`REPLICA IDENTITY FULL`	`binlog_row_image=FULL`	pre-images	included	supplemental logging
Extra load	more WAL	little	doc lookups	capture jobs	mining CPU
Difficulty	medium	easy	easy	medium	hard

MySQL is the easiest to set up; PostgreSQL is the most dangerous to run; Oracle is the most work. MongoDB is straightforward provided you remember the update mode.

The rule that applies to all of them

   THE SOURCE DATABASE IS NOW COUPLED TO
   YOUR PIPELINE'S HEALTH.

   Postgres  a stopped consumer fills the
             disk
   MySQL     a long outage loses your
             position
   Mongo     the same, via the oplog window
   SQL Srv   capture tables grow; Agent must
             run
   Oracle    archive logs and mining load

Whoever owns the database must know CDC exists, must be alerted on it, and must have a runbook. A pipeline nobody told the DBAs about is the setup for chapter 14’s worst incident.

What to take from this chapter

Every database exposes a different log with different requirements; the source-side detail is where most of your operational risk lives.
PostgreSQL: logical decoding via pgoutput, wal_level=logical needs a restart, and the replication slot will fill your disk if consumption stops. Set max_slot_wal_keep_size to convert a database outage into a pipeline one.
Set heartbeats on any filtered PostgreSQL connector, or a busy database with a quiet captured table accumulates WAL invisibly.
REPLICA IDENTITY FULL is what makes before images useful, and it costs WAL. Decide per table.
MySQL: binlog_format=ROW is mandatory, binlog_row_image=FULL is what you want, GTIDs make failover survivable, and each connector needs a unique server.id. Binlog retention bounds how long you may be down.
MongoDB uses change streams, and you almost certainly want change_streams_update_full — the default reports a patch, not the document.
SQL Server polls capture tables and depends on SQL Server Agent, whose failure looks like “no changes” rather than an error.
Oracle is a project. LogMiner is free and costs database CPU; XStream needs a GoldenGate licence. Both need supplemental logging.
MySQL is easiest, PostgreSQL is most dangerous, Oracle is most work.
The DBA team must know the pipeline exists, be alerted on it, and have a runbook.

Next: deployment shapes — Kafka Connect, Debezium Server, the embedded engine, and when Kafka is not the answer.

Written by Sepuri Sai Krishna