Logical vs physical replication, replication slots and the slot bloat disaster, publication/subscription model, WAL sender process, Debezium + Kafka change data capture, zero-downtime major version upgrades using logical replication as a migration bridge, and replication identity modes.
Module 17 — Logical Replication and CDC Pipelines
We needed to upgrade from PostgreSQL 12 to PostgreSQL 16. Physical streaming replication doesn't cross major versions. The naive approach: pg_dump, restore, brief downtime. Our database was 8TB. The dump alone took 4 hours. The restore took 6 hours. Then indexing. Total downtime: 14 hours. We told the team we could do it in 2 hours. We were wrong. Logical replication, done correctly, would have given us 0 seconds of downtime.
Physical Replication vs Logical Replication
| Physical (Streaming) | Logical | |
|---|---|---|
| Unit of transfer | WAL bytes (block-level) | Logical row changes (decoded) |
| Cross-version support | Same major version only | Works across major versions |
| Selective tables | Entire cluster | Per-table granularity |
| DDL replicated automatically | Via WAL blocks only | Not automatically |
| Bi-directional | No | Yes, carefully |
| Primary use case | HA, hot standby, read replicas | CDC, version upgrades, ETL, Kafka |
Physical replication copies WAL bytes verbatim — the standby replays the exact same block writes. It cannot cross major versions because the on-disk format changes between them. Logical replication decodes the WAL into row-level changes (INSERT/UPDATE/DELETE), which are version-agnostic. That version-agnosticism is the property that makes the zero-downtime upgrade possible.
Replication Slots — The Critical Operational Detail
Replication slots are PostgreSQL's mechanism for ensuring the WAL needed by a subscriber is not discarded before the subscriber reads it. This sounds helpful. It is helpful until the subscriber goes away.
The disaster scenario: You create a logical replication slot. The subscriber is a Debezium connector that reads changes and publishes to Kafka. The Debezium Kafka Connect worker goes down for a maintenance window. Three days pass. PostgreSQL has been faithfully retaining WAL since the slot was last consumed. WAL accumulates on disk. Your 500GB disk fills up. PostgreSQL cannot write new WAL. PostgreSQL stops accepting writes. Your entire application is down. Every application team is paging the on-call. This scenario is not hypothetical — it has killed production systems.
Monitoring slot lag — make this a production alert, not an afterthought:
SELECT
slot_name,
slot_type,
active,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS lag_size,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
ORDER BY lag_bytes DESC NULLS LAST;
Alert when any slot's lag_size exceeds 5GB. Page someone when it exceeds 20GB.
Cap WAL retention per slot using max_slot_wal_keep_size (PostgreSQL 13+):
# postgresql.conf
max_slot_wal_keep_size = '10GB'
# Slots that fall more than 10GB behind are dropped automatically.
# The subscriber must resync from scratch — painful but preferable
# to the database going down.
When a slot is dropped due to the size limit, the slot's active column goes to false and pg_replication_slots shows the slot as invalidated. The subscriber gets an error on its next attempt to read, knows it needs to resync, and can do so. Your disk stays healthy. Set the alert lower than the limit so you have time to act before the slot is dropped.
The WAL Sender Process
Every replication connection — physical or logical — is served on the publisher/primary side by a WAL sender process (walsender), visible in pg_stat_replication and as a distinct backend in pg_stat_activity. For logical replication specifically, the WAL sender does more than stream raw WAL bytes the way it does for a physical standby: it runs the logical decoding process, invoking the output plugin (pgoutput, or a third-party plugin like the one Debezium uses) to translate each WAL record back into a logical change — an INSERT/UPDATE/DELETE with actual column values — before sending it to the subscriber. This is why logical replication needs wal_level = logical: the WAL sender's decoding step requires extra information (including catalog history) that wal_level = replica doesn't retain. One WAL sender process serves one replication slot; a publisher with many subscriptions has one walsender per active subscriber connection, each independently tracking its own decoding position via that subscriber's slot.
Publication and Subscription
On the source database:
-- Create a publication for specific tables
CREATE PUBLICATION my_publication
FOR TABLE orders, products, users
WITH (publish = 'insert, update, delete');
-- Or publish all tables in the schema
CREATE PUBLICATION all_tables FOR ALL TABLES;
-- Check what's published
SELECT * FROM pg_publication_tables;
On the destination database:
-- Subscribe to the publication
-- PostgreSQL handles initial data sync automatically
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=source-db.example.com port=5432 dbname=myapp user=replicator password=secret'
PUBLICATION my_publication;
PostgreSQL handles the initial data sync (copying existing rows) and then streams ongoing changes. No application changes required during initial sync.
Monitoring subscription progress:
-- On the destination: check sync state
SELECT
subname,
received_lsn,
latest_end_lsn,
pg_size_pretty(
pg_wal_lsn_diff(latest_end_lsn, received_lsn)
) AS replication_lag
FROM pg_stat_subscription;
-- When received_lsn approaches latest_end_lsn, lag is near-zero.
-- Initial table sync status
SELECT
subname,
relname,
srsubstate, -- 'r' = ready, 'i' = initial sync in progress, 'd' = data sync
srsublsn
FROM pg_subscription_rel
JOIN pg_class ON relid = pg_class.oid
JOIN pg_subscription ON srsubid = pg_subscription.oid;
Replication Identity — The Row Identification Problem
Logical replication needs to identify which row to UPDATE or DELETE on the destination. Without a way to find the target row, UPDATE and DELETE cannot be applied.
-- DEFAULT: uses the PRIMARY KEY (the right choice for most tables)
ALTER TABLE orders REPLICA IDENTITY DEFAULT;
-- FULL: includes all column values before the change in the WAL record.
-- Works for tables without a primary key, but makes WAL significantly larger.
-- Every UPDATE writes the entire old row to WAL.
ALTER TABLE orders REPLICA IDENTITY FULL;
-- USING INDEX: use a unique index instead of the PK
ALTER TABLE orders REPLICA IDENTITY USING INDEX idx_orders_external_id;
-- NOTHING: no old row data in WAL records.
-- UPDATEs and DELETEs cannot be applied to the destination.
-- Only use if you're replicating INSERT-only tables.
ALTER TABLE orders REPLICA IDENTITY NOTHING;
Tables without a primary key and without REPLICA IDENTITY FULL will fail to replicate UPDATEs and DELETEs:
ERROR: logical replication target relation "public.orders" has
replica identity "d" (default), but the primary key is not present
Before setting up logical replication, audit all tables in the publication for missing primary keys:
SELECT
t.tablename,
pk.constraint_type
FROM pg_tables t
LEFT JOIN information_schema.table_constraints pk
ON pk.table_name = t.tablename
AND pk.constraint_type = 'PRIMARY KEY'
WHERE t.schemaname = 'public'
AND pk.constraint_type IS NULL;
-- Tables without a PK will be problematic for UPDATE/DELETE replication
Zero-Downtime Major Version Upgrade
The playbook for upgrading PostgreSQL 12 to PostgreSQL 16 with under 5 minutes of write downtime.
Step 1: Prepare the source (PG12)
-- Enable logical WAL on PG12
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();
-- Requires restart if wal_level was previously 'replica' or 'minimal'
-- Create a dedicated replication user
CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'use-a-real-secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
GRANT USAGE ON SCHEMA public TO replicator;
-- Create the publication
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
Sign in to keep reading
The rest of this module is free — sign in with Google to unlock it and track your progress.
Sign in & RegisterDiscussion
0Join the discussion