"Streaming Replication + External Logical Replication with Replication Slot Failover

[bryambalan]

Hello everyone!

I’m starting to use the Autobase project to set up a 5-node cluster, and so far, everything has worked really well in my scenario. However, I have some additional requirements and would like to know if I’m on the right track:

Scenario

In addition to Streaming Replication in the cluster, I need to set up Logical Replication for databases outside the cluster. I understand I can manually configure this by creating the Publication on the cluster’s Master node and the Subscription on the external database. However, I have a few questions:

Questions

1. Is it possible to configure Logical Replication via Ansible using the Autobase project? In other words, is there any functionality or variable in the project that can automatically set up Publications and Subscriptions
2. With Logical Replication, I noticed a problem in the event of a failover. If the Master node fails and another node takes over, the Subscription would still be pointing to the old Master’s IP, which would break replication.

I researched and saw that PostgreSQL 17 introduced Replication Slot Failover. I also noticed that the Autobase project mentions the pg_failover_slots extension. My question is:

• Does the pg_failover_slots extension provide the same functionality as the native Replication Slot Failover in PostgreSQL 17?
• With pg_failover_slots, would it be possible to ensure that, after a failover in the cluster, the Subscription continues to work by automatically redirecting to the new Master?

Final Goal

My goal is to ensure that, even after a failover, the logical replication continues without manual intervention, automatically redirecting the Subscriptions to the new Master in the cluster.

I would really appreciate any guidance on whether I’m on the right track or if there’s anything else I should consider!

Thanks in advance for your attention and any help!

[vitabaks]

Hi @bryambalan

1. Currently, Autobase does not support automated logical replication setup. Such use cases are quite rare. If this feature is important to you, please consider submitting a feature request via this link: Submit Feature Request.
2. In the meantime, I recommend using the cluster’s VIP address and defining the logical slot name in the patroni_slot variable. These slots will be preserved during switchover or failover.

If you’d like personalized assistance, consider subscribing to Autobase support services, and we’ll gladly help you with the setup and optimization.

Best regards,
Viitaliy
Autobase Team