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

On Wed, Jul 2, 2025 at 9:46 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jun 18, 2025 at 1:07 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Wed, Jun 18, 2025 at 6:06 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > > Thank you for the comments!
> > >
> > > >
> > > > 2)
> > > > I see that when primary switches back its effective wal_level to
> > > > replica while standby has wal_level=logical in conf file, then standby
> > > > has this status:
> > > >
> > > > postgres=# show wal_level;
> > > >  wal_level
> > > > -----------
> > > >  logical
> > > >
> > > > postgres=# show effective_wal_level;
> > > >  effective_wal_level
> > > > ---------------------
> > > >  replica
> > > >
> > > > Is this correct? Can effective_wal_level be < wal_level anytime? I
> > > > feel it can be greater but never lesser.
> > >
> > > Hmm, I think we need to define what value we should show in
> > > effective_wal_level on standbys because the standbys actually are not
> > > writing any WALs and whether or not the logical decoding is enabled on
> > > the standbys depends on the primary.
> > >
> > > In the previous version patch, the standby's effective_wal_level value
> > > depended solely on the standby's wal_level value. However, it was
> > > confusing in a sense because it's possible that the logical decoding
> > > could be available even though effective_wal_level is 'replica' if the
> > > primary already enables it. One idea is that given that the logical
> > > decoding availability and effective_wal_level value are independent in
> > > principle, it's better to provide a SQL function to get the logical
> > > decoding status so that users can check the logical decoding
> > > availability without checking effective_wal_level. With that function,
> > > it might make sense to revert back the behavior to the previous one.
> > > That is, on the primary the effective_wal_level value is always
> > > greater than or equal to wal_level whereas on the standbys it's always
> > > the same as wal_level, and users would be able to check the logical
> > > decoding availability using the SQL function. Or it might also be
> > > worth considering to show effective_wal_level as NULL on standbys.
> >
> > Yes, that is one idea. It will resolve the confusion.
> > But I was thinking, instead of having one new GUC + a SQL function,
> > can we have a GUC alone, which shows logical_decoding status plus the
> > cause of that. The new GUC will be applicable on both primary and
> > standby. As an example, let's say we name it as
> > logical_decoding_status, then it can have these values (
> > <status>_<cause>):
> >
> > enabled_wal_level_logical:                                  valid both
> > for primary, standby
> > enabled_effective_wal_level_logical:                   valid only for primary
> > enabled_cascaded_logical_decoding                   valid only for standby
> > disabled :
> >   valid both for primary, standby
> >
> > 'enabled_cascaded_logical_decoding'  will indicate that logical
> > decoding is enabled on standby (even when its own wal_level=replica)
> > as a cascaded effect from primary. It can be possible either due to
> > primary's wal_level=logical or logical slot being present on primary.
>
> I'm not sure it's a good idea to combine two values into one GUC
> because the tools would have to parse the string in order to know when
> they want to know either information.

Okay. Agreed.

> As for the effective_wal_level shown on the standby, if it shows the
> effective WAL level it might make sense to show as 'replica' even if
> the standby's wal_level is 'logical'

Alright. It depends on the definition we choose to assign to
effective_wal_level.

> because the standby cannot write
> any WAL and need to follow the primary.

When the standby’s wal_level is set to 'logical', the requirement for
logical decoding is already fulfilled. Or do you mean that the
effective_wal_level on standby should not be shown as logical until
both the primary and standby have wal_level set to logical and we also
have a logical slot present on standby?

> While it might be worth
> considering to accept the case of effective_wal_level (replica) <
> wal_level (logical) only on the standbys, we need to keep the
> principle that the logical decoding is available only when
> effective_wal_level = 'logical'.
>

Back to the previous question, when will the effective_wal_level be
displayed as 'logical' on standby? Which criterias need to be met?

thanks
Shveta