Andy Kerber (@dbakerber), Senior Consultant
In this blog, we will cover the installation and configuration of PostgreSQL for load balancing and High Availability (HA). This is a fairly basic, traditional configuration that uses a hot standby rather than SQL replication.
Servers
First, a summary of the servers involved.
Pgstream1 and pgstream2 are the Postgres database servers. Pgstream1 includes the primary database, pgstream2 includes the hot standby.
Pgpool1 and pgpool2 are the load balancing connection pooling servers for the Postgres databases, which are set up as a watchdog cluster. Automatic activation of the hot standby is not configured, but we will discuss it in a future blog.
The steps in the section titled ‘DB Server Setup’ to the start of ‘Hot Standby Setup’ must be done on both pgstream1 and pgstream2 and the steps are the same for both servers.
DB Server Setup
The host file is below. This file is the same on all servers discussed in this blog. The –priv ips are used for communication between the primary and standby Postgres instances. The –hb ips are used for communication between the two pgpool servers. Isolating the networks is a best practice:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 10.12.1.61 pgstream2 pgstream2.localdomain 10.12.1.225 pgstream1 pgstream1.localdomain 192.168.57.10 pgstream1-priv pgstream1-priv.localdomain 192.168.57.11 pgstream2-priv pgstream2-priv.localdomain 10.12.1.142 pgpool1 pgpool1.localdomain 10.12.1.204 pgpool2 pgpool2.localdomain 192.168.31.20 pgpool1-hb pgpool1-hb.localdomain 192.168.31.21 pgpool2-hb pgpool2-hb.localdomain
For this blog, we used the open source version of Postgres, with the open source repos. Below are the commands we used to install the required repos:
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm yum install curl --header 'Host: download.postgresql.org' --user-agent 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:66.0) Gecko/20100101 Firefox/66.0' --header 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8' --header 'Accept-Language: en-US,en;q=0.5' --referer 'https://yum.postgresql.org/repopackages.php' --header 'DNT: 1' --cookie '__utma=257560202.337789427.1552660161.1554995806.1555514629.5; __utmz=257560202.1555514629.5.4.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided); __utmc=257560202; __utmb=257560202.2.10.1555514629' --header 'Upgrade-Insecure-Requests: 1' 'https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm' --output 'pgdg-redhat-repo-latest.noarch.rpm' yum -y install pgdg-redhat-repo-latest.noarch.rpm
Next, with the repos configured and everything downloaded, install the Postgres software:
yum install postgresql11-server
Configure the data directories for Postgres, and use LVM for space management. The steps below create a partition in a new disk device, put it in LVM, label it, and create an XFS file system on it. While obviously the Postgres data can go on the root file system, it works best to put it in a separate volume group for simpler space management.
fdisk /dev/sdb
Create a new primary partition on /dev/sdb using all the space on the drive. The name of the partition is /dev/sdb1.
[root@pgstream2 ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x57646c50.
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-41943039, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-41943039, default 41943039):
Using default value 41943039
Partition 1 of type Linux and of size 20 GiB is set
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
Next, create a new logical volume group for the Postgres data. The commands below create a new physical volume from /dev/sdb1. They also create a new volume group called data, containing /dev/sdb1. Next, a new logical volume named pgdata is created that contains the volume group. The logical volume will be used for the data.
pvcreate /dev/sdb1 vgcreate data /dev/sdb1 lvcreate -n pgdata -l 100%FREE data mkfs.xfs /dev/data/pgdata xfs_admin -L pgdata /dev/data/pgdata mkdir -p /postgres/data
Next, do the same for /dev/sdc. The name of the partition is /dev/sdc1. Create a new logical volume as indicated below, the logical volume will be used for the WAL (archive) logs.
fdisk /dev/sdc vgcreate archive /dev/sdc1 lvcreate -n archive_vol -l 100%FREE archive mkfs.xfs -L archive /dev/archive/archive_vol mkdir -p /postgres/archive
Then, add lines in /etc/fstab for these two new file systems:
LABEL=pgdata /postgres/data xfs defaults 0 0 LABEL=archive /postgres/archive xfs defaults 0 0
Mount the devices using mount –a. Mounting the devices this way accomplishes two things, it ensures that the fstab format is correct, and mounts the drives.
Next, we need to initialize the database. Set the PGDATA environment variable to point to our data directory:
export PGDATA=/postgres/data/pgdata /usr/pgsql-11/bin/postgresql-11-setup initdb
This creates the initial data directory (cluster, in Postgres parlance) with an empty database and the required configuration files.
Next, edit the pg_hba.conf file to allow connections as desired. Our pg_hba.conf file is below:
local all all trust host all all 0.0.0.0/0 md5 host all all pgstream1 trust host all all pgstream2 trust # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 ident host replication all ::1/128 ident host replication all 0.0.0.0/0 md5
At this point, the initial basic setup is complete. Now we need to configure the database for hot standby replication.
Configure Postgres to start as a service. First, edit the file /usr/lib/systemd/system/postgresql-11.service and edit the line beginning with ‘Environment=’. Change the line to read:
Environment=PGDATA=/postgres/data/pgdata
Now, enable and start the service:
systemctl enable postgresql-11.service systemctl start postgresql-11.service
Hot Standby Setup – Primary
On the primary server, make the following changes to the postgresql.conf file. Only the non-default parameters are noted here:
listen_addresses = '*' shared_buffers = 1024MB # min 128kB wal_level = logical # minimal, replica, or logical full_page_writes = on # recover from partial page writes max_wal_size = 1GB min_wal_size = 80MB archive_mode = on # enables archiving; off, on, or always archive_command = 'test ! -f /postgres/archive/%f && cp %p /postgres/archive/%f' hot_standby = on # "off" disallows queries during recovery
Create the replication user, which is the user that has privileges to create the hot standby:
postgres=# create role replication with REPLICATION password 'mypassword' login;
Hot Standby Setup – Standby
On the standby server, stop the Postgres service:
systemctl stop postgresql-11.service
Then, delete all files in the PGDATA directory:
(rm –rf /postgres/data/pgdata)
Create the hot standby files using the pg_basebackup command:
pg_basebackup -h pgstream1-priv -U replication -D /postgres/data/pgdata
Next, before starting the database, create the recovery.conf file (with the contents below) in the PGDATA directory. This tells Postgres where the source WAL logs are coming from:
standby_mode = 'on' primary_conninfo = 'host=pgstream1-priv port=5432 user=replication password=replication' trigger_file= '/var/lib/pgsql/activate.now'
Start the standby:
systemctl start postgresql-11.service
After starting the service, monitor the log file in $PGDATA/log/postgresql-dow.log. Lines similar to those below indicate that the standby is working properly:
2019-04-30 14:29:47.664 CDT [20428] LOG: database system is ready to accept read only connections 2019-04-30 14:29:47.675 CDT [20436] LOG: started streaming WAL from primary at 0/7000000 on timeline 1
Should it become necessary to activate the standby in read-write mode, we will use the command ‘touch /var/lib/pgsql/activate.now’. The existence of the file created by the ‘touch’ command is the signal to activate the standby. The file is created on the standby server.
Pgpool Setup
Pgpool is connection pooling software for the Postgres database. It can be used to direct communications to the standby or primary node, as well as manage failover.
More about managing failover will be discussed later blogs. In this blog, we are going set up a two-node Pgpool watchdog cluster.
First, install the Pgpool software. For this example, we are using the most recent available version pgpool-II-10. The command to install the software is yum install pgpool-II-10. Note that for this version of Pgpool, the authors chose to leave the full version information on the directories, so you will use the Pgpool-II-10 directories rather than the Pgpool-II directories, which can be confusing.
Next, modify the files for the service to be run by root. Running it under root makes management easier. Just remove the line that tells it to run under Postgres, so the file /usr/lib/systemd/system/pgpool-II-10.service reads like this:
[Unit] Description=PGPool-II Middleware Between PostgreSQL Servers And PostgreSQL Database Clients After=syslog.target network.target [Service] EnvironmentFile=-/etc/sysconfig/pgpool-II-10 ExecStart=/usr/pgpool-10/bin/pgpool -f /etc/pgpool-II-10/pgpool.conf $OPTS ExecStop=/usr/pgpool-10/bin/pgpool -f /etc/pgpool-II-10/pgpool.conf -m fast stop ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT [Install] WantedBy=multi-user.target
Update the file on pgpool1 as follows (non-default settings are the only settings listed):
listen_addresses = '*' port = 5432 pcp_listen_addresses = '*' pcp_port = 9898 backend_hostname0 = '10.12.1.225' backend_port0 = 5432 backend_data_directory0 = '/postgres/data/pgdata' backend_flag0 = 'DISALLOW_TO_FAILOVER' backend_hostname1 = '10.12.1.61' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/postgres/data/pgdata' backend_flag1 = 'DISALLOW_TO_FAILOVER' enable_pool_hba = on pool_passwd = 'pool_passwd' log_destination = 'syslog' syslog_facility = 'LOCAL6' syslog_ident = 'pgpool-II-10' load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_user = 'postgres' sr_check_password = 'password' sr_check_database = 'postgres' health_check_user = 'postgres' health_check_password = 'password' use_watchdog = on wd_hostname = '192.168.31.20' wd_port = 9000 delegate_IP = '10.12.1.111' if_up_cmd = 'ip addr add $_IP_$/24 dev ens33 label ens33:111' if_down_cmd = 'ip addr del $_IP_$/24 dev ens33' arping_cmd = 'ping -w 1 $_IP_$' wd_lifecheck_method = 'heartbeat' wd_heartbeat_port = 9694 heartbeat_destination0 = '192.168.31.21' heartbeat_destination_port0 = 9694 other_pgpool_hostname0 = '192.168.159.21' other_pgpool_port0 = 5432 other_wd_port0 = 9000
On pgpool2, the changes are very similar, just reverse the node IP addresses:
listen_addresses = '*' port = 5432 pcp_listen_addresses = '*' pcp_port = 9898 backend_hostname0 = '10.12.1.225' backend_port0 = 5432 backend_data_directory0 = '/postgres/data/pgdata' backend_flag0 = 'DISALLOW_TO_FAILOVER' backend_hostname1 = '10.12.1.61' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/postgres/data/pgdata' backend_flag1 = 'DISALLOW_TO_FAILOVER' enable_pool_hba = on pool_passwd = 'pool_passwd' log_destination = 'syslog' syslog_facility = 'LOCAL6' syslog_ident = 'pgpool-II-10' load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_user = 'postgres' sr_check_password = 'password' sr_check_database = 'postgres' health_check_user = 'postgres' health_check_password = 'password' use_watchdog = on wd_hostname = '192.168.31.21' wd_port = 9000 delegate_IP = '10.12.1.111' if_up_cmd = 'ip addr add $_IP_$/24 dev ens33 label ens33:111' if_down_cmd = 'ip addr del $_IP_$/24 dev ens33' arping_cmd = 'ping -w 1 $_IP_$' wd_lifecheck_method = 'heartbeat' wd_heartbeat_port = 9694 heartbeat_destination0 = '192.168.31.20' heartbeat_destination_port0 = 9694 other_pgpool_hostname0 = '192.168.159.20' other_pgpool_port0 = 5432 other_wd_port0 = 9000
Next, on each server add this line to /etc/rsyslog.conf:
local6.* /var/log/pgpool-II-10/pgpool-II-10.log
The line will cause pgpool-II-10 to log information to the file /var/log/pgpool-II-10/ pgpool-II-10.log.
Then, on either server, use the pg_md5 to populate the pool_passwd file:
pg_md5 --md5auth --username=postgres password
The file is located in /etc/pgpool-II-10. Copy the password from the current server to the same directory on the other server.
Edit the pool_hba.conf to read as required. Below is the pool_hba.conf for this configuration:
# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 0.0.0.0/0 md5 host all all pgstream1 trust host all all pgstream2 trust
Copy the file to both nodes.
At this point, you are ready to start pgpool. On the first node, start the service using this command:
systemctl start pgpool-II-10.service
A successful startup of the first node will look like the following in the log file:
Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [1-1] 2019-04-30 16:39:50: pid 17955: WARNING: checking setuid bit of if_up_cmd Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [1-2] 2019-04-30 16:39:50: pid 17955: DETAIL: ifup[/usr/sbin/ip] doesn't have setuid bit Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [2-1] 2019-04-30 16:39:50: pid 17955: WARNING: checking setuid bit of if_down_cmd Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [2-2] 2019-04-30 16:39:50: pid 17955: DETAIL: ifdown[/usr/sbin/ip] doesn't have setuid bit Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [3-1] 2019-04-30 16:39:50: pid 17955: WARNING: checking setuid bit of arping command Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [3-2] 2019-04-30 16:39:50: pid 17955: DETAIL: arping[/bin/ping] doesn't have setuid bit Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [4-1] 2019-04-30 16:39:50: pid 17955: LOG: Backend status file /var/log/pgpool-II-10/pgpool_status discarded Apr 30 16:39:50 pgpool1 pgpool-II-10[17955]: [5-1] 2019-04-30 16:39:50: pid 17955: LOG: waiting for watchdog to initialize Apr 30 16:39:50 pgpool1 pgpool-II-10[17956]: [5-1] 2019-04-30 16:39:50: pid 17956: LOG: setting the local watchdog node name to "192.168.31.20:5432 Linux pgpool1.localdomain" Apr 30 16:39:50 pgpool1 pgpool-II-10[17956]: [6-1] 2019-04-30 16:39:50: pid 17956: LOG: watchdog cluster is configured with 1 remote nodes Apr 30 16:39:50 pgpool1 pgpool-II-10[17956]: [7-1] 2019-04-30 16:39:50: pid 17956: LOG: watchdog remote node:0 on 192.168.159.21:9000 Apr 30 16:39:50 pgpool1 pgpool-II-10[17956]: [8-1] 2019-04-30 16:39:50: pid 17956: LOG: interface monitoring is disabled in watchdog Apr 30 16:39:50 pgpool1 pgpool-II-10[17956]: [9-1] 2019-04-30 16:39:50: pid 17956: LOG: watchdog node state changed from [DEAD] to [LOADING] Apr 30 16:39:55 pgpool1 pgpool-II-10[17956]: [10-1] 2019-04-30 16:39:55: pid 17956: LOG: watchdog node state changed from [LOADING] to [JOINING] Apr 30 16:39:59 pgpool1 pgpool-II-10[17956]: [11-1] 2019-04-30 16:39:59: pid 17956: LOG: watchdog node state changed from [JOINING] to [INITIALIZING] Apr 30 16:40:00 pgpool1 pgpool-II-10[17956]: [12-1] 2019-04-30 16:40:00: pid 17956: LOG: I am the only alive node in the watchdog cluster Apr 30 16:40:00 pgpool1 pgpool-II-10[17956]: [12-2] 2019-04-30 16:40:00: pid 17956: HINT: skipping stand for coordinator state Apr 30 16:40:00 pgpool1 pgpool-II-10[17956]: [13-1] 2019-04-30 16:40:00: pid 17956: LOG: watchdog node state changed from [INITIALIZING] to [MASTER] Apr 30 16:40:00 pgpool1 pgpool-II-10[17956]: [14-1] 2019-04-30 16:40:00: pid 17956: LOG: I am announcing my self as master/coordinator watchdog node Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [15-1] 2019-04-30 16:40:04: pid 17956: LOG: I am the cluster leader node Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [15-2] 2019-04-30 16:40:04: pid 17956: DETAIL: our declare coordinator message is accepted by all nodes Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [16-1] 2019-04-30 16:40:04: pid 17956: LOG: setting the local node "192.168.31.20:5432 Linux pgpool1.localdomain" as watchdog cluster master Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [17-1] 2019-04-30 16:40:04: pid 17956: LOG: I am the cluster leader node. Starting escalation process Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [18-1] 2019-04-30 16:40:04: pid 17956: LOG: escalation process started with PID:17963 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [6-1] 2019-04-30 16:40:04: pid 17955: LOG: watchdog process is initialized Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [19-1] 2019-04-30 16:40:04: pid 17956: LOG: new IPC connection received Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [7-1] 2019-04-30 16:40:04: pid 17955: LOG: Setting up socket for 0.0.0.0:5432 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [8-1] 2019-04-30 16:40:04: pid 17955: LOG: Setting up socket for :::5432 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [9-1] 2019-04-30 16:40:04: pid 17955: LOG: find_primary_node_repeatedly: waiting for finding a primary node Apr 30 16:40:04 pgpool1 pgpool-II-10[17963]: [18-1] 2019-04-30 16:40:04: pid 17963: LOG: watchdog: escalation started Apr 30 16:40:04 pgpool1 pgpool-II-10[17956]: [20-1] 2019-04-30 16:40:04: pid 17956: LOG: new IPC connection received Apr 30 16:40:04 pgpool1 pgpool-II-10[17964]: [7-1] 2019-04-30 16:40:04: pid 17964: LOG: 2 watchdog nodes are configured for lifecheck Apr 30 16:40:04 pgpool1 pgpool-II-10[17964]: [8-1] 2019-04-30 16:40:04: pid 17964: LOG: watchdog nodes ID:0 Name:"192.168.31.20:5432 Linux pgpool1.localdomain" Apr 30 16:40:04 pgpool1 pgpool-II-10[17964]: [8-2] 2019-04-30 16:40:04: pid 17964: DETAIL: Host:"192.168.31.20" WD Port:9000 pgpool-II port:5432 Apr 30 16:40:04 pgpool1 pgpool-II-10[17964]: [9-1] 2019-04-30 16:40:04: pid 17964: LOG: watchdog nodes ID:1 Name:"Not_Set" Apr 30 16:40:04 pgpool1 pgpool-II-10[17964]: [9-2] 2019-04-30 16:40:04: pid 17964: DETAIL: Host:"192.168.159.21" WD Port:9000 pgpool-II port:5432 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [10-1] 2019-04-30 16:40:04: pid 17955: LOG: find_primary_node: primary node is 0 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [11-1] 2019-04-30 16:40:04: pid 17955: LOG: find_primary_node: standby node is 1 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [12-1] 2019-04-30 16:40:04: pid 17955: LOG: pgpool-II successfully started. version 4.0.3 (torokiboshi) Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [13-1] 2019-04-30 16:40:04: pid 17955: LOG: node status[0]: 1 Apr 30 16:40:04 pgpool1 pgpool-II-10[17955]: [14-1] 2019-04-30 16:40:04: pid 17955: LOG: node status[1]: 2 Apr 30 16:40:05 pgpool1 pgpool-II-10[17999]: [10-1] 2019-04-30 16:40:05: pid 17999: LOG: set SO_REUSEPORT option to the socket Apr 30 16:40:05 pgpool1 pgpool-II-10[17999]: [11-1] 2019-04-30 16:40:05: pid 17999: LOG: creating socket for sending heartbeat Apr 30 16:40:05 pgpool1 pgpool-II-10[17999]: [11-2] 2019-04-30 16:40:05: pid 17999: DETAIL: set SO_REUSEPORT Apr 30 16:40:05 pgpool1 pgpool-II-10[17998]: [10-1] 2019-04-30 16:40:05: pid 17998: LOG: set SO_REUSEPORT option to the socket Apr 30 16:40:05 pgpool1 pgpool-II-10[17998]: [11-1] 2019-04-30 16:40:05: pid 17998: LOG: creating watchdog heartbeat receive socket. Apr 30 16:40:05 pgpool1 pgpool-II-10[17998]: [11-2] 2019-04-30 16:40:05: pid 17998: DETAIL: set SO_REUSEPORT Apr 30 16:40:07 pgpool1 pgpool-II-10[17963]: [19-1] 2019-04-30 16:40:07: pid 17963: LOG: successfully acquired the delegate IP:"10.12.1.111" Apr 30 16:40:07 pgpool1 pgpool-II-10[17963]: [19-2] 2019-04-30 16:40:07: pid 17963: DETAIL: 'if_up_cmd' returned with success Apr 30 16:40:07 pgpool1 pgpool-II-10[17956]: [21-1] 2019-04-30 16:40:07: pid 17956: LOG: watchdog escalation process with pid: 17963 exit with SUCCESS.
Next, use the same command to start up the second node. In the original log, a successful startup of the second node will look like this:
Apr 30 16:42:28 pgpool1 pgpool-II-10[17956]: [22-1] 2019-04-30 16:42:28: pid 17956: LOG: new watchdog node connection is received from "192.168.159.21:11962" Apr 30 16:42:28 pgpool1 pgpool-II-10[17956]: [23-1] 2019-04-30 16:42:28: pid 17956: LOG: new node joined the cluster hostname:"192.168.31.21" port:9000 pgpool_port:5432 Apr 30 16:42:28 pgpool1 pgpool-II-10[17956]: [24-1] 2019-04-30 16:42:28: pid 17956: LOG: new outbound connection to 192.168.159.21:9000 Apr 30 16:42:29 pgpool1 pgpool-II-10[17956]: [25-1] 2019-04-30 16:42:29: pid 17956: LOG: adding watchdog node "192.168.31.21:5432 Linux pgpool2" to the standby list Apr 30 16:42:29 pgpool1 pgpool-II-10[17955]: [15-1] 2019-04-30 16:42:29: pid 17955: LOG: Pgpool-II parent process received watchdog quorum change signal from watchdog Apr 30 16:42:29 pgpool1 pgpool-II-10[17956]: [26-1] 2019-04-30 16:42:29: pid 17956: LOG: new IPC connection received Apr 30 16:42:29 pgpool1 pgpool-II-10[17955]: [16-1] 2019-04-30 16:42:29: pid 17955: LOG: watchdog cluster now holds the quorum Apr 30 16:42:29 pgpool1 pgpool-II-10[17955]: [16-2] 2019-04-30 16:42:29: pid 17955: DETAIL: updating the state of quarantine backend nodes Apr 30 16:42:29 pgpool1 pgpool-II-10[17956]: [27-1] 2019-04-30 16:42:29: pid 17956: LOG: new IPC connection received
On node 2, it will look something like this:
Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [1-1] 2019-04-30 16:42:28: pid 17193: WARNING: checking setuid bit of if_up_cmd Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [1-2] 2019-04-30 16:42:28: pid 17193: DETAIL: ifup[/usr/sbin/ip] doesn't have setuid bit Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [2-1] 2019-04-30 16:42:28: pid 17193: WARNING: checking setuid bit of if_down_cmd Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [2-2] 2019-04-30 16:42:28: pid 17193: DETAIL: ifdown[/usr/sbin/ip] doesn't have setuid bit Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [3-1] 2019-04-30 16:42:28: pid 17193: WARNING: checking setuid bit of arping command Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [3-2] 2019-04-30 16:42:28: pid 17193: DETAIL: arping[/bin/ping] doesn't have setuid bit Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [4-1] 2019-04-30 16:42:28: pid 17193: LOG: Backend status file /var/log/pgpool-II-10/pgpool_status discarded Apr 30 16:42:28 pgpool2 pgpool-II-10[17193]: [5-1] 2019-04-30 16:42:28: pid 17193: LOG: waiting for watchdog to initialize Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [5-1] 2019-04-30 16:42:28: pid 17194: LOG: setting the local watchdog node name to "192.168.31.21:5432 Linux pgpool2" Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [6-1] 2019-04-30 16:42:28: pid 17194: LOG: watchdog cluster is configured with 1 remote nodes Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [7-1] 2019-04-30 16:42:28: pid 17194: LOG: watchdog remote node:0 on 192.168.159.20:9000 Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [8-1] 2019-04-30 16:42:28: pid 17194: LOG: interface monitoring is disabled in watchdog Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [9-1] 2019-04-30 16:42:28: pid 17194: LOG: watchdog node state changed from [DEAD] to [LOADING] Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [10-1] 2019-04-30 16:42:28: pid 17194: LOG: new outbound connection to 192.168.159.20:9000 Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [11-1] 2019-04-30 16:42:28: pid 17194: LOG: setting the remote node "192.168.31.20:5432 Linux pgpool1.localdomain" as watchdog cluster master Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [12-1] 2019-04-30 16:42:28: pid 17194: LOG: watchdog node state changed from [LOADING] to [INITIALIZING] Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [13-1] 2019-04-30 16:42:28: pid 17194: LOG: new watchdog node connection is received from "192.168.159.20:7321" Apr 30 16:42:28 pgpool2 pgpool-II-10[17194]: [14-1] 2019-04-30 16:42:28: pid 17194: LOG: new node joined the cluster hostname:"192.168.31.20" port:9000 pgpool_port:5432 Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [15-1] 2019-04-30 16:42:29: pid 17194: LOG: watchdog node state changed from [INITIALIZING] to [STANDBY] Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [16-1] 2019-04-30 16:42:29: pid 17194: LOG: successfully joined the watchdog cluster as standby node Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [16-2] 2019-04-30 16:42:29: pid 17194: DETAIL: our join coordinator request is accepted by cluster leader node "192.168.31.20:5432 Linux pgpool1.localdomain" Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [6-1] 2019-04-30 16:42:29: pid 17193: LOG: watchdog process is initialized Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [7-1] 2019-04-30 16:42:29: pid 17193: LOG: Pgpool-II parent process received watchdog quorum change signal from watchdog Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [17-1] 2019-04-30 16:42:29: pid 17194: LOG: new IPC connection received Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [8-1] 2019-04-30 16:42:29: pid 17193: LOG: watchdog cluster now holds the quorum Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [8-2] 2019-04-30 16:42:29: pid 17193: DETAIL: updating the state of quarantine backend nodes Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [18-1] 2019-04-30 16:42:29: pid 17194: LOG: new IPC connection received Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [19-1] 2019-04-30 16:42:29: pid 17194: LOG: new IPC connection received Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [9-1] 2019-04-30 16:42:29: pid 17193: LOG: we have joined the watchdog cluster as STANDBY node Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [9-2] 2019-04-30 16:42:29: pid 17193: DETAIL: syncing the backend states from the MASTER watchdog node Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [20-1] 2019-04-30 16:42:29: pid 17194: LOG: new IPC connection received Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [21-1] 2019-04-30 16:42:29: pid 17194: LOG: received the get data request from local pgpool-II on IPC interface Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [22-1] 2019-04-30 16:42:29: pid 17194: LOG: get data request from local pgpool-II node received on IPC interface is forwarded to master watchdog node "192.168.31.20:5432 Linux pgpool1.localdomain" Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [22-2] 2019-04-30 16:42:29: pid 17194: DETAIL: waiting for the reply... Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [10-1] 2019-04-30 16:42:29: pid 17193: LOG: master watchdog node "192.168.31.20:5432 Linux pgpool1.localdomain" returned status for 2 backend nodes Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [11-1] 2019-04-30 16:42:29: pid 17193: LOG: Setting up socket for 0.0.0.0:5432 Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [12-1] 2019-04-30 16:42:29: pid 17193: LOG: Setting up socket for :::5432 Apr 30 16:42:29 pgpool2 pgpool-II-10[17194]: [23-1] 2019-04-30 16:42:29: pid 17194: LOG: new IPC connection received Apr 30 16:42:29 pgpool2 pgpool-II-10[17195]: [7-1] 2019-04-30 16:42:29: pid 17195: LOG: 2 watchdog nodes are configured for lifecheck Apr 30 16:42:29 pgpool2 pgpool-II-10[17195]: [8-1] 2019-04-30 16:42:29: pid 17195: LOG: watchdog nodes ID:0 Name:"192.168.31.21:5432 Linux pgpool2" Apr 30 16:42:29 pgpool2 pgpool-II-10[17195]: [8-2] 2019-04-30 16:42:29: pid 17195: DETAIL: Host:"192.168.31.21" WD Port:9000 pgpool-II port:5432 Apr 30 16:42:29 pgpool2 pgpool-II-10[17195]: [9-1] 2019-04-30 16:42:29: pid 17195: LOG: watchdog nodes ID:1 Name:"192.168.31.20:5432 Linux pgpool1.localdomain" Apr 30 16:42:29 pgpool2 pgpool-II-10[17195]: [9-2] 2019-04-30 16:42:29: pid 17195: DETAIL: Host:"192.168.159.20" WD Port:9000 pgpool-II port:5432 Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [13-1] 2019-04-30 16:42:29: pid 17193: LOG: pgpool-II successfully started. version 4.0.3 (torokiboshi) Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [14-1] 2019-04-30 16:42:29: pid 17193: LOG: node status[0]: 0 Apr 30 16:42:29 pgpool2 pgpool-II-10[17193]: [15-1] 2019-04-30 16:42:29: pid 17193: LOG: node status[1]: 0 Apr 30 16:42:30 pgpool2 pgpool-II-10[17233]: [10-1] 2019-04-30 16:42:30: pid 17233: LOG: set SO_REUSEPORT option to the socket Apr 30 16:42:30 pgpool2 pgpool-II-10[17233]: [11-1] 2019-04-30 16:42:30: pid 17233: LOG: creating socket for sending heartbeat Apr 30 16:42:30 pgpool2 pgpool-II-10[17233]: [11-2] 2019-04-30 16:42:30: pid 17233: DETAIL: set SO_REUSEPORT Apr 30 16:42:30 pgpool2 pgpool-II-10[17232]: [10-1] 2019-04-30 16:42:30: pid 17232: LOG: set SO_REUSEPORT option to the socket Apr 30 16:42:30 pgpool2 pgpool-II-10[17232]: [11-1] 2019-04-30 16:42:30: pid 17232: LOG: creating watchdog heartbeat receive socket. Apr 30 16:42:30 pgpool2 pgpool-II-10[17232]: [11-2] 2019-04-30 16:42:30: pid 17232: DETAIL: set SO_REUSEPORT
At this point, Pgpool should be properly configured. To test, login as the Postgres user on either of the Pgpool nodes and set the variables below:
export PGPORT=5432 export PGDATA=/postgres/data/pgdata export PGHOSTADDR=10.12.1.111
Then enter the command psql.
It should prompt for the password, and if it does, configuration was successful.
-bash-4.2$ psql Password: psql (10.7, server 11.2) WARNING: psql major version 10, server major version 11. Some psql features might not work. Type "help" for help. postgres=# select inet_server_addr(); inet_server_addr ------------------ 10.12.1.61 (1 row) postgres-# \quit -bash-4.2$ psql Password: psql (10.7, server 11.2) WARNING: psql major version 10, server major version 11. Some psql features might not work. Type "help" for help. postgres=# select inet_server_addr(); inet_server_addr ------------------ 10.12.1.225 (1 row) postgres=#
Connect again, as shown above, to verify that load balancing is working properly. As we can see, it successfully connected to both nodes, so we can connect to both instances. Pgpool is designed to send update queries to the primary node, and will send select queries to the standby nodes. The configuration of PostgreSQL with pgpool is complete.
Conclusion
In this blog, we have installed and configured PostgreSQL and pgpool for high availability. We did not cover automatic failover, and typically you will not want to fail over automatically. My next blog will cover failover and recovery for PostgreSQL.
Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.
