How to Read a PostgreSQL WAL Stream Without a Heavy CDC Tool

Not every team needs Debezium and a Kafka cluster to know when a row changed. If you're prototyping, working with a small number of tables, or just want to understand what a CDC connector is actually doing under the hood before committing to running one in production, PostgreSQL's own logical replication protocol is queryable directly, no extra infrastructure required.

Here's how to set up a logical replication slot and read change events straight out of Postgres.

Step 1: Confirm Your Postgres Supports Logical Replication

Logical replication has been part of core PostgreSQL since version 10, and it's usually enabled by default on modern hosted instances, but self-managed installs may need wal_level set to logical in postgresql.conf. Check the current setting first:

SHOW wal_level;

If it returns anything other than logical, you'll need to change it and restart the database, which is a meaningful operational step on a production instance, plan it like any other config change requiring a restart, not something to run mid-afternoon on a whim.

Step 2: Create a Publication

A publication tells Postgres which tables to include in the logical replication stream. Scope it to exactly what you need rather than the whole database, which keeps the stream smaller and easier to reason about:

CREATE PUBLICATION orders_publication FOR TABLE orders, order_items;

You can add or drop tables from an existing publication later, but starting narrow is the safer default, especially while you're still learning how the output looks.

Step 3: Create a Replication Slot

The replication slot is what actually persists your position in the log between connections, so Postgres knows how far back to hold write-ahead log segments for you. Without a slot, a disconnected consumer would simply miss anything that happened while it was offline.

SELECT pg_create_logical_replication_slot('orders_slot', 'pgoutput');

pgoutput is the built-in logical decoding output plugin as of Postgres 10 and later, and it's what most CDC tools, including Debezium, use by default, so what you see here is genuinely representative of what a production connector would receive.

Step 4: Read Changes From the Slot

You can poll the slot directly using SQL, which is the fastest way to see raw change events without writing any client code:

SELECT * FROM pg_logical_slot_get_changes('orders_slot', NULL, NULL);

Each call returns the changes that have accumulated since the last time you read from the slot, formatted as text by default. For a machine-readable format you'd parse programmatically, a client library speaking the streaming replication protocol is the more realistic path, but for understanding what's actually flowing through the log, this SQL-level view is the fastest way to see it.

Step 5: Watch for the One Thing That Bites Everyone

An inactive replication slot doesn't get cleaned up automatically. Postgres holds onto write-ahead log segments for as long as a slot exists and isn't fully consumed, specifically so a slow or offline consumer can catch up later without data loss. That's the entire point of the mechanism, but it means a forgotten slot with nothing reading from it will quietly accumulate WAL on disk indefinitely.

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Run this occasionally, especially after any experiment where you created a slot and never got around to consuming it or dropping it. An unbounded, unwatched slot is a genuinely common way for a Postgres disk to fill up in production, and it rarely announces itself until the database starts refusing writes.

Understanding What You're Actually Looking At

The output from pg_logical_slot_get_changes isn't pretty by default, it's a text representation meant for humans debugging, not machines parsing programmatically. Each row corresponds to one change, and you'll see the transaction ID, the operation (insert, update, or delete), the table it happened on, and the column values involved. Updates show both old and new values if you've set REPLICA IDENTITY FULL on the table beforehand, which is worth doing on anything you're experimenting with, since the default replica identity only guarantees the primary key is present on updates and deletes, not the full row.

ALTER TABLE orders REPLICA IDENTITY FULL;

Skip this step and you'll be confused later about why your delete events only show a primary key and nothing else useful for reconstructing what was actually removed. It's a one-line fix that saves a genuinely confusing debugging session.

Common Errors You'll Hit

A few mistakes show up constantly when people try this for the first time.

"logical replication is not supported", this almost always means wal_level is still set to replica or minimal, not logical. Double-check Step 1, and remember the setting requires a full database restart to take effect, not just a config reload.

A slot that won't drop, if you try to remove a replication slot while something still has an active connection to it, Postgres will refuse. Make sure any client or session reading from the slot has disconnected first:

SELECT pg_drop_replication_slot('orders_slot');

Permission errors creating a publication or slot, logical replication setup typically requires the REPLICATION role attribute or superuser access, which is a deliberate restriction since a mismanaged slot can genuinely take a production database down by exhausting disk space. Don't grant this broadly; scope it to the specific role your CDC tooling or your debugging session actually uses.

When to Graduate to a Real Connector

Reading the slot manually with SQL is great for learning and for tiny, low-stakes use cases, but it doesn't give you schema change handling, delivery guarantees across restarts, or a message bus multiple consumers can read from independently. Once more than one downstream system needs the same change stream, or you need the pipeline to survive a consumer being offline for hours without falling behind permanently, a proper connector like Debezium feeding Apache Kafka earns its operational cost.

The manual approach above is genuinely the right tool for understanding the mechanism, verifying that logical replication is configured correctly, or debugging why a production connector is behaving strangely, since you can compare what the raw slot is producing against what your connector claims to be receiving. It's a debugging tool and a learning tool more than a production architecture on its own.

There's also a middle ground worth knowing about before jumping straight to a full Kafka deployment: some teams run a lightweight consumer that reads directly from a logical replication slot using a client library rather than SQL polling, and writes changes straight to a destination, a search index, a cache, a webhook, without a message broker in between at all. That's a reasonable architecture for a single consumer with modest volume. It stops being reasonable the moment a second consumer needs the same stream, because now you're either duplicating the read logic or building your own fan-out, which is exactly the problem a message bus like Kafka already solves.

For a fuller picture of how this fits into a production-grade pipeline, including schema registries, exactly-once-ish delivery patterns, and lag monitoring, 137foundry.com has a longer guide covering the whole architecture. The companion article on change data capture walks through exactly how the pieces above connect to Kafka, schema handling, and monitoring in a setup built to run unattended in production rather than from a psql prompt.

Once you've seen raw change events coming out of a replication slot with your own eyes, the abstractions a tool like Debezium provides make a lot more sense, because you know exactly what problem they're solving underneath the configuration.