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

On Sun, Jul 6, 2025 at 8:33 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Thu, Jul 3, 2025 at 3:32 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Wed, Jul 2, 2025 at 9:46 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > On Wed, Jun 18, 2025 at 1:07 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > > >
> > > > On Wed, Jun 18, 2025 at 6:06 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)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?
>
> Even when the standby's wal_level is 'logical', the logical decoding
> cannot be used on the standby if the primary doesn't enable it. IOW,
> even if the standby's wal_level is 'replica', the logical decoding is
> available on the standby if the primary enables it.
>
> >
> > > 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?
>
> The criteria is whether the primary enables the logical decoding or
> not. If the primary enables the logical decoding by either setting
> wal_level='logical' or creating a logical slot, the
> effective_wal_level on the standby will be displayed as 'logical'.
>

Okay, I understand it now. Thanks for explaining. The proposed
behaviour looks reasonable.

thanks
Shveta