Failover Manager with Pgpool

Pgpool-II is a popular connection pooler that can provide many capabilities, like read-only traffic load balancing,connection pooling, and running as a clustered proxy. With Failover Manager to manage availability and reconfiguringPgpool-II to proxy to the correct primary, the setup can deliver high availability in an on-premises setup as well asin a cloud setup.

Failover Manager with Pgpool on premises

For an on-premises setup, you can use a VIP to route the traffic to an available Pgpool-II instance. In this setup,the automatic failover of Pgpool-II is disabled, and Failover Manager is configured to manage Pgpool-II.

Failover Manager with Pgpool on-premises

Figure 5: Failover Manager’s traffic routing using Pgpool on-premises

Failover Manager with Pgpool in the cloud

For environments with network load balancers (e.g., cloud environments), you can use a network load balancer (NLB) to balancethe traffic over all available Pgpool-II instances without requiring a VIP.

Failover Manager with Pgpool in cloud

Figure 6: Failover Manager’s traffic routing using Pgpool in cloud

Using Failover Manager with Pgpool

Installing

Install and configure Advanced Server database, Failover Manager, andPgpool as follows:

After completing the configurations, you can connect to the database onthe IP address of the network load balancer on port 9999. If a failureoccurs on the primary database server, Failover Manager promotes anew primary and then reconfigures Pgpool to redistribute traffic. If anyof the Pgpool processes is not available to accept traffic,the network load balancer redistributes all the traffic to theremaining two Pgpool processes. Make sure thatlisten_backlog_multiplier is tuned to compensate for the higher numberof connections in case of failover.