Brandon Cate — Portfolio

When dump-and-restore stops working

Database migrations are hard. Most teams avoid them when they can, because getting one wrong tends to be very risky. The standard playbook for moving from one Postgres instance to another is pg_dump and pg_restore. Normally you stop writes on the source, take a snapshot, restore it on the destination, cut traffic over. It’s well-trodden, easy to understand and if your constraints allow it, I think it’s what you should reach for first.

Sadly, the constraints don’t always allow it. Either the database is too large to dump inside any tolerable maintenance window, or the application can’t stop writing for long enough to do it cleanly. And sometimes the destination isn’t another Postgres database at all. At that point you need a way to replicate changes continuously from the source to the destination while the source is still live. That’s where Debezium comes in.

Debezium is a change-data-capture (CDC) tool. It connects to a source database, reads the stream of changes that database is already producing for its own internal use, and ships those changes as events to somewhere else, usually a Kafka topic. Downstream consumers replay the events to build whatever they need on the other side.

It’s well-engineered and works as advertised when you stay on the happy path. The places it bites are at the boundaries, and those boundaries don’t make sense at the Debezium layer — they make sense as Postgres internals. To debug the failure modes you have to understand what Debezium is reading, which is the write-ahead log (WAL).

What the WAL actually is

The write-ahead log is how Postgres survives a crash. When you commit a transaction the database has to promise the change is durable — that if the process crashes a microsecond later, the change still survives. The most direct way to keep that promise would be to write the change straight to its final location in the table before acknowledging the transaction commit. The problem is that table-file writes land at random offsets across the disk, and doing them per commit kills throughput. So Postgres takes a shortcut. It writes a small record to a sequential log file first, waits for that record to hit the disk, and only then acknowledges the transaction commit to your client. The table-file itself gets updated later, in the background. If the process dies before that background write happens, the next startup reads through the log and replays whatever was committed but not yet flushed. The “ahead” in write-ahead is the log being written ahead of the data file.

That log lives at $PGDATA/pg_wal/ as a directory of 16 MB segment files with names like 000000010000000000000001. Postgres appends records to the current segment until it fills up, then rolls over to a new one. The whole thing is plain files you can read.

To see what’s actually in them, here’s a four-statement transaction I’ll come back to throughout the piece, running against an orders table with columns id, customer, amount_c (amount in cents), and status:

INSERT INTO orders (customer, amount_c) VALUES ('alice', 4999);
INSERT INTO orders (customer, amount_c) VALUES ('bob',   12050);
UPDATE orders SET status = 'paid' WHERE customer = 'alice';
DELETE FROM orders WHERE customer = 'bob';

To inspect the WAL records that transaction produces, Postgres ships pg_waldump — a CLI tool that reads segment files off disk and prints a human-readable line per record. It’s read-only and works fine against a live cluster. The basic invocation is just the path to a segment file:

pg_waldump /var/lib/postgresql/data/pg_wal/000000010000000000000001

That dumps everything in the file, which is a lot, because every Postgres operation touches the WAL — including the catalog updates from CREATE TABLE. To narrow it down to what our four statements did, filter by the orders table’s relfilenode.

Every Postgres table is stored on disk as a file whose name is an integer rather than the table name. relfilenode is that integer, and it lives in the pg_class system catalog:

SELECT relfilenode FROM pg_class WHERE relname = 'orders';
-- 16397

So orders is on disk as a file literally named 16397. Pipe that earlier pg_waldump command output through grep with that table number, and the records from our transaction look like this:

rmgr: Heap  tx: 742  lsn: 0/0194B598  desc: INSERT+INIT off: 1  blkref #0: rel 1663/16384/16397 blk 0
rmgr: Heap  tx: 742  lsn: 0/0194B698  desc: INSERT      off: 2  blkref #0: rel 1663/16384/16397 blk 0
rmgr: Heap  tx: 742  lsn: 0/0194B730  desc: HOT_UPDATE  old_off: 1, new_off: 3  blkref #0: rel 1663/16384/16397 blk 0
rmgr: Heap  tx: 742  lsn: 0/0194B7C8  desc: DELETE      off: 2  blkref #0: rel 1663/16384/16397 blk 0

Four records, one per statement, all sharing tx: 742 — that’s how we know they belong to the same transaction.

The lsn column on each line is the log sequence number, the address of that record in the log. Every WAL record gets one when it’s written, LSNs increase monotonically across the lifetime of the cluster and never repeat, and any consumer of the WAL tracks its progress by remembering the last LSN it handled. The hex segment/offset form is just how Postgres prints the underlying number. You can mostly treat the whole string as an ordered identifier.

