Currently, I am using pgbouncer for connection pooling in the postgresql cluster. I just want to make sure, Whether it is possible to load balance request between the nodes in the postgresql cluster using pgbouncer.
Asked
Active
Viewed 4,780 times
2 Answers
3
Now there's pgbouncer-rr-patch(pgbouncer fork by AWS) that can do load balancing:
Routing: intelligently send queries to different database servers from one client connection; use it to partition or load balance across multiple servers/clusters.
Steve Chavez
- 931
- 10
- 13
2
From the PgBouncer FAQ
How to load-balance queries between several servers?
PgBouncer does not have internal multi-host configuration. It is possible via some external tools:
DNS round-robin. Use several IPs behind one DNS name. PgBouncer does not look up DNS each time new connection is launched. Instead it caches all IPs and does round-robin internally. Note: if there is more than 8 IPs behind one name, the DNS backend must support EDNS0 protocol. See README for details.
Use a TCP connection load-balancer. Either LVS or HAProxy seem to be good choices. On PgBouncer side it may be good idea to make server_lifetime smaller and also turn server_round_robin on - by default idle connections are reused by LIFO algorithm which may work not so well when load-balancing is needed
