Implementing High Availability with PgPool

Failover Manager monitors the health of Postgres nodes; in the event of a master node failure, Failover Manager performs an automatic failover to a standby node. Note that Pgpool does not monitor the health of backend nodes and will not perform failover to any standby nodes.

Beginning with version 3.2, a Failover Manager agent can be configured to use Pgpool’s PCP interface to detach the failed node from Pgpool load balancing after performing failover of the standby node. More details about the necessary configuration file changes and relevant scripts will be discussed in the sections that follow.

Configuring Failover Manager

Failover Manager provides functionality that will remove failed database nodes from Pgpool load balancing; Failover Manager can also re-attach nodes to Pgpool when returned to the Failover Manager cluster. To configure this behavior, you must identify the load balancer attach and detach scripts in the efm.properties file in the following parameters:

  • script.load.balancer.attach=/path/to/load_balancer_attach.sh %h

  • script.load.balancer.detach=/path/to/load_balancer_detach.sh %h

The script referenced by load.balancer.detach is called when Failover Manager decides that a database node has failed. The script detaches the node from Pgpool by issuing a PCP interface call. You can verify a successful execution of the load.balancer.detach script by calling SHOW NODES in a psql session attached to the Pgpool port. The call to SHOW NODES should return that the node is marked as down; Pgpool will not send any queries to a downed node.

The script referenced by load.balancer.attach is called when a resume command is issued to the efm command-line interface to add a downed node back to the Failover Manager cluster. Once the node rejoins the cluster, the script referenced by load.balancer.attach is invoked, issuing a PCP interface call, which adds the node back to the Pgpool cluster. You can verify a successful execution of the load.balancer.attach script by calling SHOW NODES in a psql session attached to the Pgpool port; the command should return that the node is marked as up. At this point, Pgpool will resume using this node as a load balancing candidate. Sample scripts for each of these parameters are provided in Appendix B.

Configuring Pgpool

You must provide values for the following configuration parameters in the pgpool.conf file on the Pgpool host:

follow_master_command = '/path/to/follow_master.sh %d %P'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
fail_over_on_backend_error = off
health_check_period = 0
failover_if_affected_tuples_mismatch = off
failover_command = ''
failback_command = ''
search_primary_node_timeout = 3

When the primary/master node is changed in Pgpool (either by failover or by manual promotion) in a non-Failover Manager setup, Pgpool detaches all standby nodes from itself, and executes the follow_master_command for each standby node, making them follow the new master node. Since Failover Manager reconfigures the standby nodes before executing the post-promotion script (where a standby is promoted to primary in Pgpool to match the Failover Manager configuration), the follow_master_command merely needs to reattach standby nodes to Pgpool.

Note that the load-balancing is turned on to ensure read scalability by distributing read traffic across the standby nodes

Note also that the health checking and error-triggered backend failover have been turned off, as Failover Manager will be responsible for performing health checks and triggering failover. It is not advisable for Pgpool to perform health checking in this case, so as not to create a conflict with Failover Manager, or prematurely perform failover.

Finally, search_primary_node_timeout has been set to a low value to ensure prompt recovery of Pgpool services upon an Failover Manager-triggered failover.

pgpool_backend.sh

In order for the attach and detach scripts to be successfully called, a pgpool_backend.sh script must be provided. pgpool_backend.sh is a helper script for issuing the actual PCP interface commands on Pgpool. Nodes in Failover Manager are identified by IP addresses, while PCP commands refer to a node ID. pgpool_backend.sh provides a layer of abstraction to perform the IP address to node ID mapping transparently.