Connection poolers point to read-only replica after a fail-over

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.7.1

• Where do you run it - cloud or metal? Kubernetes or OpenShift? Bare Metal K8S

• Are you running Postgres Operator in production? Yes

• Type of issue? Bug report

We are encountering an issue where the connection poolers continue pointing to the old master Postgres replica after a fail-over. We think this is the sequence of events:

1. postgres-2 was the leader
2. postgres-2 lost leader lock and started to demote itself
3. postgres-pooler lost connection to postgres-2 and tried to reconnect
4. postgres-0 got leader lock and started to promote itself
5. postgres-2 finished restarting before postgres-0
6. postgres-pooler reconnected to postgres-2
7. postgres-0 finished promoting
8. patroni updated K8S service IP to point to the endpoint of postgres-0, but it was too late since connection-pooler had already finish reconnecting
9. postgres-pooler were now stucked with the read-only postgres-2 until server_lifetime elapsed

Does this make sense ?
Should postgres-operator issue a RECONNECT to command to all pooler after the service endpoint is update ?

Start by reproducing the reported failover sequence involving postgres-pooler, Patroni, and the Kubernetes service endpoint. Check whether poolers can reconnect to the newly promoted postgres-0 instead of remaining on read-only postgres-2 until server_lifetime expires; done means poolers consistently target the writable leader after failover.