Failover Manager with PgBouncer

You can use Failover Manager and PgBouncer to provide high availabilityin an on-premises setup as well as in a cloud setup. PgBouncer is a popularconnection pooler, but it is not enough to achieve PostgreSQL highavailability by itself as it doesn’t have multi-host configuration,failover, or detection.

Failover Manager with PgBouncer on premises

For an on-premises setup, use the connection libraries to provide highavailability by using a connection string with multiple hosts.

Figure 3: Failover Manager’s traffic routing using PgBouncer on-premises

Failover Manager with PgBouncer in the cloud

For a cloud setup, use a network load balancer (NLB) to balance the traffic on both instances of PgBouncer.

Failover Manager with PgBouncer cloud architecture diagram

Failover Manager with PgBouncer cloud architecture diagram

Figure 4: Failover Manager’s traffic routing using PgBouncer in cloud

EDB does not support this architecturewith PgBouncer and Failover Manager/PostgreSQL running on the samemachines:

  • A restriction with cloud network load balancers [Azure](https://docs.microsoft.com/en-us/azure/load-balancer/load-balancer-troubleshoot-backend-traffic#cause-4-accessing-the-internal-load-balancer-frontend-from-the-participating-load-balancer-backend-pool-vm)

    • doesn’t route traffic properly when source and destination reside

    • on the same machines.

  • In a mixed architecture, traffic between PgBouncer and Postgres can

    • become unbalanced (sometimes local, sometimes networked).

  • PgBouncer and PostgreSQL compete for resources.

  • A master failure impacts both routing (PgBouncer) and database

    • when these two components are combined on the same machines.

Using Failover Manager with PgBouncer

Installing

Install and configure Advanced Server database, Failover Manager, and PgBouncer on AWS virtual machines as follows:

After completing the configurations, you can connect to the database onthe IP address of the network load balancer on port 6432. If a failureoccurs on the primary database server, Failover Manager promotes a newprimary and then reconfigures PgBouncer to redistribute traffic. If anyof the PgBouncer processes is not available to accept traffic, the networkload balancer redistributes all the traffic tothe remaining PgBouncer processes. Make sure that the max_client_connparameter is tuned to compensate for the higher number of connections incase of failover.