PgPool Watchdog is a subprocess of PgPool that allows High Availability for PgPool. The objective of this article is to show how to configure Watchdog and observe quorum behavior, as well as to configure the usage of a virtual IP for High Availability of applications.
Every PgPool node that is going to be part of the Watchdog cluster must have a file located in its configuration directory (/etc/pgpool-II for community PgPool or /etc/sysconfig/edb/pgpool4.5/ for EDB PgPool) called pgpool_node_id, containing a unique integer number that will serve as its ID in the Watchdog cluster. This file also needs to be owned by the postgres or enterprisedb user.
Example:
echo 0 > /etc/sysconfig/edb/pgpool4.5/pgpool_node_id
chown enterprisedb:enterprisedb /etc/sysconfig/edb/pgpool4.5/pgpool_node_id
For the other nodes in the cluster, add a different number (other than 0) in their respective pgpool_node_id files.
use_watchdog = on
In the example below, we are configuring three PgPool nodes. For each PgPool node, we must add a different number at the end of the setting names for the hostname, Watchdog port, and PgPool port. The pgpool_port value must match the value defined in the port setting.
Example:
hostname0 = '192.168.1.103'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '192.168.1.104'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = '192.168.1.105'
wd_port2 = 9000
pgpool_port2 = 9999
This setting must have a unique priority number across all PgPool nodes. It is a unique value for each pgpool.conf file of each PgPool node. For the first PgPool, configure it with 1, for the second with 2, and for the third with 3. You can assign any numbers you prefer, but they must be unique for each PgPool node.
Example:
-- On PgPool configuration file of the backend 0:
wd_priority = 1
-- On PgPool configuration file of the backend 1:
wd_priority = 2
-- On PgPool configuration file of the backend 2:
wd_priority = 3
In the delegate_ip setting, you specify an IP address from your network that is currently not assigned to any host. This IP address will be assigned to the PgPool node that is the leader in the Watchdog cluster. The Virtual IP (VIP) is only added to a PgPool node if a quorum is established for Watchdog.
Example:
delegate_ip = '192.168.1.249'
To add or remove the VIP from the leader node, PgPool uses the Linux command ip addr. Ensure that the PgPool host has this command installed (it is native to most Linux distributions). Then, configure the following parameters, where $_IP_$/24 is a variable that automatically receives the value of delegate_ip plus your network mask (in this example, /24). Also, specify the device of your host (in this example, the device is enp0s3):
Example:
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s3 label (PGPOOL-VIP)'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s3'
If the ping command is not located in /bin, configure its path as the following example:
ping_path = '/usr/bin'
In the sudoers file of all PgPool nodes, add a line allowing the postgres or enterprisedb user (depending on which PgPool version you have installed) to run the ip command with sudo privileges, without requiring a password. This user will be responsible for adding or removing the virtual IP from the leader node.
visudo
enterprisedb ALL=(ALL) NOPASSWD: /sbin/ip
IMPORTANT!!: Proceed with caution when editing the sudoers file. Ensure that the line is added correctly to avoid password requirements and to prevent conflicts with other rules.
The Watchdog mode by default requires an odd number of PgPool hosts as members of the Watchdog cluster and a minimum of three nodes to establish a quorum. The existence of a quorum allows PgPool to elect a leader and attach the VIP to the leader node. Each online PgPool node votes to reach consensus to determine if a failover should occur, detaching the failed Postgres backend node.
You can check the quorum state by using the PCP command pcp_watchdog_info (more information on its usage is available below in this article). The value displayed in Nodes required for quorum defines how many PgPool nodes must be online for the quorum to exist.
Output when the quorum exists:
Watchdog Cluster Information
Total Nodes : 3
Remote Nodes : 2
Member Remote Nodes : 2
Alive Remote Nodes : 1
Nodes required for quorum: 2
Quorum state : QUORUM EXIST
Local node escalation : NO
Leader Node Name : 192.168.1.104:9999 Linux ROCKY8
Leader Host Name : 192.168.1.104
The output if the quorum is absent:
Watchdog Cluster Information
Total Nodes : 3
Remote Nodes : 2
Member Remote Nodes : 2
Alive Remote Nodes : 0
Nodes required for quorum: 2
Quorum state : QUORUM ABSENT
Local node escalation : NO
Leader Node Name : 192.168.1.103:9999 Linux ROCKY8
Leader Host Name : 192.168.1.103
The following parameters allow PgPool to rely on the quorum to decide when to perform a failover, which detaches a failed Postgres backend node from PgPool. The Quorum state must be QUORUM EXIST in the output of pcp_watchdog_info.
failover_when_quorum_exists = on
We can also specify that a failed node will only be detached (failover) if consensus is achieved in the quorum. For example, in a Watchdog cluster with three nodes, if one PgPool goes offline, there will be no consensus even though the quorum exists, and the failed node will be kept in quarantine until a consensus is achieved instead of detaching the node.
failover_require_consensus = on
If there are not enough online PgPool nodes in the quorum to achieve consensus, nodes can be allowed to cast multiple votes to achieve consensus:
allow_multiple_failover_requests_from_node = on
If there are enough PgPool nodes for a consensus, the failed backend will be detached and appear as down in the output of the command SHOW POOL_NODES; for all PgPool nodes:
SHOW POOL_NODES;
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+---------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.1.100 | 5444 | up | unknown | 0.500000 | standby | unknown | 0 | false | 0 | | | 2024-10-29 18:08:25
1 | 192.168.1.101 | 5444 | down | unknown | 0.000000 | standby | unknown | 0 | false | 0 | | | 2024-10-29 18:08:23
2 | 192.168.1.102 | 5444 | up | unknown | 0.500000 | standby | unknown | 0 | true | 0 | | | 2024-10-29 18:08:25
(3 rows)
If there are not enough PgPool nodes for a consensus, the failed backend will remain in quarantine status until consensus is reached, and the node will not be detached. This will show in the output of the command SHOW POOL_NODES; for all PgPool nodes:
SHOW POOL_NODES;
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+---------------+------+------------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 192.168.1.100 | 5444 | up | unknown | 0.500000 | standby | unknown | 0 | false | 0 | | | 2024-10-29 18:28:18
1 | 192.168.1.101 | 5444 | quarantine | unknown | 0.000000 | standby | unknown | 0 | false | 0 | | | 2024-10-29 18:28:18
2 | 192.168.1.102 | 5444 | up | unknown | 0.500000 | standby | unknown | 0 | true | 0 | | | 2024-10-29 18:28:19
(3 rows)
The following error may occur if there are not enough votes to achieve consensus and a failed Postgres backend becomes online before Watchdog assigns it a quarantine status. To resolve this, you may need to restart the PgPool node(s):
psql: error: connection to server at "192.168.1.249", port 9999 failed: ERROR: unable to read message kind
DETAIL: kind does not match between main(0) slot[0] (52)
As previously mentioned, the default number of PgPool nodes required for Watchdog to establish a quorum is an odd number with a minimum of three nodes. However, if your environment only has two PgPool nodes for example, it may initially work and establish a quorum, but if one node goes down, there will be no quorum, and the VIP will not be assigned to the remaining PgPool node. In this case, you can configure the quorum to work with half the number of nodes. This value also affects the number of necessary votes for consensus:
enable_consensus_with_half_votes = on
The following parameter removes a shutdown PgPool node from the quorum computation, meaning the node is marked as a Nonmember and removed from the cluster. If the previously shutdown node restarts, it is automatically added back to the cluster. This setting also allows Watchdog to establish a quorum and consensus with only one PgPool node in the Watchdog cluster, enabling Watchdog to assign the VIP to a single remaining node or detach failed Postgres backends with just one PgPool node in the cluster:
wd_remove_shutdown_nodes = on
If a PgPool node enters a LOST state, the following timeout can be set (in seconds) to remove this node from the quorum; once it reconnects, it will automatically rejoin as a member of the quorum:
wd_lost_node_removal_timeout = 10s
If a node does not join at the initial cluster startup for any reason and remains in the NO-SHOW state, its membership can be revoked by setting the timeout below (in seconds). When the node starts up and connects to the cluster, it will become a member again:
wd_no_show_node_removal_timeout = 10s
The wd_lifecheck_method parameter defines the life check method used by Watchdog. It can be set to:
-
heartbeat, which periodically sends network packets to PgPool nodes; -
query, which runs a simple SQL query defined by the user periodically; or -
external, which executes a specific command defined by the user.
The wd_interval specifies the interval between life checks in seconds (the value must be greater than 0).
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
- `heartbeat_hostname: PgPool node hostname or IP address;
-
heartbeat_port: PgPool node port for the heartbeat; -
heartbeat_device: Network device of the PgPool node; -
wd_heartbeat_keepalive: Interval between each sent heartbeat packet; -
wd_heartbeat_deadtime: Time in seconds before marking the remote PgPool node as failed if no heartbeat signal is received within this period.
Example for three PgPool nodes where the network device is enp0s3 for each host:
heartbeat_hostname0 = '192.168.1.103'
heartbeat_port0 = 9694
heartbeat_device0 = 'enp0s3'
heartbeat_hostname1 = '192.168.1.104'
heartbeat_port1 = 9694
heartbeat_device1 = 'enp0s3'
heartbeat_hostname2 = '192.168.1.105'
heartbeat_port2 = 9694
heartbeat_device2 = 'enp0s3'
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
-
wd_life_point: The number of retry attempts for a failed life check before marking the node as inactive (value must be greater than 1); -
wd_lifecheck_query: Defines a query to check if the PgPool node is alive; -
wd_lifecheck_dbname: Database name for the connection where the query will be executed; -
wd_lifecheck_user: Specifies the database user name used for life checks of remote PgPool nodes; -
wd_lifecheck_password: Password for the database user; anAES256-CBCencrypted password can also be specified.
Example configuration:
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'postgres'
wd_lifecheck_user = 'enterprisedb'
wd_lifecheck_password = '12345678'
Ensure that all PgPool nodes have the same configuration except for wd_priority, which should be set to a different number on each node. Once properly configured, the VIP will be assigned to the current PgPool leader node, and you can verify it by running the command ip addr, for example.
The pcp_watchdog_info command provides information about the status of the Watchdog quorum, the status of each PgPool node, and which node is the current leader with the VIP assigned to it.
To use pcp_watchdog_info, the command must be installed on the host executing it, which can be any server with network access to PgPool (or the PgPool host itself if running the command locally).
The pcp_watchdog_info command can be executed using the address of any PgPool node. Since all nodes should return the same output, it's recommended to use the VIP address for the PCP command, as it will always route to the current active leader node. For a more detailed output, use the -v option. Note that each node displays the Delegate IP, but it is only assigned to the node currently acting as the Leader.
Example of usage:
pcp_watchdog_info -h 192.168.1.249 -p 9898 -U pcpuser -w -v
Example of output:
Watchdog Cluster Information
Total Nodes : 3
Remote Nodes : 2
Member Remote Nodes : 2
Alive Remote Nodes : 2
Nodes required for quorum: 2
Quorum state : QUORUM EXIST
Local node escalation : YES
Leader Node Name : 192.168.1.104:9999 Linux ROCKY8
Leader Host Name : 192.168.1.104
Watchdog Node Information
Node Name : 192.168.1.104:9999 Linux ROCKY8
Host Name : 192.168.1.104
Delegate IP : 192.168.1.249
PgPool port : 9999
Watchdog port : 9000
Node priority : 2
Status : 4
Status Name : LEADER
Membership Status : MEMBER
Node Name : 192.168.1.103:9999 Linux ROCKY8
Host Name : 192.168.1.103
Delegate IP : 192.168.1.249
PgPool port : 9999
Watchdog port : 9000
Node priority : 1
Status : 7
Status Name : STANDBY
Membership Status : MEMBER
Node Name : 192.168.1.105:9999 Linux ROCKY8
Host Name : 192.168.1.105
Delegate IP : 192.168.1.249
PgPool port : 9999
Watchdog port : 9000
Node priority : 3
Status : 7
Status Name : STANDBY
Membership Status : MEMBER