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

On Mon, May 19, 2025 at 2:05 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Sun, May 18, 2025 at 1:09 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Sat, May 10, 2025 at 7:08 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > >
> > > Can we have a parameter like immediately_reserve in
> > > create_logical_slot API, similar to what we have for physical slots?
> > > We need to work out the details, but that should address the kind of
> > > use case you are worried about, unless I am missing something.
> >
> > Interesting idea. One concern in my mind is that in the use case I
> > mentioned above, users would need to carefully manage the extra
> > logical slot to keep the logical decoding active. The logical decoding
> > is deactivated on the standby as soon as users drop all logical slots
> > on the primary.
> >
>
> Yes, but the same is true for a physical slot in the case of physical
> replication used via primary_slot_name parameter.

Could you elaborate on this? IIUC the purpose of using a physical slot
in a physical replication case is obvious; users don't want to lose
WAL files necessary for replication. On the other hand, this empty
logical slot needs to be maintained just for keeping the logical
decoding active.

>
> > Also, with this idea of automatically increasing WAL level, do we want
> > to keep the 'logical' WAL level? If so, it requires an extra step of
> > creating a non-reserved logical slot on the primary in order for the
> > standby to activate the logical decoding. On the other hand, we can
> > also keep the 'logical' WAL level for the compatibility and for making
> > the logical decoding enabled without the coordination of WAL level
> > transition.
>
> Right, I also feel we should retain both ways to enable logical
> replication at least initially. Once we get some feedback, we may
> think of removing 'logical' as wal_level.
>
> > But wal_level GUC parameter would no longer tell the
> > actual WAL level to users when 'replica' + logical slots.
> >
>
> Right.
>
> > Is it
> > sufficient to provide a read-only GUC parameter, say
> > effective_wal_level showing the actual WAL level being used?
> >
>
> I am not so sure about how we want to communicate this to the user,
> but I guess to start with, this is a good idea.

I recently had a discussion with Ashtosh at PGConf.dev regarding an
alternative approach: introducing a new command syntax such as "ALTER
SYSTEM UPDATE wal_level TO 'logical'". In his presentation[1], he
outlined this proposed command as a means to modify specific GUC
parameters synchronously. The backend executing this command would
manage the transition, allowing users to interrupt the process via
Ctrl-C if necessary. In the specific context of wal_level change, this
command could be designed to reject operations like "ALTER SYSTEM
UPDATE wal_level TO 'minimal'" with an error, effectively preventing
undesirable wal_level transitions to or from 'minimal'. While this
approach shares similarities with our previous proposal of
implementing a dedicated SQL function for WAL level modifications, it
offers a more standardized interface for users.

Though I find merit in this proposal, I remain uncertain about its
implementation details and whether it represents the optimal solution
for online wal_level changes, particularly given that our current
approach of automatic WAL level adjustment appears viable. Ashtosh
plans to initiate a separate discussion thread where we can explore
these considerations in greater detail.

Regards,

[1] https://www.pgevents.ca/events/pgconfdev2025/schedule/session/286-changing-shared_buffers-on-the-fly/

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