9. Configure Watchdog service for Pgpool-II master and standby nodes
Step 1
Edit pgpool.conf on each Pgpool-II node to contain relevant watchdog settings and values.
|
Setting
|
Value
|
Description
|
|---|---|---|
|
use_watchdog
|
on
|
Turns on watchdog within Pgpool-II
|
|
wd_hostname
|
'10.91.9.101'
or
’10.91.9.102’
|
Host name or IP address of this watchdog
|
|
wd_port
|
9000
|
Port number of this watchdog
|
|
delegate_IP
|
'10.91.9.100'
|
Virtual IP address that clients use to access PostgreSQL (through Pgpool-II)
|
|
ifconfig_path
|
'/etc/pgpool2'
|
The absolute path of the directory that contains the if_up_cmd and if_down_cmd commands or scripts
|
|
if_up_cmd
|
'ifup.sh $_IP_$ <eni id of Pgpool node>'
|
The command issued when Pgpool-II attempts to bring up the virtual IP interface with the delegate_IP address. You can retrieve the eni ID of the Pgpool-II node by logging into the EC2 administration console and going to your Pgpool-II instance. In its description in the console, locate the Network interfaces entry, click eth0, and locate the interface ID. Use it for the $<eni id of Pgpool node>.
|
|
if_down_cmd
|
ifdown.sh $_IP_$ <eni id of Pgpool node>
|
The command issued when Pgpool-II attempts to bring down the virtual IP interface with the delegate_IP address. See if_up_cmd to obtain the eni ID of the Pgpool-II node.
|
|
arping_path
|
'/usr/bin'
|
Path of the install iputils-arping package
|
|
arping_cmd
|
'arping -U $_IP_$ -w 1'
|
The arping command used to verify the IPs.
|
|
heartbeat_destination0
|
'10.91.9.102'
or
’10.91.9.101’
|
The IP address against which the heartbeat check is made; the value of the other_pgpool_hostname0 setting
|
|
heartbeat_destination_port0
|
9694
|
Use the default value.
|
|
heartbeat_device
|
'eth0'
|
The NIC device for the IP address for heartbeat communication
|
|
other_pgpool_hostname0
|
'10.91.9.102'
or
’10.91.9.101’
|
The IP address of the other Pgpool-II server instance
|
|
other_pgpool_port0
|
5432
|
The port to which the other Pgpool-II node listens
|
|
other_wd_port0
|
9000
|
The port to which the other Pgpool-II watchdog feature listens
|
Step 2
Create a ifup.sh script to be used in the if_up_cmd setting of the pgpool.conf file.
1. Place the script in the /etc/pgpool2 directory.
2. Ensure it is owned by PostgreSQL and belongs to the postgres user.
3. Ensure its permissions are set to 755.
4. The file should contain the following
#!/bin/bash -x
/sbin/ifconfig eth0:0 inet $1 netmask 255.255.255.0
/sbin/ifconfig eth0:0 inet $1 netmask 255.255.255.0
Step 3
Create an ifdown.sh script to be used in the if_down_cmd setting of the pgpool.conf file.
1. Place this file in the /etc/pgpool2 directory.
2. Ensure it is owned by PostgreSQL and belongs to the postgres user.
3. Ensure its permissions are set to 755.
4. The file should contain the following
#!bin/bash -x
/sbin/ifconfig eth0:0 down
/sbin/ifconfig eth0:0 down
Step 4
Set the ssh public keys that correspond to a PostgreSQL user's .ssh/authorized_keys entry on each of the PostgreSQL database servers in the /root/.ssh directory. The root user runs the HA (with watchdog) Pgpool-II processes, and in the case of failover of PostgreSQL database servers, is responsible for executing the /etc/pgpool2/failover.sh script.
 |
Permission Denied messages related to keys will occur during failover.
|
Step 5
Before the initial startup of Pgpool-II nodes with watchdog, PTC recommends to clean up or discard the pgpool_status file in the /var/log/postgresql directory. This will cause Pgpool-II to use the node0 configured PostgreSQL database in the pgpool.conf file at startup. If a failover has occurred and the master node does not match the node0 entry in the pgpool.conf file, keep the pgpool_status file as it keeps track of the current master between start and stop of the Pgpool-II process.
Step 6
Start the pgpool process as the root user on the first Pgpool-II instance. Do not start it as a service. Run the following commands:
sudo su
/usr/sbin/pgpool -n -f /etc/pgpool2/pgpool.conf > ~postgres/pgpool.log
2>&1 &
/usr/sbin/pgpool -n -f /etc/pgpool2/pgpool.conf > ~postgres/pgpool.log
2>&1 &
Step 7
View the Pgpool-II entries in the syslog to verify that the watchdog feature has been initialized properly.
more /var/log/syslog | grep pgpool
Output should state that the watchdog and Pgpool-II have been initialized and started successfully.
Step 8
To clear this log before running a new process of Pgpool-II, PTC recommends backing up the existing syslog for historical reference and then invoke the following command:
> /var/log/syslog
Verify that there are no error entries in the ~postgres/pgpool.log file. Verify that the Pgpool-II processes have started.
Step 9
The default configuration specified in the PostgreSQL HA section sets the a value of 125 for the num_init_children setting in the pgpool.conf file. There should be 125 processes with the status “wait for connection request”, including one prefixed with “PCP:”. There are several other processes that pgpool-II and watchdog create.
Step 10
Check the interface config (ifconfig) to ensure that the virtual IP has been created. Verify that a new network interface has been created with the IP address you assigned to the delegate_IP setting in the pgpool.conf file. It should be called “eth0:0”.
Step 11
Start a Pgpool-II process as the root user on the second Pgpool-II server instance. Do not start it as a service. The logs should now show that one instance has been promoted to master and the other has successfully started. The master should report that it has accepted a registration request from the non-master Pgpool-II instance (via its IP address).
Step 12
To stop the Pgpool-II processes, use the following commands as root:
sudo su
/usr/sbin/pgpool --mode=immediate stop
/usr/sbin/pgpool --mode=immediate stop