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

Hi,

On Tue, Jun 24, 2025 at 12:13:32AM +0900, Masahiko Sawada wrote:
> 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).

Yeah, I think that sounds reasonable and that would avoid users to use
the slot created with immediately_reserve set to false by mistake.

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

Right...So not sure we need such a GUC. What about always behave with the
automatic behavior?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com