Configuring Pgpool-II watchdog: It’s going to be a lot easier

Enterprise PostgreSQL Solutions

Comments are off

Configuring Pgpool-II watchdog: It’s going to be a lot easier

Watchdog is the high availability component of Pgpool-II. Over the past few releases watchdog has gotten a lot of attention from the Pgpool-II developer community and received lots of upgrades and stability improvements.

One of the not very strong areas of pgpool-II watchdog is its configuration interface. Watchdog cluster requires quite a few config settings on each node, and it’s very easy to get it wrong and hard to debug.

For example in a three-node Pgpool-II cluster, we normally require to configure the following parameters in each pgpool.conf

# Local node identificatin (Unique for each node) 
wd_hostname
we_port
wd_heartbeat_port

# Node #1 endpoint
other_pgpool_hostname0
other_pgpool_port0
other_wd_port0

# Node #2 endpoint
other_pgpool_hostname1
other_pgpool_port1
other_wd_port1

# Local device setting for sending heartbeat
heartbeat_device

#Node #1 heartbeat endpoint
heartbeat_destination0
heartbeat_destination_port0

#Node #2 heartbeat endpoint
heartbeat_destination1
heartbeat_destination_port1

The main issue here is not the number of parameters. The issue is the value of almost each of these parameters is different for each pgpool-II node and that makes configuring, debugging, adding and removing of a watchdog difficult to manage.

One pgpool.conf for every node

When we were discussing the features for Pgpool-II 4.2 last year we decided to make the ease of use as one of the priorities for the next release. And since the most difficult configuration belongs to the watchdog area so we decided to fix that first up.

In the upcoming 4.2 version Pgpool-II uses the unified watchdog configuration and unlike the previous versions now we can use the same pgpool.conf file for every Pgpool-II node.

For the same three node pgpool-II cluster, The configuration file will now need to set these below parameters once and can use same pgpool.conf file for each node

# Node #1 config
hostname0
wd_port0
pgpool_port0

# Node #2 config
hostname1
wd_port1
pgpool_port1

# Node #3 config
hostname2
wd_port2
pgpool_port2

#Node #1 heartbeat config
heartbeat_hostname0
heartbeat_port0
heartbeat_device0
    
#Node #2 heartbeat config
heartbeat_hostname1
heartbeat_port1
heartbeat_device1

#Node #3 heartbeat config
heartbeat_hostname2
heartbeat_port2
heartbeat_device2

Once these parameters are configured the next step is to specify the unique node id of each pgpool-II node. For that purpose, pgpool-II opted to use the same technique as used by other distributed software like zookeeper (myid file), i.e. A separate single-line configuration file to set the local node id.

For that purpose, similar to the myid file, Pgpool-II uses a pgpool_node_id file.
pgpool_node_id just contains a single integer in human-readable ASCII text that represents the local pgpool-II node id.

# create pgpool_node_id for node #1
echo 1 > etc/pgpool_node_id 

Conclusion

Although from the look of it, this seems like a small feature yet it is a very useful one and makes the watchdog cluster deployment a lot easier and less error-prone. On top of it, it’s not very easy to add/remove the pgpool-II nodes from the watchdog cluster.

Pgpool-II 4.2 alpha was released just a few days ago, and GA is expected in around a monthtime. So I thought to blog about this feature as upgrading to 4.2 would require keeping these configuration changes in mind since the watchdog needs to be reconfigured before proceeding with the upgrade. Otherwise upgrading to 4.2 without considering these changes would lead to downtime.