Forward traffic to a redundant container if master fails in Kubernetes

Due to the fact that I don't have enough information about the architecture used and it is based only on a very descriptive example - I am uploading the information obtained from research allowing community members to complete/extend this answer.

Based on this documentation PgBouncer does not have an internal multi-host configuration. To load-balance queries between several server one can use external tools such as:

1. DNS round-robin. Use several IPs behind one DNS name. PgBouncer does not look up DNS each time a new connection is launched. Instead, it caches all IPs and does round-robin internally. Note: if there are more than 8 IPs behind one name, the DNS backend must support the EDNS0 protocol. See README for details.

2. Use a TCP connection load-balancer. Either LVS or HAProxy seem to be good choices. On the PgBouncer side it may be a good idea to make server_lifetime smaller and also turn server_round_robin on: by default, idle connections are reused by a LIFO algorithm, which may work not so well when load-balancing is needed.

This blog explains why to use another loadbalancer mechanism with pgBouncer. According to that one you shouldn't use pgBouncer instead of HAProxy or some other load balancer. It is obvious that pgBouncer has several configurable features addressing what a load balancer addresses - but this is most common that prod environments use HAProxy or some other load balancer for HA. The reason for this is that HAProxy better balances workloads across live servers than pgbouncer.

Although pgbouncer is better for postgres connection pooling, it might be better to use one small daemon that perfectly performs one task, instead of a bigger one that does two tasks, but worse.

It is also good idea to use PgPool. See also this answer.

Here are also article that compares PgBouncer and PgPool. Small part of that: | | PGBOUNCER | PGPOOL-II | |--|--|--| | High availability |Not supported. |PostgreSQL high availability is supported through Pgpool-II in-built watcher processes. Winner! | |Load balancing|Not supported - PgBouncer recommends use of HAProxy for high availability and load balancing.| Supports automatic load balancing - is even intelligent enough to redirect read requests to standbys, and writes to masters. Winner! |