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

On Thu, May 8, 2025 at 1:06 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, May 6, 2025 at 11:59 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Thu, Apr 24, 2025 at 11:14 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > > On Thu, Apr 24, 2025 at 5:30 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > > On Wed, Apr 23, 2025 at 9:35 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > > > >
> > > > > On Wed, Apr 23, 2025 at 5:46 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > > > >
> > > > > > BTW, did we consider the idea to automatically transition to 'logical'
> > > > > > when the first logical slot is created and transition back to
> > > > > > 'replica' when last logical slot gets dropped? I see some ideas around
> > > > > > this last time we discussed this topic.
> > > > >
> > > > > Yes. Bertrand pointed out that a drawback is that the primary server
> > > > > needs to create a logical slot in order to execute logical decoding on
> > > > > the standbys[1].
> > > > >
> > > >
> > > > True, but if we want to avoid that, we can still keep 'logical' as
> > > > wal_level for the ease of users.
> > >
> > > I think we'd like to cover the use case like where users start with
> > > 'replica' on the primary and execute logical decoding on the standby
> > > without neither creating a logical slot on the primary nor restarting
> > > the primary.
> > >
> >
> > Okay, if we introduce a SIGHUP GUC like max_wal_level as you are
> > proposing, the above requirement will be fulfilled, right?
>
> Right. Both the primary and the standby can increase WAL level to
> 'logical' without server restart nor creating a logical slot.
>
> > The other
> > way is by API pg_activate_logical_decoding().
>
> Yes. This approach would be simpler than the current proposal as we
> don't need other new infrastructure such as executing a task in the
> background.
>

Right, but to an extent, this is also similar to having a requirement
of a logical slot on the primary. Now, it seems to me that the point
you are trying to make is that to allow logical decoding on standby,
it is okay to ask users to use pg_activate_logical_decoding() on
primary, but it would be inconvenient to ask them to have a logical
slot on primary instead. If my understanding is correct, then why do
you think so? We recommend that users have a physical slot on primary
and use it via primary_slot_name on standby to control resource
removal, so why can't we ask them to have a logical slot on primary to
allow logical decoding on standby?


> However, we might want to note that wal_level value would
> no longer show the actual runtime WAL level if the logical decoding is
> activated via this API. Probably it's better to introduce a read-only
> GUC, say runtime_wal_level, showing the actual WAL level.
>

Yeah, we need some way to show the correct value. In one of the
previous emails on this thread, you mentioned that we can use
show_hook to show the correct value. I see that show_in_hot_standby()
uses in_memory value to show the correct value. Do you have something
like that in your mind?

BTW, what is your idea to preserve the state to allow logical decoding
across server restart when the user uses the API, do we want to
persist the state in some way, if so, how? OTOH, if we use the idea to
have a logical slot to allow decoding, then the presence of a logical
slot can tell us whether we need to enable the new state to allow
logical decoding after restart.

> Also,
> Ashutosh pointed out[1] before that cloud providers do not like
> multiple ways of changing configuration esp. when they can not control
> it. But I'm not sure this applies to the API as it's a SQL function
> whose access privilege can be controlled.
>

By multiple ways, do we mean to say that one way for users would be to
use the existing way (change wal_level to logical and restart server),
and the other way would be to use the new API (or have a logical
slot)? But won't similarly users have multiple ways to retain WAL for
standby servers (either by using wal_keep_size or by having a
primary_slot_name). The other example is that one can either manually
change postgresql.conf file or use ALTER SYSTEM to change it, and then
reloadthe  config or restart the server for the change to take effect.
There could be other similar examples as well if one tries to list all
such possibilities. I feel one should be concerned if we are trying to
make both wal_level GUC as SIGHUP, and also try to provide an API to
enable logical decoding.

> > >
> >
> > That makes sense. If we are using an API like
> > pg_activate_*/pg_deactivate_*, then why add an additional dependency
> > on the slots?
>
> I thought that we need to remember how logical decoding got enabled
> because otherwise even if we enable logical decoding using the API,
> it's disabled to 'replica' if all logical slots get removed. So the
> idea I mentioned above is that we somehow prevent logical decoding
> from being disabled even if all logical slots are removed. If we're
> using only these APIs to enable/disable logical decoding, we don't
> need to add a dependency on the slots, although we probably want to
> disallow disabling logical decoding if there is at least one active
> logical slot.
>

Yeah, this is a detail that should be discussed once we finalize the
API to enable logical decoding on both primary and standby without
restarting the primary server.

--
With Regards,
Amit Kapila.