Client side HA with target_session_attrs

[benbro]

With libpq it is possible to specify multiple hosts and require a read-write connection. The client will connect all hosts until it finds the primary. This is a nice way to support HA on the client without a need to put a load balancer in front of the postgres cluster.
pg_auto_failover uses this feature for HA.

We can use SELECT pg_is_in_recovery(); to check if the node is primary or not and periodically check it to see if a failover happened and we need to find a new primary. default_transaction_read_only might also be needed.

Is there a way to achieve this with pgo?

[tsloughter]

Oh neat, there isn't a way to do anything like this in pgo today. Except for creating multiple pools and then checking which is primary and having some additional indirection of "use this pool" in your application.

[tsloughter]

As for finding a new primary, does an application really need to do anything like periodically checking? I'd expect that we'd be disconnected by the current primary and forced to reconnect if the cluster is re-configuring.

[benbro]

I don't think we'll get disconnected when the primary is changed manually while the old primary is still up.