PostgreSQL 19: Enabling Logical Decoding Dynamically with effective_wal_level

PostgreSQL 19 introduces effective_wal_level, which allows logical decoding to be activated only when it’s actually needed. There’s no need to modify wal_level or restart the server. The feature is especially handy for DBAs who run logical decoding and logical replication.

The problem before PostgreSQL 19

Suppose a server runs with wal_level = replica. That is enough for physical streaming replication. If you later need logical decoding, for CDC, logical replication or a migration pipeline, the server must run with wal_level = logical.
Before PostgreSQL 19, moving from replica to logical required a restart. A simple request like “enable logical decoding” therefore meant changing the setting, restarting PostgreSQL and only then starting logical decoding. In production, that restart can call for a maintenance window.

How effective_wal_level works

When a valid logical replication slot needs logical decoding, PostgreSQL operates at an effective level of logical. This happens even though wal_level is still replica, and no restart is needed for the transition. If no logical decoding is needed, the effective level stays at replica.
The difference between the two approaches (for PostgreSQL 19, starting from wal_level = replica):

• Enabling logical decoding: Before, you set wal_level = logical in the configuration. In PostgreSQL 19, you create a logical replication slot.
• Restart: Before, a restart was required. In PostgreSQL 19, it is not.
• Configured wal_level afterwards: Before, it was logical. In PostgreSQL 19, it remains replica.
• effective_wal_level: Before, it did not exist. In PostgreSQL 19, it is logical while a valid logical slot exists.
• When the last logical slot is dropped or invalidated:
• Before, the level stayed at logical until you changed the configuration and restarted. In PostgreSQL 19, it returns to replica, asynchronously.

A quick demonstration

check both settings with current_setting(). At first, both wal_level and effective_wal_level show replica. After creating a logical replication slot named demo_slot with the pgoutput plugin, wal_level still shows replica but effective_wal_level shows logical. Once the last valid logical slot is removed, PostgreSQL can eventually return the effective level to replica.

Why DBAs should care

The feature helps most in setups that use logical replication, CDC, Debezium, data migration pipelines and logical decoding in general. The old path was to change the setting, restart, then decode. The new one is to start from wal_level = replica, create a logical slot, let effective_wal_level become logical, and begin decoding. This makes the transition more dynamic and avoids a restart whose only purpose is to change the effective WAL level.

wal_level vs effective_wal_level

• wal_level is the configured WAL level.
• effective_wal_level is the level PostgreSQL is actually operating with. when troubleshooting logical decoding in PostgreSQL 19, you should not rely on SHOW wal_level; alone. Also run SHOW effective_wal_level;, or query both together with current_setting().

Things to check before relying on it

• Standby servers: Logical slots on a standby are invalidated if the primary’s effective_wal_level falls below logical.
• Tools that read wal_level directly: A tool that validates only the configured value may not recognize the effective one, so test your CDC tool first.
• wal_level = minimal: The documented behavior applies when wal_level = replica. The PostgreSQL documentation doesn’t describe it for minimal, so don’t assume it applies.
• Timing of the drop back to replica: The change is asynchronous, so don’t expect it to happen instantly.

Key takeaway

effective_wal_level is a small change, but it is a useful operational improvement. PostgreSQL can keep wal_level = replica and use logical effectively only when logical decoding is actually needed. In short, wal_level shows what is configured, and effective_wal_level shows what PostgreSQL is using. For DBAs, this means one less restart-driven operation when enabling logical decoding.

Comments

[Shubham Baldava]

Great write-up. The "test your CDC tool first" caveat is spot on. We tried this with OLake (open-source Postgres → Iceberg/Parquet CDC) on PG 19rc1, and it's exactly the gap you describe.

Our CDC pre-flight check ran SHOW wal_level and required logical, so a wal_level = replica server with a logical slot got rejected even though logical decoding was active. The replication itself was fine. With the check switched to effective_wal_level on 19+, backfill plus streamed inserts, updates and deletes over pgoutput all worked with wal_level = replica. Fix is up here: github.com/datazip-inc/olake/pull/...

One practical tip: create the logical slot before running your CDC tool's connection check. The effective level only switches once a valid slot exists. When we dropped the slot, it went back to replica within about a second in our test, but treat that as asynchronous like you say.