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

On Fri, Jun 20, 2025 at 12:15 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
>
> Hi,
>
> On Fri, Jun 20, 2025 at 09:48:47AM +0530, shveta malik wrote:
> > On Thu, Jun 19, 2025 at 2:30 PM Bertrand Drouvot
> > <bertranddrouvot.pg@gmail.com> wrote:
> > >
> > > 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.
> >
> > Overall the plan sounds reasonable one.
>
> Thanks for sharing your thoughts!
>
> > But we need to think if the
> > slot is dropped on primary as part of Drop Subscription on subscriber,
> > then how will the user convey the wal-level preserve option?
>
> If the drop subscription attempts to drop the last logical replication slot
> on the primary then it will fail. The "DROP SUBSCRIPTION" doc states:
>
> "
> To proceed in this situation, first disable the subscription by executing
> ALTER SUBSCRIPTION ... DISABLE, and then disassociate it from the replication slot
> by executing ALTER SUBSCRIPTION ... SET (slot_name = NONE).
>
> After that, DROP SUBSCRIPTION will no longer attempt any actions on a remote host.
> Note that if the remote replication slot still exists, it (and any related table
> synchronization slots) should then be dropped manually; otherwise it/they will
> continue to reserve WAL and might eventually cause the disk to fill up.
> "
>
> So one option is to drop the logical replication slot manually (providing a valid
> wal_level_action value).
>
> That's not the most elegant solution but if the error message is clear enough
> (that this is the last logical replication slot and that it has to be
> removed manually) that's "doable" to reach a clean state on the publisher
> and subscriber sides.
>
> > Giving it
> > as part of subscription-cmd to preserve wal-level on primary
>
> Yeah, another option is to make "wal_level_action" part of the "DROP SUBSCRIPTION"
> command. In that case a common scenario would be:
>
> - first drop fails because the wal_level_action value has not been specified
> - then try to drop again but this time specifying a wal_level_action value
>
> > might not be a good idea.
>
> Agree that it sounds kind of weird and I'm not sure that I like the idea of
> giving the "wal level" on the primary control on the subscriber side.
>
> Without it a typical scenario would be:
>
> - drop fails
> - ALTER SUBSCRIPTION <> disable
> - drop the slot on the primary
> - ALTER SUBSCRIPTION <> SET (slot_name = NONE)
> - drop succeeds
>
> and that might not be user friendly but it gives the wal level control on the
> publisher side (and I think that's better).
>

I still feel that to switch wal_level automatically on primary, having
changes in subscription commands/steps might not be a good idea. The
acceptance of this idea could be lesser.

thanks
Shveta