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

On Wed, Jan 22, 2025 at 11:42 PM Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Wed, Jan 22, 2025 at 04:46:00PM -0800, Masahiko Sawada wrote:
> > I would like to summarize the proposed approaches thus far:
>
> Thanks!
>
> > Regarding the user interface, there are three approaches:
> >
> > 1. Implementing SQL function controls (e.g.,
> > pg_activate_logical_decoding() and pg_deactivate_logical_decoding()).
> > This would enable users to activate logical decoding even with
> > wal_level=replica by calling the SQL function. While cloud providers
> > seem not like having multiple configuration methods, this could
> > potentially be managed through appropriate EXECUTE privileges. Another
> > drawback is the user confusion when 'SHOW wal_level' displays
> > 'replica' despite processes writing WAL records with logical
> > information. This might be dealt with by implementing a show_hook
> > function for wal_level.
> >
> > 2. Implementing automatic logical decoding activation. This would
> > trigger upon creation of the first logical slot and deactivate upon
> > removal of the final slot. This approach shares the user confusion
> > concern of the first proposal. Moreover, it presents a significant
> > limitation: users would be unable to utilize logical decoding on
> > standby servers without maintaining at least one logical slot on the
> > primary -- a substantial disadvantage.
>
> Yeah, unless we keep wal_level around but I agree that the following (3.) looks
> like the way to go (as it removes any confusion).
>
> > 3. Converting wal_level to a SIGHUP parameter, thereby supporting all
> > possible wal_level transition combinations. While this represents the
> > most elegant solution among the proposals,
>
> +1
>
> > it necessitates additional
> > development effort for less common scenarios, such as transitioning
> > between 'minimal' and 'replica' levels. Such transitions require
> > specific handling -- for instance, changing between 'minimal' and
> > 'replica' requires a checkpoint, while decreasing from 'replica' to
> > 'minimal' necessitates terminating certain processes like WAL senders
> > and archiver.
>
> Yeah. OTOH switching from replica to minimal is "dangerous" as it makes
> previous base backups unusable for point-in-time recovery. So I wonder if it
> wouldn't be better to keep a restart mandatory depending of the transition
> state (that would probably make users thinking "twice" before doing the
> transition that requires a restart). I don't think any GUC does that already but
> that might be something to explore, thoughts?

I'm concerned that such inconsistency could introduce confusion for
users. The outcome by changing wal_level to minimal is already
documented, and I guess users would check what parameters are going to
be changed even before reloading the config file. I'm not sure that
requiring a server restart only for lowering wal_level to 'minimal'
would really be a protection for unexpectedly making previous base
backups unusable.

It might make sense to raise a WARNING when ALTER SYSTEM lowers the
wal_level to 'minimal'.

Regards,

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