Failover Manager with EDB Pgpool-II v4
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 reconfiguring EDB Pgpool-II to proxy to the correct primary, the setup can deliver high availability in an on-premises setup as well as in a cloud setup.
For an on-premises setup, you can use a VIP to route the traffic to an available EDB Pgpool-II instance. In this setup, the automatic failover of EDB Pgpool-II is disabled, and Failover Manager is configured to manage EDB Pgpool-II.
For environments with network load balancers (e.g., cloud environments), you can use a network load balancer (NLB) to balance the traffic over all available EDB Pgpool-II instances without requiring a VIP.
Install and configure Advanced Server database, Failover Manager, and EDB Pgpool-II as follows:
|PgDB server 1, server2, and server 3||Primary / standby node running Advanced Server 13 and Failover Manager 4.2|
|EDB Pgpool-II||Pgpool node running EDB Pgpool-II 4.2 in a watchdog configuration. Register these three nodes as targets in the target group. Three is the minimum and is sufficient for most cases.|
EDB does not support this architecture with EDB Pgpool-II and Failover Manager/PostgreSQL running on the same machines for the following reasons:
A restriction with cloud network load balancers doesn't route traffic properly when source and destination reside on the same machines.
In mixed architecture, traffic between Pgpool and Postgres can become unbalanced.
Pgpool and PostgreSQL might compete for resources.
Failover Manager can remove failed database nodes from EDB Pgpool-II load balancing. It can also reattach nodes to EDB Pgpool-II when returned to the Failover Manager cluster. To configure Failover Manager for high availability using EDB Pgpool-II, you must set the following properties in the cluster properties file:
You can configure some important parameters in the
pgpool.conf file to integrate EDB Pgpool-II with Failover Manager.
There are three PostgreSQL backend nodes: one primary and two standby
nodes. Configure using backend_* configuration parameters in
pgpool.conf, and use the equal backend weights for all nodes. This
makes the read queries distributed equally among all nodes.
Set the following configuration parameter in the
pgpool.conf file to enable load balancing and streaming replication mode:
For EDB Pgpool-II version 4.2:
For EDB Pgpool-II versions prior to 4.2:
Health checking and failover are handled by Failover Manager, so disable them on the EDB Pgpool-II side. To disable the health check and failover on EDB Pgpool-II side, assign the following values:
Ensure the following while setting up the values in the pgpool.conf file:
Keep the value of
pgpool.confdifferent on each node. The node with the highest value gets the highest priority.
backend_hostname2, and so on are shared properties (in Failover Manager terms) and must hold the same value in the
pgpool.conffile on all the EDB Pgpool-II nodes.
Update the correct interface value in
arpingcmd props in the
Add the properties
heartbeat_destination2, and so on as per the number of nodes in the
pgpool.conffile on every node. Here set
heartbeat_destination0to the IP address or hostname of the local node.
The script uses the PCP interface, so you need to set up the PCP and
.PCPPASS file to allow PCP connections without a password prompt.
To set up PCP, see: Configuring pcp.conf.
To set up PCPPASS, see: PCP commands.
The load balancing is turned on to ensure read scalability by distributing read traffic across the standby nodes.
The health checking and error-triggered backend failover are turned off, as Failover Manager is responsible for performing health checks and triggering failover. We do not recommend using EDB Pgpool-II to perform health checking in this case, to avoid a conflict with Failover Manager or prematurely performing failover.
search_primary_node_timeout is set to a low value to
ensure prompt recovery of EDB Pgpool-II services upon a Failover
Both EDB Pgpool-II and Failover Manager provide the functionality to employ a virtual IP for seamless failover. While both provide this capability, the EDB Pgpool-II leader is the process that receives the application connections through the virtual IP. As in this design, such virtual IP management is performed by the EDB Pgpool-II watchdog system. Failover Manager VIP doesn't help in this design, so disable it.
In a failure situation of the active instance of EDB Pgpool-II (the primary EDB Pgpool-II server in our sample architecture), the next available standby EDB Pgpool-II instance (according to watchdog priority) is activated and takes charge as the leader EDB Pgpool-II instance.
For Failover Manager / EDB Pgpool-II integration using the network load balancer in AWS or Azure, you need to perform some additional steps.
Add the following rules to the security groups to be used by the EDB Pgpool-II instances:
Rules for the security group to be used by the EDB Pgpool-II instances (SG Pgpool).
Type Protocol Port range Source Description Custom TCP TCP 9000 Entire Subnet Watchdog Custom TCP TCP 9694 Entire Subnet Heartbeat Custom TCP TCP 9898 Entire Subnet pcp Custom TCP TCP 9999 Entire Subnet Pgpool
In addition to these rules, add the rules for SSH and Ping as per your requirement.
Rules for the security group to be used by the database instances (SG DB):
Type Protocol Port range Source Description Custom TCP TCP 7800 Entire Subnet Failover Manager Custom TCP TCP 5444 Entire Subnet Postgres
Setting these rules ensures that the ports required to run the database, Failover Manager, and EDB Pgpool-II are open for communication between the nodes and the load balancer for traffic routing and health monitoring.
In addition to these rules, add the rules for SSH and Ping as per your requirement.
If using AWS, see Configuring NLB in AWS.
After configuring the security group rules described in Configuring the network load balancer , follow the Azure documentation to:
Create a backend pool consisting of all the virtual machines running EDB Pgpool-II instances. Use the private IPs of the virtual machines to create the backend pool.
Add a health probe to check if the EDB Pgpool-II instance is available on the virtual machines. Set the protocol to
TCPand the port to
Add two load balancing rules, one each for port
9999. These rules ensure that the network traffic coming toward that port is distributed evenly among all the virtual machines present in the backend pool. Set the type to
Publicload balancer or
After completing these configurations, you can connect to the database
on the IP address of the network load balancer on port 9999. If a
failure occurs on the primary database server, Failover Manager
promotes a new primary and then reconfigures EDB Pgpool-II to redistribute
traffic. If any of the EDB Pgpool-II processes is not available to accept
traffic, the network load balancer redistributes all the
traffic to the remaining two EDB Pgpool-II processes. Make sure that the
listen_backlog_multiplier parameter is tuned to compensate for the
higher number of connections in case of failover.
The following assumptions have been taken for the sample configuration:
All the EC2 instances and the load balancer are deployed in the same subnet. If required, you can add the database nodes to another subnet, but that requires a more complex configuration and might have a performance impact.
There is a security group for EDB Pgpool-II and a security group for the database instances.
After configuring the security group rules described in Configuring the network load balancer , follow the AWS documentation to:
Create two target groups with the following details:
Name Type Protocol Port VPC pcp Instances TCP 9898 Select the VPC to which the instances are connected. pgpool Instances TCP 9999 Select the VPC to which the instances are connected.
Leave the rest of the settings (Health check TCP and Advanced health check settings) as default.
Register the created target groups with the instances that are running PgBouncer.
Create a load balancer with the following details:
Type VPC Listener
Internal. EDB recommends using an internal load balancer.
Choose a VPC and map it to the desired zones. Create a listener with
9898, and forward it to the target group pcp. Create another listener with
9999, and forward it to the target group pgpool.
After completing the configurations, you can connect to the database on
the IP address of the network load balancer on port 9999. If a failure
occurs on the primary database server, Failover Manager promotes a
new primary and then reconfigures EDB Pgpool-II to redistribute traffic. If any
of the EDB Pgpool-II processes is not available to accept traffic,
the network load balancer redistributes all the traffic to the
remaining two EDB Pgpool-II processes. Make sure that
listen_backlog_multiplier is tuned to compensate for the higher number
of connections in case of failover.