Postgres cluster

Hi ,

I want to setup a three node cluster for postgresql service with juju on lxd. I have a few questions that need to be understand on my end.

I know there is pgbouncer which is more lightweight than the postgresql service so the first question is do I have to deploy three pgbouncer for each node?

I also want know how I can load balance connections through this cluster with haproxy or any other kind, I have read that postgres does not have active-active cluster so how I can split read-write queries ?

Thank you in advance.

2 Likes

a simple up for making the post available.

In terms of load balancing connections through this cluster, the PostgreSQL relation provides connection details to the master and slaves separately, which you can use in your application to decide which queries you want to send where.

If you’re writing a charm based on the Operator Framework you can use the ops-lib-pgsql library to make implementing this easier for you. The README there shows an example of handling connections to the master and slave(s).

In terms of pgbouncer, this is entirely optional and depends on your use case as to whether you want to deploy it and how many instances of it you need. It doesn’t support a relation to HAProxy though - hopefully the information above about the PostgreSQL relation explains what you’re after in terms of handling connections to your PostgreSQL cluster.

Thank you so much but these are a little beyond my knowledge.

How about using keepalived in front of the cluster ? I want to use keepalived in case of the master goes down and route traffic to one slave but how can I find out which slave becomes master?