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

Hi,

On Wed, Jun 18, 2025 at 03:22:59PM +0530, shveta malik wrote:
> On Wed, Jun 18, 2025 at 2:39 PM Bertrand Drouvot
> <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> >
> > 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).
> >
>
> 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).

I wonder if a way to address the concerns that we shared above is to use a
mixed approach like:

- Forget the immediately_reserve idea
- If a user creates a logical slot then we automatically switch to wal_level =
logical (if not already done): I think that's a nice user experience
- *and* provide a new API pg_activate_logical_decoding(), if the user has no
need to create a logical slot on the primary (wants to use the standby to offload
all the logical decoding)

So if the user also uses a logical slot on the primary (for real..) then there
is no need to launch pg_activate_logical_decoding(), until....:

The user decides to drop the logical slot on the primary, and then:

- If the slot is not the last logical slot, that's fine, drop it
- If the slot is the last logical one AND the user did not set a new flag
"wal_level_action" to "say preserve" or "force downgrade" (in the drop command)
then the drop fails with an informative error message.

That way:

- pg_activate_logical_decoding() is needed only if there is not already a logical
slot on the primary
- the drop requires the user to think twice if this is the last logical slot
- we don't ask the user to create a logical slot if he does not want to use it
on the primary

Thoughts?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com