Working with Logical Decoding
1. Understanding Logical Decoding
| Concept | Detail |
|---|
| Reads | WAL into logical row-level changes |
| Use Cases | CDC, audit, downstream sync, ETL |
| Slot | Durable cursor in WAL |
| Output Plugin | Format (test_decoding, pgoutput, wal2json) |
2. Configuring wal_level for Logical Decoding
# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
3. Creating Logical Replication Slot
SELECT * FROM pg_create_logical_replication_slot('cdc_slot', 'pgoutput');
4. Using Output Plugins
| Plugin | Use |
|---|
| pgoutput | Built-in; powers logical replication / Debezium |
| test_decoding | Human-readable, debugging only |
| wal2json | JSON change stream |
5. Consuming Changes
SELECT * FROM pg_logical_slot_get_changes('cdc_slot', NULL, NULL);
-- Advances slot; changes are gone after fetch
6. Peeking at Changes
SELECT * FROM pg_logical_slot_peek_changes('cdc_slot', NULL, NULL);
-- Does NOT advance slot (debugging)
7. Dropping Replication Slot
SELECT pg_drop_replication_slot('cdc_slot');
Warning: Inactive slots pin WAL forever; disk fills up. Always drop unused slots.
8. Monitoring Replication Slots
SELECT slot_name, slot_type, active, restart_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
9. Understanding Change Data Capture
| Tool | Built On |
|---|
| Debezium | pgoutput slot + Kafka Connect |
| AWS DMS | logical decoding (test_decoding/pglogical) |
| Native logical replication | pgoutput + subscriptions |
10. Using Logical Decoding for Audit Trails
-- Consume slot in background worker → write each change to audit_log topic
-- Guarantees: every committed change observed exactly once per consumer.