What each record is describing — INSERT+INIT, blk 0, off: 1 — only makes sense once you know how Postgres lays data out on disk. The file for our orders table isn’t a flat stream of rows. It’s divided into fixed-size 8 KB chunks called pages, and inside a page, rows are stored as tuples with a small array of page slots at the top of the page pointing to where each tuple’s bytes live. (Postgres also calls these “line pointers” in the docs — same thing. I’ll call them page slots throughout to keep them distinct from the replication slots that show up later.)

All four statements in our transaction landed in the same page: chunk 0 of 16397, the very first 8 KB of the file. The first INSERT had to create that page from scratch and put alice’s row into page slot 1, which is why the WAL records this one as INSERT+INIT instead of a plain INSERT. The second INSERT placed bob into page slot 2 of the now-existing page. The UPDATE wrote a new version of alice’s row into page slot 3 — Postgres doesn’t overwrite in place, it appends a new tuple and points the old slot at it, which is why the record’s columns are old_off: 1, new_off: 3. The DELETE marked page slot 2 as removed. Visually, page 0 ends up something like this:

Page 0 of file 16397  (8 KB)

  byte 0    +---------------------------------------------------------+
            |  Page header                                            |
            +---------------------------------------------------------+
            |  Page slot 1  ->  byte 8128  (HOT-redirected to slot 3) |
            |  Page slot 2  ->  byte 8048  (dead, after DELETE)       |
            |  Page slot 3  ->  byte 7968                             |
            +---------------------------------------------------------+
            |                                                         |
            |                    ...free space...                     |
            |                                                         |
            +---------------------------------------------------------+
  byte 7968 |  tuple:  alice,  4999, paid           <- UPDATE         |
  byte 8048 |  tuple:  bob,   12050, pending        <- INSERT #2      |
  byte 8128 |  tuple:  alice,  4999, pending        <- INSERT #1      |
  byte 8192 +---------------------------------------------------------+

Same WAL, different reader

The physical WAL we just looked at speaks in pages and page slots, which is the right shape for crash recovery and the wrong shape for tracking what actually happened to your rows. Knowing that alice’s status went from pending to paid needs row-level events with column values, not raw page edits. Postgres has a second mode for reading the WAL that produces exactly that information: logical decoding.

Two concepts do the work, and it pays to have a clean mental model of each before looking at the SQL.

A replication slot, not to be confused with a page slot, is a per-consumer bookmark Postgres maintains on the server. It records the last LSN the consumer has confirmed reading. If the consumer goes away — restart, crash, network blip — and comes back later, the slot tells the server exactly where to resume. While the slot exists, Postgres also refuses to delete any WAL files that sit at or beyond the slot’s position. That guarantee is what makes a slot durable in a useful way: the consumer can be offline for an hour and still pick up every change that happened in the gap. It’s also the source of one of the worst failure modes, which I’ll come back to.

An output plugin is a small library Postgres loads inside the server process. Its job is to take raw WAL records as they’re read off disk and turn them into something the consumer can use. Postgres ships with test_decoding, which produces human-readable text — handy for inspecting what logical decoding actually sees, not suitable for production because its output format isn’t stable across versions. Built-in logical replication and Debezium both use pgoutput, which produces a stable binary wire format. Same WAL records going in either way, different translation coming out.

With those two ideas in hand, setup is three steps.

First, the cluster needs wal_level=logical. This is a server-level config flag that tells Postgres to write extra information into each WAL record — enough to reconstruct row values, not just enough to redo a page write. Without it, logical decoding can’t get the column data it needs out of the log.

postgres -c wal_level=logical -c max_wal_senders=4 -c max_replication_slots=4

max_wal_senders and max_replication_slots cap how many consumers can connect at once.

Second, the table needs REPLICA IDENTITY set. This setting controls what gets logged when a row is updated or deleted. By default, Postgres only logs the primary key of the affected row — enough to find the row again on this database, not enough to tell a downstream consumer what the row used to look like. A consumer often needs more than the key: the old column values to compute a diff, or non-key columns to feed into a join in some other system. REPLICA IDENTITY FULL tells Postgres to log the entire row as it was before the change, so the consumer can see both the old and new states of every column:

ALTER TABLE orders REPLICA IDENTITY FULL;

FULL is heavier — every UPDATE and DELETE now logs every column instead of just the primary key — but for migrations I default to it and live with the extra bytes. Mid-migration is the wrong moment to discover that a column the downstream needs isn’t in the stream.

Third, create the replication slot. This call registers the bookmark on the server and attaches it to an output plugin:

SELECT * FROM pg_create_logical_replication_slot('demo_slot', 'test_decoding');

