PostgreSQL Logical Replication and CDC
PostgreSQL logical replication lets an external consumer receive a stream of row-level changes (INSERT, UPDATE, DELETE, TRUNCATE) from the database in real time. It piggybacks on the Write-Ahead Log — the same mechanism Postgres already uses for crash recovery — and exposes decoded, row-level events over a TCP connection using Postgres’s own wire protocol. This is the foundation for Change-Data-Capture (CDC): a consumer connects, creates a replication slot, and starts receiving a stream of changes it can forward to Kafka, another database, a search index, or anywhere else.
How It Works — The Big Picture
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
┌──────────────────────────────────────────────────────────────────┐
│ PostgreSQL Server │
│ │
│ ┌──────────┐ ┌─────────────┐ ┌──────────────────────────┐ │
│ │ Client │───▶│ WAL Writer │───▶│ WAL (wal_level=logical)│ │
│ │ (INSERT) │ └─────────────┘ └──────────┬───────────────┘ │
│ └──────────┘ │ │
│ ▼ │
│ ┌───────────────────────────┐ │
│ │ WAL Sender Process │ │
│ │ (one per connected sub) │ │
│ └──────────┬────────────────┘ │
│ │ │
│ ┌──────────▼────────────────┐ │
│ │ Logical Decoding │ │
│ │ ┌────────────────────┐ │ │
│ │ │ Output Plugin │ │ │
│ │ │ (pgoutput) │ │ │
│ │ └────────────────────┘ │ │
│ └──────────┬────────────────┘ │
│ │ │
│ ┌──────────▼───────────────┐ │
│ │ Replication Slot │ │
│ │ (tracks consumer's LSN) │ │
│ └──────────┬───────────────┘ │
│ │ │
│ ┌──────────▼────────────────┐ │
│ │ Publication │ │
│ │ (filters tables/actions) │ │
│ └───────────────────────────┘ │
└──────────────────────────────────┬───────────────────────────────┘
│ PostgreSQL wire protocol
│ (COPY stream)
▼
┌──────────────────────────────────────────────────────────────────┐
│ Replication Client │
│ │
│ ┌────────────────────────────────────────────────────────────┐ │
│ │ 1. Receive XLogData messages │ │
│ │ 2. Parse: RelationMessage, InsertMessage, UpdateMessage… │ │
│ │ 3. Apply changes (write to another DB, Kafka, file, etc.) │ │
│ │ 4. Send StandbyStatusUpdate to confirm progress │ │
│ └────────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────┘
The WAL writer in the diagram is not replication-specific. It is the core of PostgreSQL’s normal write path: the client does an INSERT/UPDATE/DELETE, PostgreSQL writes the change to WAL first (the “write-ahead” part) for crash recovery, then the actual data pages get updated in shared buffers and eventually flushed to disk by the background writer/checkpointer. The WAL exists whether or not you use replication. If Postgres crashes after writing WAL but before flushing data pages, it replays the WAL on startup to recover. Logical replication piggybacks on the WAL — the WAL sender reads from the same WAL files that were already being written for durability. It does not create a separate write path.
The Wire Protocol
The connection between the PostgreSQL server and the CDC consumer uses PostgreSQL’s custom binary protocol running directly over TCP (default port 5432). It is not HTTP — it’s a purpose-built protocol that supports streaming, which is important because the server needs to continuously push changes without the client polling.
Authentication is a direct handshake built into the protocol:
- Client opens a TCP connection and sends a StartupMessage (username, database, protocol version)
- Server responds with an authentication request (e.g., SCRAM-SHA-256)
- Client sends credentials
- Server responds with AuthenticationOk (or error)
- Server sends parameter messages and ReadyForQuery
TLS can be layered on top (client sends an SSLRequest before startup), but it’s still not HTTP — it’s TLS wrapping the Postgres protocol directly.
Replication Connections
A replication connection is a regular Postgres connection with an extra parameter in the connection string. There are two modes:
| Parameter | SQL queries | Replication commands | Typical use |
|---|---|---|---|
replication=database |
Yes | Yes | Logical replication |
replication=true |
No | Yes | Physical standby servers |
With replication=database, you get a full connection that can do regular SQL and replication commands. You can even start one from psql:
1
psql "host=localhost dbname=mydb replication=database"
And then issue replication commands:
1
2
3
IDENTIFY_SYSTEM;
CREATE_REPLICATION_SLOT my_slot LOGICAL pgoutput;
START_REPLICATION SLOT my_slot LOGICAL 0/0;
Write access is not restricted at the connection level — if you need to lock down a replication user, restrict the role’s privileges (e.g., grant only SELECT and REPLICATION, no INSERT/UPDATE/DELETE). The replication=database mode was added for logical replication, where the consumer often needs to query table schemas to understand the structure of the changes being streamed.
WAL Level (wal_level)
PostgreSQL’s Write-Ahead Log records every change before it hits disk. The wal_level setting controls how much detail gets written. Each level is a superset of the one below — more information means larger WAL files and slightly more I/O overhead.
| Level | What it records | Use case |
|---|---|---|
minimal |
Only enough for crash recovery | Standalone, no replicas |
replica |
Enough for physical standby replication + archiving | Streaming replication |
logical |
Everything in replica plus row-level change data (column values, tuple images) |
Logical replication / CDC |
replica has been the default since PostgreSQL 10 (before that, minimal was the default). This means physical replication works out of the box without a config change. You still have to explicitly set logical if you need it.
Why is it called “logical”?
Because it operates at the level of logical concepts — rows, columns, tables, inserts — as opposed to physical concepts like file offsets, page numbers, and raw bytes. The naming follows the same distinction databases use elsewhere (logical vs physical query plans, logical vs physical backups).
What each level actually records
At wal_level=replica, a WAL record for an INSERT looks roughly like:
1
2
3
Block reference: rel 1663/16384/16385, fork main, block 42
Offset: 128, length: 64
Data: 0x00A4F2E1 9C03 ... (raw bytes of the heap page change)
It says “at this position in this file, write these bytes.” A physical standby can replay this because it has the same files in the same layout.
At wal_level=logical, the same INSERT also includes:
1
2
3
Relation: public.orders (OID 16385)
Columns: id (int4), customer (text), amount (numeric)
New tuple: (42, 'alice', 99.95)
The actual row data with column names and types, so logical decoding can reconstruct the change as a meaningful row-level event. This is what makes it possible to stream changes to consumers that don’t have access to the physical data files — a different Postgres version, a different database, or not a database at all.
At wal_level=minimal, some operations skip WAL entirely when safe — for example, CREATE TABLE ... AS SELECT or COPY into a table created in the same transaction. If the transaction doesn’t commit, the whole table gets thrown away, so no WAL is needed. This makes it the most efficient for single-server setups that never replicate.
wal_level = logical is the key setting for CDC. Without it, the WAL doesn’t contain enough information to reconstruct what changed — only which pages changed. This is a server-level setting that requires a restart.
Replication Slots (max_replication_slots)
A replication slot is a server-side bookmark that tracks how far a particular consumer has read through the WAL. It guarantees the server won’t delete WAL segments the consumer hasn’t processed yet. max_replication_slots sets the maximum number of these bookmarks the server will maintain simultaneously. Each CDC consumer needs its own slot.
Slots are persistent — they exist on the server whether or not anyone is currently connected to them. A slot is created and stays there until explicitly dropped, regardless of consumer activity. This is different from max_wal_senders, which limits active connections. You can have a slot with no one consuming from it.
If you run out of slots, new consumers can’t connect. If a slot’s consumer goes offline for a long time (or is abandoned entirely), the slot keeps existing, keeps preventing WAL cleanup, but nobody is consuming from it — so the WAL just grows forever. The cap gives the DBA explicit control over how many of these open-ended commitments the server takes on, the same philosophy as max_connections or max_wal_senders.
Slots vs Publications
Slots and publications are independent objects — neither references the other. A slot tracks a WAL position; a publication defines a table filter. The CDC consumer ties them together at consumption time. When starting replication, you specify both the slot and the publication:
1
2
3
CREATE_REPLICATION_SLOT my_slot LOGICAL pgoutput;
START_REPLICATION SLOT my_slot LOGICAL 0/0
(proto_version '1', publication_names 'my_pub');
You could use the same slot with different publications, or consume from a slot without any publication at all (you would get all decoded changes unfiltered). Multiple consumers each need their own slot, but they can reference the same publication.
WAL Senders (max_wal_senders)
A WAL sender is a server process that streams WAL data to a connected replication client. PostgreSQL spawns one WAL sender process per connected consumer. Each process independently reads from the WAL, runs logical decoding, streams changes over its own TCP connection, and tracks progress via its own replication slot.
A WAL sender is the process; a slot is the bookmark it reads from. When a consumer connects, Postgres spawns a WAL sender and attaches it to the consumer’s replication slot to know where to start reading. They are not one-to-one in the same way:
- A slot exists all the time, even when no one is connected
- A WAL sender only exists while a consumer is actively connected
- Every active slot needs exactly one dedicated WAL sender — no two slots share a WAL sender
- But not every WAL sender needs a slot — physical standbys use a WAL sender to stream WAL without necessarily using a replication slot
So you could have 5 slots but only 2 WAL senders running (3 consumers are offline). Or you could need more WAL senders than slots if you also have physical standbys:
- 5 replication slots (logical consumers)
- 3 physical standbys without slots
- Total WAL senders needed: 8
max_wal_senders limits how many of these processes can run concurrently. This must be at least as large as max_replication_slots (plus any physical standby replicas).
The Replication Role
Connecting to PostgreSQL for replication uses a special protocol mode (replication=database in the connection string). Only users with the REPLICATION attribute (or superusers) are allowed to do this.
1
CREATE ROLE cdc_reader WITH REPLICATION LOGIN PASSWORD 'secret';
The REPLICATION privilege is separate from regular table access. A replication user doesn’t need SELECT on your tables — the WAL sender reads from WAL, not from tables directly. But they do need the privilege to create replication connections and manage slots.
Publications
A publication defines what data gets replicated. It acts as a filter:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Everything
CREATE PUBLICATION all_changes FOR ALL TABLES;
-- Specific tables
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;
-- Specific operations
CREATE PUBLICATION inserts_only FOR TABLE orders
WITH (publish = 'insert');
-- Column subsets (PG 15+)
CREATE PUBLICATION partial FOR TABLE users (id, name, email);
-- Row filters (PG 15+)
CREATE PUBLICATION big_orders FOR TABLE orders WHERE (total > 1000);
Publications live on the publisher (source) side.
Replica Identity
Logical replication only carries row-level DML — INSERT, UPDATE, DELETE, and TRUNCATE (PostgreSQL 11+). No DDL comes through (no CREATE TABLE, ALTER TABLE, etc.). The logical decoding layer filters DDL out and only emits row change events.
For INSERT, the full new row is always included — no extra identification is needed. But for UPDATE and DELETE, the consumer receives “this row was changed” or “this row was deleted” and needs to know which row was affected. Replica identity controls what extra identifying information is included in the WAL for these operations. It is configured per table, since different tables have different tradeoffs between WAL size and identification needs.
1
2
3
4
5
6
7
8
9
10
11
-- Use primary key columns (default)
ALTER TABLE orders REPLICA IDENTITY DEFAULT;
-- Use a specific unique index
ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_email_idx;
-- Include the entire old row
ALTER TABLE orders REPLICA IDENTITY FULL;
-- Include nothing (UPDATE/DELETE won't work for subscribers)
ALTER TABLE orders REPLICA IDENTITY NOTHING;
| Setting | What’s in UPDATE/DELETE messages | Trade-off |
|---|---|---|
DEFAULT |
Primary key columns only | Minimal WAL overhead; consumer must match on the same PK |
INDEX |
Columns of the named unique index | Useful when PK is synthetic but you match on a natural key |
FULL |
All column values of the old row | Most WAL overhead; required if table has no PK/unique index |
NOTHING |
No old row data | Smallest WAL; UPDATE/DELETE cannot be applied by consumers |
The Replication Protocol
Logical replication uses PostgreSQL’s streaming replication protocol over a regular TCP connection:
- Client connects with
replication=databaseparameter - IDENTIFY_SYSTEM — server returns its ID, timeline, current LSN
- CREATE_REPLICATION_SLOT — server creates a bookmark for this consumer
- START_REPLICATION — server begins streaming WAL from the slot’s position
- Server streams CopyData messages containing:
XLogData— actual WAL changes (decoded by the output plugin)PrimaryKeepaliveMessage— heartbeat from server
- Client sends
StandbyStatusUpdate— “I’ve processed up to LSN X” - Server advances the slot’s confirmed position and can reclaim old WAL
Two Interfaces: SQL Functions vs. Replication Protocol Commands
PostgreSQL provides two ways to interact with logical decoding — through regular SQL functions or through the replication protocol. They access the same underlying machinery (same slots, same output plugins, same WAL data), but use different “languages” depending on how you connect.
| Normal SQL connection | replication=database connection |
|
|---|---|---|
| Create a slot | SELECT pg_create_logical_replication_slot('my_slot', 'test_decoding'); |
CREATE_REPLICATION_SLOT my_slot LOGICAL test_decoding; |
| Consume changes | SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL); |
START_REPLICATION SLOT my_slot LOGICAL 0/0; |
| Peek at changes | SELECT * FROM pg_logical_slot_peek_changes('my_slot', NULL, NULL); |
N/A |
| Drop a slot | SELECT pg_drop_replication_slot('my_slot'); |
DROP_REPLICATION_SLOT my_slot; |
| Delivery model | Pull (you poll with queries) | Push (server streams changes continuously) |
The SQL functions work from any normal connection — no special connection parameters needed. The user just needs the REPLICATION privilege (or superuser). These are convenient for debugging, learning, and lightweight custom CDC where polling is acceptable.
The replication protocol commands (CREATE_REPLICATION_SLOT, START_REPLICATION, IDENTIFY_SYSTEM, etc.) are not SQL — they are a separate command set understood only by replication connections. This is what pg_recvlogical, Debezium, CREATE SUBSCRIPTION, and production CDC consumers use under the hood for real-time streaming.
Either interface can create and drop slots — a slot created via SQL is identical to one created via the replication protocol, and vice versa. What matters is which interface you use to consume from it: SQL functions give you pull-based access, while the replication protocol gives you push-based streaming.
“Peek” (pg_logical_slot_peek_changes) only exists as a SQL function — there is no replication protocol equivalent. The replication protocol is push-based and streaming: once you issue START_REPLICATION, the server continuously sends changes and the slot position advances as you acknowledge them. There is no concept of “look without consuming” in that model. Peeking only makes sense in the pull-based SQL model where you are querying a snapshot of pending changes and might want to inspect them without advancing the slot.
Output Plugins
The logical decoding system is pluggable — the WAL contains raw internal change data, and an output plugin translates that into a format the consumer can understand. You choose the plugin when creating a replication slot:
1
CREATE_REPLICATION_SLOT my_slot LOGICAL pgoutput;
PostgreSQL ships with:
pgoutput— the built-in binary protocol (added in PostgreSQL 10 specifically for logical replication)test_decoding— a human-readable format for debugging/learning
Third-party plugins include wal2json (JSON output) and decoderbufs (Protocol Buffers). pgoutput only handles the output side — it takes decoded WAL changes and serializes them into binary messages. It has no role outside of the logical replication pipeline.
LSN (Log Sequence Number)
An LSN is a 64-bit pointer into the WAL stream, formatted as XXXXXXXX/XXXXXXXX (e.g., 0/15D68C0). It always increases. The gap between two LSNs tells you how many bytes of WAL exist between them. The name comes from the original PostgreSQL terminology — the WAL was called the “transaction log” or “xlog,” which is why you see XLogData, XLogRecord, and similar names throughout the Postgres codebase.
Setting Up Logical Replication for CDC
Two sides need to be configured: the PostgreSQL server (publisher) and the CDC consumer that connects to it.
Step 1: Configure the Server
These are server-level settings in postgresql.conf. Changing wal_level requires a restart.
1
2
3
4
# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
Step 2: Allow Replication Connections
Add an entry in pg_hba.conf to allow the CDC consumer’s host to make replication connections.
1
2
3
# pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host all cdc_reader 192.168.1.0/24 scram-sha-256
Step 3: Create a Replication User
1
2
3
4
5
CREATE ROLE cdc_reader WITH REPLICATION LOGIN PASSWORD 'secret';
-- Grant SELECT if the consumer needs to query table schemas
-- (common with replication=database connections)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_reader;
Step 4: Set Replica Identity on Tables
This is per table. If the table has a primary key, DEFAULT is usually fine. For tables without a primary key that need UPDATE/DELETE replication, use FULL.
1
2
ALTER TABLE orders REPLICA IDENTITY DEFAULT; -- uses PK
ALTER TABLE events REPLICA IDENTITY FULL; -- no PK, send full row
Step 5: Create a Publication
Define which tables and operations the consumer will receive.
1
CREATE PUBLICATION my_pub FOR TABLE orders, customers, events;
Step 6: Connect and Start Consuming
From the CDC consumer, open a replication connection and start streaming.
1
2
3
4
5
6
7
8
9
-- Connect with replication=database
-- psql "host=publisher dbname=mydb user=cdc_reader password=secret replication=database"
-- Create a replication slot (one-time)
CREATE_REPLICATION_SLOT my_slot LOGICAL pgoutput;
-- Start streaming (the connection switches to streaming mode)
START_REPLICATION SLOT my_slot LOGICAL 0/0
(proto_version '1', publication_names 'my_pub');
The 0/0 is the LSN (WAL position) to start reading from. 0/0 is a special value meaning “start from wherever the slot was created.” If your consumer disconnects and reconnects, you could pass the last confirmed LSN (e.g., 0/15D68C0) to resume from that point — though the slot already tracks this, so 0/0 usually works for reconnection too.
After START_REPLICATION, the connection is no longer interactive. The server pushes XLogData messages and the consumer sends StandbyStatusUpdate messages back to confirm progress.
