Maintain cluster-wide logical replication slots in SynchronousReplication mode

[ivlcic]

What would you like to be added: Maintaining an online logical replication slots state for SynchronousReplication so the logical replication could switch-over when standby is promoted.

Why is this needed: This is needed for logical replication external to Stolon cluster. For instance: Data warehousing solution with Stolon cluster Hot data -> Logical rep. -> PostgreSQL Cold data)

Naive proposal Some double checking must me done since I'm no sure this is possible. Maybe one solution would be creating logical repl. slot on all servers with periodically calling pg_replication_slot_advance
on standbys reading master's replication slot state (taken in consideration that SynchronousReplication must be enabled - no lag on standbys).

It might be that its impossible to make it correctly: Logical replication and physical standby failover

[sebasmannem]

Yes, your proposed solution is also what Patroni does. And what EFM does as well. If we want it in Stolon, maybe I can issue a PR for it.

[ivlcic]

That would be great. My knowledge of PostgreSQL and Stolon internals is insufficient to do it.

[Ondrysak]

Some relevant information on how Patroni is doing this

https://www.postgresql.eu/events/pgconfde2022/sessions/session/3745/slides/306/Implementing%20failover%20of%20logical%20replication%20slots%20in%20Patroni.pdf