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

On Thu, Jan 8, 2026 at 5:17 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Jan 7, 2026 at 4:56 AM Matthias van de Meent
> <boekewurm+postgres@gmail.com> wrote:
> >
> > On Fri, 19 Dec 2025 at 08:51, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > > On Thu, Dec 18, 2025 at 9:14 PM Peter Smith <smithpb2250@gmail.com> wrote:
> > > >
> > > > I checked the v35/v36 patch diffs, and I also have no further review comments.
> > > >
> > >
> > > Thank you for reviewing the patch!
> > >
> > > I'm going to push it early next week if there are no major comments.
> > >
> > > Regards,
> >
> > Hi,
> >
> > Sorry for the belated reply. I noticed this patch got committed, and
> > after reading its commit message (and now, code) I'm concerned that
> > I'm now unable to disable wal_level=logical without removing streaming
> > replication as feature.
> > When I configure wal_level=replica, to me that means to NOT enable
> > wal_level=logical, and that means that I do *not* want the increased
> > overhead in my cluster's table updates that is associated with
> > wal_level=logical (but still want to be able to have streaming
> > replication).
> >
> > I had expected the topical feature to be implemented through changing
> > wal_level to PGC_SIGHUP from PGC_POSTMASTER (and then propagating that
> > through a similar system), which would've required an explicit
> > agreement of the cluster owner to increase the WAL overhead in favour
> > of being able to do logical decoding. However, by making
> > effective_wal_level controlled by CREATE_REPLICATION_SLOT, this guc is
> > suddenly effectively set-able by users with the REPLICATION privilege,
> > which it previously wasn't. And I don't trust my physical subscribers'
> > roles to _not_ also create a logical replication slot.
> >
> > So, sorry I'm late, but I don't agree with the way this decides to
> > change the effective wal level. It elevates REPLICATION users to be
> > able to control wal_level without actually going through the security
> > controls of the system. And no, granting SET ON PARAMETER wal_level
> > for REPLICATION roles isn't a solution IMO - replication roles
> > shouldn't decide which types of replication are allowed in the
> > cluster, only the system owner (and its explicit delegates) should.
> >
> > NB. I'm not opposed to changing wal_level in a running cluster, and I
> > do think that the current xact+checkpoint -based approach to selecting
> > the local effective_wal_level is fine, as well as standby picking up
> > the primary's current setting; it's the trigger condition for the
> > decision to change effective_wal_level that I have problems with.
> >
>
> Thank you for the comments.
>
> I understand the concern that users with the REPLICATION privilege can
> now effectively control wal_level, potentially increasing system-wide
> overhead. While the REPLICATION privilege already implies a high
> degree of trust as we allow it to take a basebackup and create a
> physical slot etc., I agree that this feature might elevate that power
> further, and we may need a mechanism to address this.
>

The feature can be seen as a way for a non-superuser override the
decision of superuser who has no way to control it. I think it weakens
superuser control.

> We considered making wal_level a SIGHUP parameter but decided against
> it. If we only support transitions between replica and logical,
> wal_level would become a "partial" SIGHUP parameter, introducing
> complexity and potential confusion.

The latest buffer resizing patches [1] makes shared_buffers a SIGHUP
variable but requires a SQL callable function to be used to actually
apply the new size. I think we need a new level SIGHUPPLUS
(suggestions about name are welcome :), PGC_DYNAMIC maybe), which
allows a the new value of GUC to be loaded but require another step to
apply the change in the cluster. wal_level can be another candidate
for that kind of GUC. As we discussed in pgconf.dev, I think it will
be to maintain consistency across GUCs.

> Conversely, supporting all
> transitions (including to/from minimal) would add significant
> complexity, even though such changes are rare. Furthermore, I believe
> that automatically toggling logical decoding availability provides a
> better user experience than requiring users to manually change the
> configuration and reload.
>

The second step can be used to throw an error in the cases where
transition is not possible.

> To address your concerns, I have come up with the following ideas:
>
> 1. Introduce a new GUC (e.g., allow_dynamic_wal_level) to control
> whether the dynamic adjustment of effective_wal_level is allowed
> system-wide. This parameter would be a SIGHUP parameter and require
> superuser privileges to change.
>
> 2. Redefine the behavior of wal_level='logical'. Under this approach,
> wal_level='replica' would strictly prevent dynamic changes. If
> wal_level='logical' is set, the effective_wal_level would start at
> replica and dynamically switch to logical only when there is at least
> one logical slot. The downside is that users who explicitly want
> logical decoding would still incur the overhead of the toggle
> mechanism rather than having it enabled from the start.
>
> I am open to other ideas and opinions, and your feedback is very welcome.
>

If the above idea isn't acceptable here's 3rd option
3. Introduce dynamic_replica as an option between replica and logical.
When wal_level = dynamic_replica (name subject to change), the cluster
toggles between replica and logical based on the existence of
replication slots. replica and logical levels maintain their meaning
as of today. Users using 'replica' or 'logical' today change their
wal_level to dynamic_replica in the PG 19 cluster.

[1] https://www.postgresql.org/message-id/CAExHW5sVxEwQsuzkgjjJQP9-XVe0H2njEVw1HxeYFdT7u7J%2BeQ%40mail.gmail.com
--
Best Wishes,
Ashutosh Bapat