Logical Decoding & Change Data Capture

wal_level = logical

A stricter real requirement than wal_level = replica, discovered directly in this lab: physical streaming replication (Block 3.6) works at replica, but logical decoding genuinely refuses to create a slot at anything less than logical, confirmed with a real, specific error message.

test_decoding (output plugin)

PostgreSQL's own built-in logical decoding output plugin, genuinely present on every standard PostgreSQL installation (it is what PostgreSQL's own regression test suite uses) with zero extra installation required — confirmed directly on this VM as the real, working substitute for the confirmed-absent wal2json.

pg_logical_slot_get_changes() vs pg_logical_slot_peek_changes()

get_changes() consumes changes from the slot, advancing its position so the same changes are never returned again; peek_changes() reads the same changes without advancing the slot, useful for inspecting what is pending without committing to having processed it yet.

⚙️ Discover the Real wal_level Requirement

Confirm logical decoding genuinely requires wal_level=logical, then fix it with a real restart.

psql -U postgres -c "ALTER SYSTEM SET wal_level = 'replica';" && as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w

psql -U postgres -d beer_db -c "SELECT pg_create_logical_replication_slot('cdc_slot','test_decoding');"

psql -U postgres -c "ALTER SYSTEM SET wal_level = 'logical';" && as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w

psql -U postgres -d beer_db -c "SHOW wal_level;"

student@lab:~$ as-postgres sh -c "sed -i 's/wal_level = minimal/wal_level = replica/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w' waiting for server to shut down.... done server stopped waiting for server to start.... done server started student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_create_logical_replication_slot('cdc_slot','test_decoding');" SET ERROR: logical decoding requires "wal_level" >= "logical" student@lab:~$ as-postgres sh -c "sed -i 's/wal_level = replica/wal_level = logical/' /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w' waiting for server to shut down.... done server stopped waiting for server to start.... done server started student@lab:~$ psql -U postgres -d beer_db -c "SHOW wal_level;" SET wal_level ----------- logical (1 row)

📥 Create a Real Slot and Capture Real Changes

Create a logical replication slot using the built-in test_decoding plugin, since wal2json is confirmed absent, and generate real changes to capture.

which wal2json 2>&1; find / -iname '*wal2json*' 2>/dev/null; echo DONE

psql -U postgres -d beer_db -c "SELECT pg_create_logical_replication_slot('cdc_slot','test_decoding');"

psql -U postgres -d beer_db -c "CREATE TABLE cdc_demo (id serial primary key, note text); INSERT INTO cdc_demo (note) VALUES ('first'); UPDATE cdc_demo SET note='updated' WHERE id=1; DELETE FROM cdc_demo WHERE id=1;"

student@lab:~$ which wal2json 2>&1; find / -iname '*wal2json*' 2>/dev/null; echo DONE DONE student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_create_logical_replication_slot('cdc_slot','test_decoding');" SET pg_create_logical_replication_slot ------------------------------------- (cdc_slot,0/792FE90) (1 row) student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE cdc_demo (id serial primary key, note text); INSERT INTO cdc_demo (note) VALUES ('first'); UPDATE cdc_demo SET note='updated' WHERE id=1; DELETE FROM cdc_demo WHERE id=1;" SET CREATE TABLE INSERT 0 1 UPDATE 1 DELETE 1

📖 Consume the Real Change Stream

Consume the slot and inspect the real, structured change stream directly.

psql -U postgres -d beer_db -c "SELECT data FROM pg_logical_slot_get_changes('cdc_slot', NULL, NULL);"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT data FROM pg_logical_slot_get_changes('cdc_slot', NULL, NULL);" SET data ------------------------------------------------------------------- BEGIN 895 table public.cdc_demo: INSERT: id[integer]:1 note[text]:'first' table public.cdc_demo: UPDATE: id[integer]:1 note[text]:'updated' table public.cdc_demo: DELETE: id[integer]:1 COMMIT 895 (5 rows)

🗑️ Drop the Slot and Understand the Real Risk

Drop the logical slot and confirm it is gone, understanding why an unconsumed one risks the same WAL retention bloat as a physical slot.

psql -U postgres -d beer_db -c "SELECT pg_drop_replication_slot('cdc_slot');" -c "DROP TABLE cdc_demo;"

psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_replication_slots WHERE slot_name = 'cdc_slot';"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_drop_replication_slot('cdc_slot');" -c "DROP TABLE cdc_demo;" SET pg_drop_replication_slot --------------------------- (1 row) DROP TABLE student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_replication_slots WHERE slot_name = 'cdc_slot';" SET count ------- 0 (1 row)