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

On Wed, Jun 18, 2025 at 2:39 PM Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Tue, Jun 10, 2025 at 02:00:55PM -0700, Masahiko Sawada wrote:
> > > > > > > 0001 patch allows us to create a logical slot without WAL reservation.
>
> Thanks for the patch and sorry to be late in this conversation.
>
> The thing that worry me a bit with this is that that could be easy to attempt
> to use the slot "by mistake" and then (as a consequence) trigger WAL reservation
> by mistake on the primary. I think that this mistake is more likely to happen
> with a logical slot as compared to a physical slot.
>

Yes, agreed. Another concern is the possibility of someone
intentionally using it and associating it with a subscription. If the
subscription is later dropped, it could also cause the slot to be
removed. In cases where this is the only slot on the primary, it may
render the standby slots unusable as well. Refer the problem described
in [1]

> IIUC the idea is to "just" increase WAL level to 'logical' so that one could then
> be allowed to make use of logical decoding from the standby. The primary goal
> of logical decoding from standby is to move some load from the primay to
> the standby i.e we don't expect/want the logical slot to be used on the primary.
>
> So what about making sure that if a logical slot is created with immediately_reserve
> set to false then no one can use it? (That would ensure that WAL reservation
> will not happen).
>

+1.
One approach is to reserve a specific slot name for this purpose. If
such a slot is created, we can internally set immediately_reserve to
false and also prevent it from being used. The only permitted user
action on this slot would be to drop it using
pg_drop_replication_slot() instead of being dropped as part of
subscription-drop.
Another concern is ensuring that users actually create this slot. If
there is already an active subscription subscribed to the primary, the
effective_wal_level will be set to logical already, allowing logical
decoding on the standby to proceed without issue. In such a case, the
user might not bother to create additional slots (same as problem
described in [1])) and later may unintentionally end up making standby
slots unusable. Any ideas on how to ensure it?

> That said, we might also want to create another parameter name (than
> immediately_reserve) to better reflect this behavior (if we move that way).
>
> Thoughts?

Or we could avoid exposing control of immediately_reserve to the user
altogether? Instead, we reserve a specific slot name and ensure that
it never reserves WAL in the future by preventing it from being
consumed under any circumstances (as you suggested).

[1]: https://www.postgresql.org/message-id/CAJpy0uDW6BpNXLZ0AaP%3D_GU6pCsZf_7Sk2R0Ti%2Bov%2BEO6ruMkg%40mail.gmail.com

thanks
Shveta