WAL Level Insufficient for Replication

Scenario

A team sets up logical replication for a new reporting pipeline. The subscriber connects successfully, but when creating the publication, they get ERROR: logical replication not enabled or the subscriber gets ERROR: could not start WAL streaming: FATAL: replication slot "myslot" does not exist. The publisher’s wal_level is set to replica — sufficient for streaming replication, but not for logical replication.

How to Identify

Conditions:

• CREATE PUBLICATION fails or logical replication slot cannot be created
• SHOW wal_level returns replica or minimal instead of logical
• CREATE REPLICATION SLOT ... LOGICAL fails with permission/level error
• Streaming replication works fine (it only needs replica)
• After changing wal_level, the old WAL slots and publications survive but need refresh

Analysis Steps

-- Check current WAL level
SHOW wal_level;
-- 'minimal'  = no replication at all
-- 'replica'  = streaming + physical replication only
-- 'logical'  = streaming + physical + logical replication

-- Check if logical replication is possible with current setting
SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders');

-- Check existing replication slots
SELECT slot_name, slot_type, active, wal_status
FROM pg_replication_slots;
-- slot_type = 'logical' requires wal_level = 'logical'

-- Check existing publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;
-- Publications exist but may not be replicating if wal_level insufficient

-- Try to create a logical replication slot (will fail if wal_level != logical):
-- SELECT pg_create_logical_replication_slot('test_slot', 'pgoutput');
-- ERROR: logical replication not enabled
-- HINT: Set wal_level to 'logical' to enable logical replication.

Pitfalls

• Changing wal_level from replica to logical requires a full PostgreSQL restart — a reload is not sufficient.
• wal_level = logical generates slightly more WAL than replica because it includes additional information needed for logical decoding (row-level change information).
• After restart: existing streaming standbys continue to work — they don’t care about logical vs replica.
• max_replication_slots = 0 prevents all replication slot creation regardless of wal_level. Set it to at least the number of expected slots + 2 spare.
• Downgrading wal_level from logical to replica will invalidate all logical replication slots on the next restart — logical subscribers will break.

Resolution Approach

Change wal_level = logical in postgresql.conf (or ALTER SYSTEM), restart PostgreSQL, then create the publication and subscription. Ensure max_replication_slots and max_wal_senders are high enough for all expected logical connections.