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

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. 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. 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.

>
> > > We can also have another API like the
> > > one you originally proposed (pg_activate_logical_decoding) for the
> > > ease of users. But the minimum requirement would be that one creates a
> > > logical slot to enable logical decoding/replication.
> >
> > I think we want to avoid the runtime WAL level automatically decreased
> > to 'replica' once all logical slots are removed, if users still want
> > to execute logical decoding on only the standby. One idea is that if
> > users enable logical decoding using pg_activate_logical_decoding(),
> > the runtime WAL level doesn't decrease to 'replica' even if all
> > logical slots are removed.
> >
>
> 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.

Regards,

[1] https://www.postgresql.org/message-id/CAExHW5tyJrdjqKFQ%2BqDs8Yq3E_P1Fj_T4pwVW9WACmMznRtDuw%40mail.gmail.com

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com