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

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).

Regards,

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