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 ?
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?
One question - are you planning to deploy this PostgreSQL service for use by non-Juju clients? It sounds like you might, based on some of the the questions. The PostgreSQL charm isn’t really designed for that use case, it’s designed for other charms to connect to it via Juju relations, which I think may be where there’s confusion about the best way to connect to it.
If you can confirm that’ll help with the direction of future replies to this thread.
Yes actually I want to use the cluster for non-juju applications.
Here in the work place, they asked me to setup a postgresql cluster and I found juju an easy way to set it up.
