search

erleans / pgo

Erlang Postgres client and connection pool
Apache License 2.0
79 stars 16 forks source link

Client side HA with target_session_attrs #72

Open benbro opened 1 year ago

benbro commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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