Re: POC: enable logical decoding when wal_level = 'replica' without a server restart - Mailing list pgsql-hackers

On Wed, Oct 22, 2025 at 8:28 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Oct 22, 2025 at 2:40 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Fri, Oct 17, 2025 at 11:09 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> >
> > I want to discuss the create-publication case, which currently gives
> > this warning:
> >
> > postgres=# create publication pub1 for all tables;
> > WARNING:  logical decoding should be allowed to publish logical changes
> > HINT:  Before creating subscriptions, set "wal_level" >= "logical" or
> > create a logical replication slot when "wal_level" = "replica".
> > CREATE PUBLICATION
> >
> > But is this warning really necessary during publication creation?
> >
>
> On HEAD, the WARNING/HINT is as follows for the above case:
> WARNING:  "wal_level" is insufficient to publish logical changes
> HINT:  Set "wal_level" to "logical" before creating subscriptions.
>
> Shouldn't we simply change the HINT to "Set "wal_level" to "logical"
> or create a logical replication slot before creating subscriptions."?
>

I had suggested in a previous review [1-#6b] that perhaps this HINT
wording is overkill now. e.g. I felt now maybe it only needs to say.
HINT: Set "wal_level" >= "replica".

E.g. I thought there is no need to mention "create a logical slot
before creating subscriptions", because creating a typical
subscription (which has a slot) is doing that anyway, so the
publisher's effective_wal_level will increase on the fly, without the
user needing to do anything.

At least, that is what I observed with the experiment shown below:

Indeed, doesn't it mean this entire WARNING is redundant now? e.g.
When wal_level is "minimal", I thought the CREATE PUBLICATION command
would be impossible in the first place. And for wal_level =  "replica"
or "logcial", it just works without the user doing anything.

//////////
EXPERIMENT -- Try creating a subscription that will automatically bump
up the effective_wal_level on the Publisher.

==========
On Publisher
==========

test_pub=# show wal_level; show effective_wal_level;
 wal_level
-----------
 replica
(1 row)

 effective_wal_level
---------------------
 replica
(1 row)

test_pub=# create publication mypub for all tables;
WARNING:  logical decoding must be enabled to publish logical changes
HINT:  Before creating subscriptions, set "wal_level" >= "logical" or
create a logical replication slot when "wal_level" = "replica".
CREATE PUBLICATION

==========
On Subscriber:
==========

test_sub=# show wal_level; show effective_wal_level;
 wal_level
-----------
 replica
(1 row)

 effective_wal_level
---------------------
 replica
(1 row)

test_sub=# create subscription mysub connection 'dbname=test_pub'
publication mypub;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
test_sub=# 2025-10-23 13:56:08.260 AEDT [14453] LOG:  logical
replication apply worker for subscription "mysub" has started
2025-10-23 13:56:08.278 AEDT [14455] LOG:  logical replication table
synchronization worker for subscription "mysub", table "t1" has
started
2025-10-23 13:56:08.391 AEDT [14455] LOG:  logical replication table
synchronization worker for subscription "mysub", table "t1" has
finished

==========
Now, back on the Publisher:
==========

test_pub=# show wal_level; show effective_wal_level;
 wal_level
-----------
 replica
(1 row)

 effective_wal_level
---------------------
 logical
(1 row)

======
[1] https://www.postgresql.org/message-id/CAHut%2BPtB4zZrMKm_vUd-JO29Uq2479unx1KR%3DhGu%3DsQxfSVXJg%40mail.gmail.com

Kind Regards,
Peter Smith.
Fujitsu Australia