`promote-to-primary` action can break async-replication if primary unit on secondary cluster takes over sync role first

[ggouzi]

On a dual-cluster setup with asynchronous replication (2+2), after promote-to-primary scope=unit, the secondary is stuck in Replica and doesn't go to Sync Standby.

Summary of the setup:

Member	IP address	Site	Patroni Status
postgresql-0	10.10.128.24	Primary	Sync Standby (primary cluster)
postgresql-1	10.10.128.23	Primary	Leader (primary cluster)
postgresql-0	10.10.118.24	Secondary	Standby Leader (secondary cluster)
postgresql-1	10.10.118.23	Secondary	Replica (secondary cluster)

Steps to reproduce

1. On primary cluster, perform a promote-to-primary action to switch secondary to primary unit

juju run postgresql/0 -- promote-to-primary scope=unit

2. Observe patronictl output

Expected behavior

sudo patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml list
+ Cluster: postgresql (7633847608209757521) -+-----------+----+-----------+
| Member       | Host         | Role         | State     | TL | Lag in MB |
+--------------+--------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.52.128.24 | Leader       | running   |  4 |           |
| postgresql-1 | 10.52.128.23 | Sync Standby | streaming |  4 |         0 |
+--------------+--------------+--------------+-----------+----+-----------+

Actual behavior

sudo patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml list
+ Cluster: postgresql (7633847608209757521) --------+----+-----------+
| Member       | Host         | Role    | State     | TL | Lag in MB |
+--------------+--------------+---------+-----------+----+-----------+
| postgresql-0 | 10.52.128.24 | Leader  | running   |  4 |           |
| postgresql-1 | 10.52.128.23 | Replica | streaming |  4 |         0 |
+--------------+--------------+---------+-----------+----+-----------+

Versions

Operating system: Ubuntu 24.04
Juju CLI: 3.6.14
Juju agent: 3.6.14
Charm revision: 1047
LXD: 5.21/stable

Log output

Juju debug log:

Additional context

When the promote happens, we now have 2 nodes postgresql-0 the pg_stat_replication table. First one in sync is the primary unit of the secondary cluster. Second one is the replica unit of the main cluster (the one we aimed to failover with the promote-to-primary action)

postgres=# SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;
 application_name | client_addr  |   state   | sync_state 
------------------+--------------+-----------+------------
 postgresql-0     | 10.10.118.24 | streaming | sync
 postgresql-0     | 10.10.128.24 | streaming | potential

Workaround:

• Adjust synchronous_node_count from 1 to 2. This cannot be increased to more than 1 using juju config.

sudo charmed-postgresql.patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml edit-config

• Restart patroni on the main unit of the secondary cluster (10.10.118.24)

sudo snap start charmed-postgresql.patroni

Now the correct unit (10.10.128.24, new Leader) took over the sync state

postgres=# SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;
 application_name | client_addr  |   state   | sync_state 
------------------+--------------+-----------+------------
 postgresql-0     | 10.10.128.24 | streaming | sync
 postgresql-1     | 10.10.118.23 | streaming | async
(2 rows)

And patronictl returns proper config

sudo patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml list
+ Cluster: postgresql (7633847608209757521) -+-----------+----+-----------+
| Member       | Host         | Role         | State     | TL | Lag in MB |
+--------------+--------------+--------------+-----------+----+-----------+
| postgresql-0 | 10.10.128.24 | Leader       | running   |  4 |           |
| postgresql-1 | 10.10.128.23 | Sync Standby | streaming |  4 |         0 |
+--------------+--------------+--------------+-----------+----+-----------+

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-9979.

This message was autogenerated

[taurus-forever]

We should update our documentation.

TL;DR: Primary cluster and Strandby cluster names must be unique:
https://patroni.readthedocs.io/en/rel_3_3/standby_cluster.html

This ambiguity can cause PostgreSQL to satisfy the synchronous replication requirement
using the standby cluster’s connection instead of the intended primary cluster member,
leading PostgreSQL to prematurely acknowledge transactions as synchronously
committed when they are not durable on the correct standby.

This is a silent failure — replication continues and no errors are logged,
but the cluster is effectively operating without a valid synchronous standby,
which is a potential data loss scenario if the primary fails.

Exactly the case reported here. I will keep it open to update docs (and add a protection in charm: block if apps have the same name on relation creation), but please deploy Charmed PostgreSQL app using different Juju app names in Primary and Secondary sites. Does it work for you? @ggouzi

[mastier]

Thanks @taurus-forever . @ggouzi is not available at the moment.

That means I will need to update the deployment docs and configuration on our side then, to have different postgresql app names on two juju models.

[mastier]

@taurus-forever What if you use not only unit name (derived from application) but also model in relation databag for configuting patroni. Will that implementation allow us to skip the need of redeployment if we have juju models named differently ?

[taurus-forever]

Hi @mastier , expanding the Patroni scope from the current app-name to something more unique, like app-name+model+controller... or just use juju app UUID... is definitely an option, but 1) still missing even in edge 2) will require charm re-deployment... we are focusing on stereo mode + watcher changes. Once completed, we would return here. 🙏

[mastier]

@taurus-forever understood, we will act accordinly. Thank you!