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

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.

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' because the standby cannot write
any WAL and need to follow the primary. 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'.

Regards,

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