The first argument is the slot name (used by the consumer to identify itself on reconnect), the second is the plugin name. From this point forward Postgres is recording the position of demo_slot, and test_decoding is on standby waiting to translate any WAL records that come through.

Now run the same four-statement transaction from earlier with this slot in place, then peek at what the plugin captured (peeking reads the slot’s contents without advancing the bookmark — useful for inspection):

SELECT lsn, xid, data FROM pg_logical_slot_peek_changes('demo_slot', NULL, NULL);

    lsn    | xid |                              data
-----------+-----+----------------------------------------------------------------
 0/194B530 | 742 | BEGIN 742
 0/194B598 | 742 | table public.orders: INSERT: id[bigint]:1 customer[text]:'alice' amount_c[integer]:4999 status[text]:'pending' ...
 0/194B698 | 742 | table public.orders: INSERT: id[bigint]:2 customer[text]:'bob' amount_c[integer]:12050 status[text]:'pending' ...
 0/194B730 | 742 | table public.orders: UPDATE: old-key: id[bigint]:1 customer[text]:'alice' ... new-tuple: id[bigint]:1 customer[text]:'alice' ... status[text]:'paid' ...
 0/194B7C8 | 742 | table public.orders: DELETE: id[bigint]:2 customer[text]:'bob' ...
 0/194B858 | 742 | COMMIT 742

The thing I want to point at: the LSNs 0/194B598, 0/194B698, 0/194B730, 0/194B7C8 are the same LSNs you saw in the pg_waldump output above. Same records on disk, different reader. pg_waldump rendered them as “page-and-offset change against relfilenode 16397.” test_decoding resolved the same records against the catalog (so 16397 becomes public.orders) and unpacked the row data Postgres logged because wal_level=logical and REPLICA IDENTITY FULL were set. Nothing new is being generated. The plugin is doing translation, and the rules of the translation are determined by how the WAL records were written in the first place.

peek shows you the slot’s contents without moving the bookmark. The production sibling is pg_logical_slot_get_changes, which reads and advances the slot in one call. Once a consumer has called get, the WAL behind the new position is fair game for Postgres to recycle, so consumers only call get after they’ve durably handled the data on their side.

In production you don’t drive this from SQL. You stream over the replication protocol with pg_recvlogical or a client library, which keeps a long-lived connection open and pushes new records to the consumer as Postgres writes them:

pg_recvlogical -d demo --slot=demo_slot --start -f -

What Debezium is doing on top of this

Debezium is a long-running process that opens a streaming replication connection to a named slot — the same way pg_recvlogical did — and ships the resulting events to somewhere else. That somewhere is usually Kafka, sometimes a file or a webhook. Between the slot and the sink, Debezium does work pg_recvlogical doesn’t: it decodes a binary output plugin and assembles each event into a stable JSON payload downstream consumers can use.

It doesn’t use test_decoding. The Postgres connector defaults to pgoutput, the same plugin Postgres uses internally for replicating between a primary and its read replicas. pgoutput data is binary, not human-readable, but it has a stable format documented in the Postgres source and it ships with Postgres itself, so it works on managed services like RDS or Cloud SQL where you can’t install custom extensions. That portability is most of why Debezium picked it.

Unlike our previous test_decoding replication slot example, pgoutput works a little differently. Instead of just decoding everything that comes through the slot, it consults a publication. A publication is a server-side filter you create with SQL that declares which tables and which operations belong in the consumer’s stream. The plugin makes it so that the replication slot only emits messages for tables that match an active publication:

CREATE PUBLICATION orders_pub FOR TABLE orders;
SELECT * FROM pg_create_logical_replication_slot('debezium_slot', 'pgoutput');

Now with this Debezium can connect, asks pgoutput to use orders_pub, and starts receiving binary messages whenever orders changes. Debezium decodes those messages, looks up the table schema, and assembles a payload.

Below is trimmed from a real Debezium event, it looks like this:

{
  "op": "u",
  "ts_ms": 1716489699123,
  "source": {
    "version": "2.7.0.Final",
    "connector": "postgresql",
    "db": "demo",
    "schema": "public",
    "table": "orders",
    "txId": 742,
    "lsn": 26523440,
    "snapshot": "false"
  },
  "before": {
    "id": 1, "customer": "alice", "amount_c": 4999, "status": "pending"
  },
  "after": {
    "id": 1, "customer": "alice", "amount_c": 4999, "status": "paid"
  }
}

