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

On Mon, Jun 23, 2025 at 7:01 PM Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Mon, Jun 23, 2025 at 05:10:37PM +0900, Masahiko Sawada wrote:
> > On Thu, Jun 19, 2025 at 6:00 PM Bertrand Drouvot
> > <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> > >
> > > - pg_activate_logical_decoding() is needed only if there is not already a logical
> > > slot on the primary
> > > - the drop requires the user to think twice if this is the last logical slot
> > > - we don't ask the user to create a logical slot if he does not want to use it
> > > on the primary
> > >
> > > Thoughts?
> >
> > If there is no logical slot on the primary, how can the user disable
> > logical decoding that has been enabled via
> > pg_activate_logical_decoding()?
>
> I was thinking to keep the pg_deactivate_logical_decoding() API proposed
> in this thread.

Okay. One approach that combines your idea and Shveta's idea is:

- a special (empty) logical slot with the reserved slot name can be
created and deleted only by SQL functions,
pg_activate_logical_decoding() and pg_deactivate_logical_decoding().
- this special slot cannot be used by logical decoding.
- effective_wal_level is increased and decreased when creating and
dropping a slot (i.e., either a normal logical slots or the special
logical slot).

That way, users who enabled the logical decoding via
pg_activate_logical_decoding() can keep the effective_wal_level being
'logical' since the special slot remains unless executes
pg_deactivate_logical_decoding(). And users who don't use these new
APIs can enable and disable the logical decoding at normal logical
slot creation and deletion.

>
> > Given the discussion so far, it seems we might want to have a
> > safeguard to prevent the effective_wal_level from being dropped to
> > 'replica' if the last logical slot is accidentally dropped. Another
> > idea we can consider is that we automatically increase
> > effective_wal_level to 'logical' upon the logical slot creation but
> > don't automatically decrease it when dropping the last slot. To
> > decrease the effective_wal_level to 'replica', users would need to do
> > that explicitly for example using a SQL function,
> > pg_disable_logical_decoding().
>
> Yeah that could be an idea (and then we don't add the new wal_level_action
> to the drop slot command).
>
> > We might want to have a GUC parameter
> > for users to turn on/off this automatic behavior.
>
> You mean a GUC to both automaticly set effective_wal_level to logical at slot creation
> and also decrease effective_wal_level to replica if last replication slot is dropped?

What I imagined was to control only the decreasing behavior that could
be more problematic than the increase case. But it might be rather
confusing (e.g., what if we turn off that behavior and restart the
server?).

Regards,

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