Re: POC: enable logical decoding when wal_level = 'replica' without a server restart

On Thu, Jan 16, 2025 at 4:28 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Sun, Jan 12, 2025 at 10:52 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
> > On Fri, Jan 10, 2025 at 8:28 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > Hi,
> > >
> > > On Tue, Jan 7, 2025 at 11:30 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > > >
> > > > Hi Sawada-San.
> > > >
> > > > FWIW, I also thought it was a good idea suggested by Bertrand [1] to
> > > > "hide" everything behind the slot create/delete, and thereby eliminate
> > > > the need for user intervention using those new
> > > > pg_activate/deactivate_logical_decoding functions.
> > > >
> > > > But, one concern doing it this way is how to prevent a user
> > > > (accidentally?) getting themselves into a different replication mode
> > > > without realising it? Or say they change the mode and then "forget"
> > > > that they did that.
> > >
> > > I think that as long as there is at least one logical replication slot
> > > users still have the will to execute logical decoding, so they require
> > > logical info WAL-logging. I think it would rather be good for users to
> > > be able to forget about that.
> > >
> > > > For example, If wal_level=replica and then somebody does CREATE
> > > > PUBLICATION/SUBSCRIPTION won't that implicitly enable the logical
> > > > decoding and then leave it enabled until all the logical replication
> > > > slots are eventually dropped?
> > >
> > > Yes.
> > >
> > > > Now, when the user examines wal_level it
> > > > is still going to show 'replica', which could be misleading --- e.g.
> > > > how will they even know that they can't really trust that anymore, and
> > > > they must also check the pg_get_logical_decoding_status?
> > >
> > > I think that if we automatically enable logical decoding even when
> > > wal_level=replica, we would not need pg_get_logical_decoding_status().
> > > These changes would break compatibility tools checking if wal_level is
> > > 'logical', but I guess that tools will not need to check that anymore.
> > > Users can simply understand that if there is at least one logical
> > > replication slot the system writes necessary information to use it.
> > > Having said that we anyway need to mention it in the doc and raising
> > > NOTICE/WARNING would also be a good idea as you mentioned.
> > >
> >
> > Hi Sawada-San
> >
> > IMO it seems misleading to be having logical replication behaviour
> > while the wal_level still displays as 'replica'.
> >
> > So, I was wondering if it would be worthwhile to introduce a
> > 'show_hook' for this GUC.
> >
> > I hacked a quick implementation (attached) which now displays a middle
> > value for this scenario as "replica-logical". See below:
> >
> > test_pub=# show wal_level;
> > wal_level
> > -----------
> > replica
> > (1 row)
> >
> > test_pub=# select pg_get_logical_decoding_status();
> > pg_get_logical_decoding_status
> > --------------------------------
> > disabled
> > (1 row)
> >
> > test_pub=# select pg_activate_logical_decoding();
> > pg_activate_logical_decoding
> > ------------------------------
> >
> > (1 row)
> >
> > test_pub=# show wal_level;
> > wal_level
> > -----------------
> > replica-logical
> > (1 row)
>
> I think this would break the compatibility for monitoring tools
> anyway. Given that the logical decoding is enabled if there is at
> least one logical slot, they just need to check the number of existing
> logical slots to know whether logical info WAL-logging is enabled or
> not.
>

But, that "replica-logical" string value was just for my demonstration.

From your reply, I cannot tell if you accidentally overlooked the next
part where I said:
OTOH, you might prefer to display the "replica-logical" case as "logical".

~~~

e.g. then the above example would look like this:

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

test_pub=# select pg_get_logical_decoding_status();
pg_get_logical_decoding_status
--------------------------------
disabled
(1 row)

test_pub=# select pg_activate_logical_decoding();
pg_activate_logical_decoding
------------------------------
(1 row)

test_pub=# show wal_level;
wal_level
-----------
logical
(1 row)

Eventually, if the number of logical replication slots falls to 0, the
wal_level value displayed reverts to "replica".

Since there are no *new* wal_level strings introduced how would that
break compatibility for monitoring tools?

And, since the (fudged) wal_level value "logical" already indicates
whether logical info WAL-logging is enabled or not, no additional
kinds of checking are needed.

======
Kind Regards,
Peter Smith.
Fujitsu Australia