op is the operation type: c for create, u for update, d for delete, and r for read. before and after are the row’s column values from before and after the change — before is populated because we set REPLICA IDENTITY FULL earlier. source.lsn is the same WAL position you saw back in pg_waldump, just rendered as a decimal: 0/194B730 in hex is 26523440 in base 10. Debezium tracks that number so that after a restart it can tell Postgres to resume the stream from the last LSN it acknowledged.

If Kafka is unavailable for an hour and Debezium can’t write events, the slot keeps Postgres holding onto every WAL record produced during the gap. When Debezium reconnects, it tells Postgres “resume from LSN X” — the last position it managed to confirm before the outage — and the stream picks up from there. No events are lost from the database side. The only at-least-once concern is the other end: if Debezium writes a message to Kafka but crashes before confirming the LSN back to Postgres, on restart it’ll re-send that one message. Downstream consumers have to be idempotent.

The failure modes

A few things bite in practice. None of them are subtle once you know what to look for.

Replication slots retain WAL until they’re consumed. Postgres normally recycles old WAL segments once they’re no longer needed for crash recovery or replication. A replication slot blocks that recycling: every segment past the slot’s last-acknowledged position has to stay on disk until the consumer reads it. If the consumer dies and nobody drops the slot, the pg_wal/ directory grows without bound until the disk fills.

To catch this before the disk does, you need to know how much WAL each slot is currently pinning. Every slot on the server shows up as a row in pg_replication_slots, and the column that matters is restart_lsn which is the earliest LSN the slot still requires Postgres to keep around. The cluster’s current write position is returned by pg_current_wal_lsn(). The byte distance between those two positions is the amount of WAL the slot is holding hostage, and pg_wal_lsn_diff does that subtraction in LSN units:

SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS bytes_retained
FROM pg_replication_slots;

 slot_name | bytes_retained
-----------+----------------
 demo_slot |          48376

48 KB on a healthy slot, well under any threshold you’d care about. In production I think you want an alert on bytes_retained per slot anyway, because a stalled consumer with no monitor is exactly what fills the disk.

Idle replication slots still need to advance. A slot’s restart_lsn only moves when the consumer reads a change and acknowledges back. If the published table is low-traffic and nothing flows through the slot, the consumer has nothing to acknowledge, and restart_lsn stays anchored where it was. The rest of the cluster keeps writing WAL though — other tables, internal background work — so pg_current_wal_lsn() advances while the slot’s position does not. The gap between them grows for the same reason a dead consumer’s gap grows, even though this consumer is perfectly healthy. The fix is heartbeats. Debezium has a heartbeat.interval.ms setting that periodically writes a no-op into a heartbeat table. That write produces a WAL record, Debezium reads and acknowledges it, and restart_lsn advances past the idle bytes.

The initial snapshot is its own thing. When Debezium first attaches to a database, the slot only captures changes from that moment onward — it doesn’t know about rows that already exist. To get the existing state, Debezium opens a long-running transaction with repeatable-read isolation, which lets it see a frozen view of the database as of when the transaction began even as other transactions keep writing. Inside that transaction, Debezium reads every row from every included table and emits each one as an r (read) event. Meanwhile, the slot is retaining every WAL record produced after the snapshot’s start LSN, because Debezium will switch to streaming mode at that LSN once the copy finishes. On a large database the snapshot can take hours, and the retained WAL grows the whole time. Snapshotting a 2 TB table on a high-write database can pin hundreds of GB of WAL on disk before the cutover. Plan disk headroom around the snapshot, not the steady state.

Schema changes during streaming are mostly fine, sometimes not. When a table’s schema changes, pgoutput emits a Relation message before the next row event, telling the consumer “from this point forward, this table looks like this.” Debezium uses that to keep its internal schema view in sync, so additive changes — adding columns, adding tables — go through without much drama. The painful cases are anything that invalidates the assumption that old rows are still translatable into the new schema. A column rename breaks downstream lookups; a type change can fail to cast existing values. The Debezium Postgres connector schema-change docs cover the matrix in detail.

Why this is worth knowing

The reason I think it’s worth understanding the WAL is that the failure modes don’t make sense at the Debezium-as-a-service level. “My pipeline is lagging” or “we ran out of disk” are symptoms whose explanation lives one layer down — in slot retention, in heartbeat config, in REPLICA IDENTITY choices made when the table was created. Knowing the layer lets you debug from the database side, not just from the connector dashboard.

It also makes the build-versus-buy conversation cleaner. Debezium is doing a careful, well-engineered job of consuming pgoutput and normalizing it into Kafka. If your needs match — Kafka sink, Debezium envelope, the failure modes above — use it. If they don’t, you can write a Postgres consumer in a couple hundred lines of Go or Python, because the protocol is documented and the replication slot durability is doing the hard work for you.