PostgreSQL High Availability using PgPool II

With increasing and fast development of connectivity, web applications have growth enormously, in part because of the growing of the amount of clients and the requests of these as well. These applications work with an underlying database. Having interruptions of the database may cause unexpected functioning. For small applications this could be permissible, however for critical applications constant interruptions of databases are unacceptable. For instance, applications in airport that monitor and control air traffic should be equipped with a database capable of serving the data every time that is requested. If this database gets down this airport may suffer a serious catastrophe. There exits tons of phenomena that could crashes services provided for databases.

Some solutions are synchronous, meaning that a data-modifying transaction is not considered committed until all servers have committed the transaction. This guarantees that a failover will not lose any data and that all load-balanced servers will return consistent results no matter which server is queried. In contrast, asynchronous solutions allow some delay between the time of a commit and its propagation to the other servers, opening the possibility that some transactions might be lost in the switch to a backup server, and that load balanced servers might return slightly stale results. Asynchronous communication is used when synchronous would be too slow.Postgresql_HA.jpg

Replication Model

A master-slave replication setup sends all data modification queries to the master server. The master server asynchronously sends data changes to the slave server. The slave can answer read-only queries while the master server is running. The slave server is ideal for data warehouse queries.Streaming Replication (SR) provides the capability to continuously ship and apply the WAL XLOG records to some number of standby servers in order to keep them current.

Physical Infrastructure and Implementation Plan

HA_Postgrese_Fina.JPG

We will implement two servers (Master & Standby) clusters. In order to have redundancy we will setup high availability for pgpool instances also.We can deploy them on the same servers PostgreSQL database is deployed on.

We will be using two Redhat Linux 7 servers each carrying PostgreSQL 11 and Pgpool-II (4.0.5)

Pgpool-II

Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client. Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version) comes in.

If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server. It does that by distributing SELECT queries among available servers, improving the system’s overall throughput. If one of database servers goes down or becomes unreachable, Pgpool-II will detaches it and continues operations using rest of database servers. There are some sophisticated features which help the automated fail over including timeout and retries.

Pgpool-II can manage multiple PostgreSQL servers. Activating the replication feature makes it possible to create a real time backup on 2 or more PostgreSQL clusters, so that the service can continue without interruption if one of those clusters fails. Pgpool-II has built-in replication (native replication). Pgpool-II works on Linux, Solaris, FreeBSD, and most of the UNIX-like architectures. Windows is not supported.

PostgreSQL and pgpool-II sharing the same hosts.

watchdog_master_slave

Watchdog

Before starting with the actual implementation it is important to demystify one pgpool-II component – watchdog. The purpose of this component is to periodically check all other pgpool-II instances (especially the active one) if they are still running. If the active instance fails standby instance will be aware of this failure and take over active role, thanks to the watchdog. If there’s more than one standby instance running – the one with the highest priority will become the active one (we can configure priority of a particular instance in pgpool-II configuration file, as will be explained later).Watchdog checks other pgpool-II instances, not PostgreSQL databases. Health of the databases is checked by all pgpool-II instances.

Installing PostgreSQL

  • Installing yum repository

Visit https://yum.postgresql.org/repopackages.php#pg11  and scroll down to PostgreSQL 11 section, right click on your your distribution link and copy link.Install the package using yum

 sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

To list available packages

 sudo yum list postgresql*

Following command will install these packages:

1. postgresql11 – common binaries, man pages, translations, etc
2. postgresql11-libs – shared libraries
3. postgresql11-server – Main server binary and others
4. postgresql11-contrib – Additional extensions libraries to add some awesome features

sudo yum install postgresql11-server postgresql11-contrib

You need to initialize the PostgreSQL 11 database before you can successfully start it.

 /usr/pgsql-11/bin/postgresql-11-setup initdb

Enable the PostgreSQL 11 service to start at boot:

sudo systemctl enable postgresql-11

Start the postgres instance

sudo systemctl start postgresql-11

If you cannot, or do not want to, use the yum based installation method, all the RPMs that are in the yum repository are available for direct download

Configuring Replication

Create user named replication with REPLICATION privileges:

$ su - postgres
$psql
CREATE ROLE replication WITH REPLICATION PASSWORD 'XXXXXXXXXX' LOGIN;

Next change the following entries in postgresql.conf file:

vi /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = '*'
port = 5433
  • PostgreSQL instance does not have to listen to all IP addresses. Precisely, it does not have to listen virtual IP address, but it has to listen on the main server’s IP so that pgpool-II installed on the other server can access it, and it should listen on localhost address if pgpool-II instance installed on the same server accesses it this way. Anyway, there’s no harm in setting PostgreSQL to listen all available addresses.
  • Note that I’ve changed default PostgreSQL port (5432) to 5433. We want to use 5432 for pgpool-II instance so that all outside world clients can connect to pgpool-II by using this well known port.

Add/change the following entries in pg_hba.conf file:

host replication replication 10.1.10.150/32 md5
host replication replication 10.1.10.155/32 md5
  • The first two lines are allowing replication user to access the database from the IP address specified (you should change actual IP addresses appropriately). Basically not both pg_hba.conf files (on both servers) have to contain both lines. File on the primary server can contain only the second line, while the file on the standby server can contain only the first line, but again there’s no harm in having the same file with both lines on both servers.

Restart both the postgresql server process to apply the changes

sudo systemctl restart postgresql-11

 Replication Slots

Replication Slots are a persistent record of the state of a replica that is kept on the master server even when the replica is offline and disconnected. The pg_replication_slots view provides a listing of all replication slots that currently exist on the database cluster, along with their current state.

Replication slots are introducing a new approach: they are basically allowing primary server to be aware of each standby and its replication state, and to keep WAL files as long as needed – no more, no less. With this technology primary server will retain WAL files basically forever, waiting for standby to pick them up (unless itself goes down due to pg_xlog overflow). It means that we can simply turn on standby server weeks after it went down, and it will catch-up without any additional intervention on our side.

Create a replication slot in primary server

SELECT * FROM pg_create_physical_replication_slot('it_rdbms02');

Make the additional changes in postgresql.conf file

wal_level = hot_standby
max_replication_slots = 3
max_wal_senders = 311
  •  First line tells the primary server that replicas will be working in hot_standby mode, meaning that it should send data without delay (streaming replication).
  • In the second line I’ve set maximal number of replication slots to 3 although we will use only one for now.
  • The third line defines maximal number of concurrent connections from standby servers.

Restart primary server to apply the changes

sudo systemctl restart postgresql-11

WAL Archiving

This section is only for those who decided to go with WAL archiving!

Below are the parameters to be used for configuring wal archiving

wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /path_to/archive/%f'

Restart primary server to apply the changes

sudo systemctl restart postgresql-11

Configuring Standby Server

Stop the standby server and remove the data directory

sudo -i -u postgres
cd /var/lib/pgsql/11/
rm -rf data

Execute pg_basebackup command in order to get initial state from primary server.

pg_basebackup -v -D data -R -P -h 10.1.10.150 -p 5433 -U replication

Add / change the following line in postgresql.conf file:

hot_standby = on
hot_standby_feedback = on
  • hot_standby tells standby server that it will be used for read-only queries (load balancing).
  • hot_standby_feedback prevents rows from being deleted too early from a slave’s point of view. The idea is to inform the master about the oldest transaction ID on the slave so that VACUUM can delay its cleanup action for certain rows.

recovery.conf for Replication Slots

This section is only for those who decided to go with replication slots technology.

We need to create / change recovery.conf file from data directory so that it contains the following:

Add / change the following line in postgresql.conf file:

$ vi /var/lib/pgsql/11/data/recovery.conf
standby_mode = 'on'
primary_slot_name = 'it_rdbms02'
primary_conninfo = 'host=10.1.10.150 port=5433 user=replication password=reppassword'
trigger_file = '/var/lib/pgsql/11/data/im_the_master'

Explanations:

  • The first line specifies that the server should be started as a standby;
  • The second line tells the server that replication slots will be used, and the slot name it should use (must be the same as defined above while creating the slot);
  • The third line represents a connection string which is used by the standby server to connect with the primary (change IP address, port and password appropriately);
  • The fourth line specifies a trigger file whose presence should cause streaming replication to end – meaning failover. You can define any path and name for a trigger file.Since actual primary server (as defined above) does not contain recovery.conf file, it neither have to contain trigger file in order to be the primary server.

Start standby server.

sudo systemctl start postgresql-11

recovery.conf for WAL Archiving

This section is only for those who decided to go with WAL archiving!

recovery.conf file in this case is similar to one used in replication slots scenario, with few changes:

standby_mode = 'on'
primary_conninfo = 'host=10.1.10.150 port=5433 user=replication password=reppassword'
trigger_file = '/var/lib/pgsql/11/data/im_the_master'
restore_command = 'cp /path_to/archive/%f "%p"'

Note that restore_command must point to the same location as archive_command defined in postgresql.conf file on primary server.

Start standby server.

sudo systemctl start postgresql-11

Testing Replication

We can first check postgresql service status. If the replication is running you should get the following output

# Primary server:
sudo systemctl status postgresql-11 pgsql/11/data (port 5433): online # Standby server: sudo systemctl status postgresql-11 pgsql/11/data (port 5433): online,recovery

In replication the standby server is always in recovery state.

First we’ll create temporary database on the primary server:

sudo -u postgres psql
postgres=# CREATE DATABASE replicationtest;
CREATE DATABASE
postgres=# \l
 List of databases
      Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------+----------+----------+-------------+-------------+-----------------------
 postgres        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 replicationtest | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
(4 rows)

Now you should get the same list of databases on the standby server, by executing:

sudo -u postgres psql
postgres=# \l
  • Manual failover using trigger_file
$ grep "trigger_file" /var/lib/pgsql/11/data/recovery.conf
trigger_file = '/var/lib/pgsql/11/data/im_the_master'
$ touch /var/lib/pgsql/11/data/im_the_master
$ psql -p 5433 -c " select pg_is_in_recovery()"
pg_is_in_recovery
------------------
f
(1 row)

Installing pgpool-II

To use the yum repository, you must first install the repository RPM. To do this, download the correct RPM from the repository RPM listing, and install it with commands like:

yum install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm

Once this is done, you can proceed to install and update the packages from the official repository by using yum.

yum -y install pgpool-II-pg11*

Start the pgpool service

systemctl enable pgpool.service
systemctl start pgpool.service

Extensions and SQL Scripts

pgpool-II comes with some PostgreSQL extensions and SQL scripts. You can find these in source directory (extracted from tarball), in src/sql subdirectory. Original pgpool-II documentation says that they should be compiled, but you shouldn’t do this – it is already covered by installed package

Each extension consists of two files: *.control and *.sql, and to copy an extension we need to copy them both. Besides extensions there are some SQL script files we would also want to copy, but we will copy them to sql subdirectory of PostgreSQL configuration directory (/var/lib/pgsql/11/data/sql)

wget http://www.pgpool.net/download.php?f=pgpool-II-4.0.5.tar.gz
tar -xzvf download.php\?f\=pgpool-II-4.0.5.tar.gz
# Create SQL scripts directory:
mkdir /var/lib/pgsql/11/data/sql
# Navigate to src/sql subdirectory:
/home/ec2-user/pgpool-II-4.0.5/src/sql
# While there let's copy the first script file:
cp insert_lock.sql /var/lib/pgsql/11/data/sql
# Navigate to pgpool_adm (the first extension) subdirectory:
cd pgpool_adm
# Let's copy pgpool_adm extension:
cp pgpool_adm.control /etc/pgpool-II/
cp pgpool_adm--1.0.sql /etc/pgpool-II/
# While there let's copy SQL script file also (note that I'm changing extension of the file also):
cp pgpool_adm.sql.in /var/lib/pgsql/11/data/sql/pgpool_adm.sql
# Navigate up in order to select another extension:
cd ..
# Navigate to pgpool-recovery (the next extension) subdirectory:
cd pgpool-recovery
# Let's copy pgpool-recovery extension:
cp pgpool_recovery.control /etc/pgpool-II/
cp pgpool_recovery--1.1.sql /etc/pgpool-II/
# While there let's copy SQL script files also 
cp pgpool-recovery.sql.in /var/lib/pgsql/11/data/sql/pgpool-recovery.sql
cp uninstall_pgpool-recovery.sql /var/lib/pgsql/11/data/sql/
# Navigate up in order to select another extension:
cd ..
# Navigate to pgpool-regclass (the next extension) subdirectory:
cd pgpool-regclass
# Let's copy pgpool-regclass extension:
cp pgpool_regclass.control /etc/pgpool-II/
cp pgpool_regclass--1.0.sql /etc/pgpool-II/
# While there let's copy SQL script files also 
cp pgpool-regclass.sql.in /var/lib/pgsql/11/data/sql/pgpool-regclass.sql
cp uninstall_pgpool-regclass.sql /var/lib/pgsql/11/data/sql/
 
# Navigate to your home directory and you can delete pgpool source directory since we've copied everything we need:
cd
rm -r pgpool-II-4.0.5

Edit pgpool-recovery.sql file

CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text, text)
RETURNS bool
AS '/usr/pgsql-11/lib/pgpool-recovery', 'pgpool_recovery'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS '/usr/pgsql-11/lib/pgpool-recovery', 'pgpool_remote_start'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '/usr/pgsql-11/lib/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS '/usr/pgsql-11/lib/pgpool-recovery', 'pgpool_switch_xlog'
LANGUAGE C STRICT;

We will have all directories and files in /etc/postgresql owned by postgres user, so we’ll execute:

chown postgres:postgres -R /var/lib/pgsql/11/data/sql

Create Extensions

# Navigate to SQL scripts directory:
cd /var/lib/pgsql/11/data/sql
# Execute scripts:
sudo -u postgres psql -p 5433 -f pgpool-recovery.sql template1
sudo -u postgres psql -p 5433  -f pgpool_adm.sql template1

Or the same thing by using extensions:

sudo -u postgres psql template1
=# CREATE EXTENSION pgpool_recovery;
=# CREATE EXTENSION pgpool_adm;
=# \q

Preparing Scripts

pgpool-II is able to decide when the failover should be performed, but it actually doesn’t know how to perform it. For this reason we need to create failover script that will be used by pgpool-II to actually perform the failover.

failover.sh

As the name implies, this script should perform failover. As we already know, it is easy to do – we should simply create a trigger file on the server which should takeover primary role.

#!/bin/bash
 
if [ $# -ne 5 ]
then
    echo "failover falling_node oldprimary_node new_primary replication_password trigger_file"
    exit 1
fi
 
FALLING_NODE=$1         # %d
OLDPRIMARY_NODE=$2      # %P
NEW_PRIMARY=$3          # %H
REPL_PASS=$4
TRIGGER_FILE=$5
 
echo "failover.sh FALLING_NODE: ${FALLING_NODE}; OLDPRIMARY_NODE: ${OLDPRIMARY_NODE}; NEW_PRIMARY: ${NEW_PRIMARY}; at $(date)\n" >> /var/lib/pgsql/11/data/replscripts/exec.log
 
if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
    if [ $UID -eq 0 ]
    then
        sudo -u postgres ssh -T postgres@$NEW_PRIMARY /var/lib/pgsql/11/data/replscripts/promote.sh -f -p $REPL_PASS -d $OLDPRIMARY_NODE
    else
        ssh -T postgres@$NEW_PRIMARY /var/lib/pgsql/11/data/replscripts/promote.sh -f -p $REPL_PASS -d $OLDPRIMARY_NODE
    fi
    exit 0;
fi;
 
exit 0;

The last thing to do is to ensure file ownership and permissions:

chown postgres:postgres /etc/pgpool-II/failover.sh
chmod 0700 /etc/pgpool-II/failover.sh

recovery_1st_stage.sh

  • Standby recovery script should be specified in recovery_1st_stage_command key of pgpool.conf file. It is explained below.
  • recovery_1st_stage_command script is not customisable  in terms of input arguments. There are few input arguments, and all of them are predetermined.
  • According to the documentation recovery_1st_stage_command must reside in PostgreSQL data directory (/var/lib/postgresql/9.5/main), for security reasons.
  • Current primary server is not specified by input arguments. I’ve spent significant time trying to understand how to get this information within the script, until we will realized that the script always executes on the primary server, so we can get primary server hostname by querying $HOSTNAME environment variable.
vi /var/lib/pgsql/11/data/recovery_1st_stage.sh

 

#!/bin/bash
if [ $# -ne 3 ]
then
    echo "recovery_1st_stage datadir remote_host remote_datadir"
    exit 1
fi
 
PGDATA=$1
REMOTE_HOST=$2
REMOTE_PGDATA=$3
 
PORT=5433
 
echo "recovery_1st_stage.sh - PGDATA: ${PGDATA}; REMOTE_HOST: ${REMOTE_HOST}; REMOTE_PGDATA: ${REMOTE_PGDATA}; at $(date)\n" >> /var/lib/pgsql/11/data/replscripts/exec.log
 
hostnamelower=$(echo "$HOSTNAME" | tr '[:upper:]' '[:lower:]')
remotelower=$(echo "$REMOTE_HOST" | tr '[:upper:]' '[:lower:]')
 
if [ "$hostnamelower" = "$remotelower" ]; then
	echo "Cannot recover myself."
	exit 1
fi
 
echo "Checking if primary info file exists..."
if [ ! -f /var/lib/pgsql/11/data/primary_info ]; then
	echo "Primary info file not found."
	exit 1
fi
 
echo "Reading additional data from primary info file..."
source /var/lib/pgsql/11/data/primary_info
 
if [ ! -e $TRIGGER_FILE ]; then
	echo "Trigger file not found."
	exit 1
fi
 
if [ -e $STANDBY_FILE ]; then
	echo "Standby file found."
	exit 1
fi
 
if [ $UID -eq 0 ]
then
	sudo -u postgres ssh -T postgres@$REMOTE_HOST /var/lib/pgsql/11/data/replscripts/initiate_replication.sh -f -t $TRIGGER_FILE -s $STANDBY_FILE -H $HOSTNAME -P $PORT -u $REPL_USER -p $REPL_PASSWORD
else
	ssh -T postgres@$REMOTE_HOST /var/lib/pgsql/11/data/replscripts/initiate_replication.sh -f -t $TRIGGER_FILE -s $STANDBY_FILE -H $HOSTNAME -P $PORT -u $REPL_USER -p $REPL_PASSWORD
fi
 
exit 0;

Configuring pgpool-II

We need to slightly change PostgreSQL’s main configuration file, so at the end of postgresql.conf file add the following line

pgpool.pg_ctl = '/usr/pgsql-11/bin/pg_ctl'

pcp.conf

This file is used by pgpool-II control interface for authentication, meaning that in this file you’ll specify who can access pgpool-II control interface.

vi /etc/pgpool-II/pcp.conf

The next thing to do is to add one or more lines in the following format:

username:[password encrypted in md5]

where username should be replaced with actual username, and part in square brackets with md5 encrypted password.

Here is an example: We’ll create user “admin” with password “pa55w0rd”. The first thing We’ll do is to md5 encrypt the password by executing:

pg_md5 pa55w0rd
97bf34d31a8710e6b1649fd33357f783

The second line is the result, of course. Now we’ll use this result and add the following line to pcp.conf file:

admin:97bf34d31a8710e6b1649fd33357f783

Although the user set here doesn’t have to be PostgreSQL user (admin used here is not a PostgreSQL user), if you want to use some superuser features in pgpoolAdmin later, you need to set PostgreSQL superuser here (i.e. postgres), and set the same password as used in PostgreSQL. Actually, since you can set multiple users, it might be a good idea to set both users here, and then select the one that will be used to access pgpoolAdmin based on the task that should be performed.

If we have  add postgres user here (or some other superuser), make following changes to pg_hba.conf file

host all postgres 10.1.10.150/32 trust
host all postgres 10.1.10.155/32 trust

pgpool.conf

Let’s start with connection settings:

listen_addresses = '*'
port = 5432
socket_dir = '/var/run/postgresql'

pgpool communication manager connection settings:

pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'

In backend connection settings we’ll actually specify our PostgreSQL instances:

backend_hostname0 = 'IT-RDBMS01'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/11/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'IT-RDBMS02'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/11/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

Configuration option that is worthwhile noting here is backend_weight (0 and 1). It is used in load balancing, and allows you to specify how the load should be distributed. For example, if you set backend_weight1 = 0, the second node won’t be used in load balancing at all. If you, for example, want the first node to get twice as many queries than the second node, you can specify backend_weight0 = 2 and backend_weight1 = 1. Effectively it means that about 66.7% of the queries will be sent to the first node, and about 33.3% to the second. Nevertheless, don’t forget that only read-only queries are subjected to load balancing. All write queries have to be sent to the primary node anyway.

In FILE LOCATIONS section of the file we’ll set:

pid_file_name = '/var/run/postgresql/pgpool.pid'

In REPLICATION MODE section of the file we’ll leave default values. Recheck the following:

replication_mode = off

In LOAD BALANCING MODE section we’ll leave defaults, ensuring that:

load_balance_mode = on

In MASTER/SLAVE MODE once again we’ll leave default values. Ensure that:

master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'postgres'
sr_check_password = 'changeit'pid_file_name = '/var/run/postgresql/pgpool.pid'

In HEALTH CHECK section of the file set the following:

helth_check_period = 5
health_check_timeout = 0
helth_check_user = 'postgres'
health_check_password = 'p0579r35'

In FAILOVER AND FAILBACK section put the following:

failover_command = '/etc/pgpool-II/failover.sh %d %P %H myreplicationpassword /var/lib/pgsql/11/data/im_the_master'load_balance_mode = on

In ONLINE RECOVERY section of the file put the following:

recovery_user = 'postgres'
recovery_password = 'pa55w0rd'
recovery_1st_stage_command = 'recovery_1st_stage.sh'
recovery_2nd_stage_command = ''

In WATCHDOG section of the file ensure:

use_watchdog = on
trusted_servers = 'DC1,DC2'ode = off

There is more to set in the same section:

wd_hostname = 'IT-RDBMS01'
wd_port = 9000
wd_priority = 2trusted_servers = 'DC1,DC2'ode = off

We will repeat the same settings for my other host (IT-RDBMS02):

wd_hostname = 'IT-RDBMS02'
wd_port = 9000
wd_priority = 1pid_file_name = '/var/run/postgresql/pgpool.pid'

And there’s more to set in the same section:

wd_ipc_socket_dir = '/var/run/postgresql'
delegate_IP = '10.1.10.159'

But no, it’s not over yet. There’s more to set in this very section:

wd_lifecheck_method = 'heartbeat'
wd_interval = 3
wd_heartbeat_port = 9694

Heartbeat settings also requires us to set the other pgpool, and it will be different on different nodes, of course. In my case, on IT-RDBMS01 it will be:

heartbeat_destination0 = 'IT-RDBMS02'
heartbeat_destination_port0 = 9694

and on IT-RDBMS02 it will be:

heartbeat_destination0 = 'IT-RDBMS01'
heartbeat_destination_port0 = 9694

Still in the same section we also need to set other pgpool-II settings. In case of IT-RDBMS01 host:

other_pgpool_hostname0 = 'IT-RDBMS02'
other_pgpool_port0 = 5432
other_wd_port0 = 9000

and similarly on IT-RDBMS02:

other_pgpool_hostname0 = 'IT-RDBMS01'
other_pgpool_port0 = 5432
other_wd_port0 = 9000

Additional Packages

Run the following command:

sudo yum install php php-mysql php-devel php-gd php-pecl-memcache php-pspell php-snmp php-xmlrpc php-xml php5-pgsql iputils iputils-arping
yum install phpPgAdmin httpd

In order to be able to manipulate virtual IP pgpool-II needs to be able to execute ip and arping commands. But there’s a catch: these commands are requiring root access, and as you might remember pgpool-II service runs under postgres user which doesn’t have root permissions. It is true that we could let the service run as root, but again it wouldn’t solve the problem – since we’ll install and use pgpoolAdmin (which runs under Apache), www-data user also needs to be able to execute these commands.

sudoers file

The first thing w’ll do is to allow postgres and www-data users to sudo execute these commands without being prompted for root password.We can also accomplish this by adding the following lines to sudoers file:

postgres ALL=(root) NOPASSWD: /bin/ip
www-data ALL=(root) NOPASSWD: /bin/ip
postgres ALL=(root) NOPASSWD: /usr/bin/arping
www-data ALL=(root) NOPASSWD: /usr/bin/arping

Command Wrappers

The next thing we’ll do is creating simple wrapper scripts for mentioned commands.

/bin/ip_w

#!/bin/bash
# Wraps ip command
 
if [ $UID -eq 0 ]
then
        #echo "Executing: /bin/ip $@"
        /bin/ip $@
else
        #echo "Executing: sudo /bin/ip $@"
        sudo /bin/ip $@
fi
 
exit 0

/usr/bin/arping_w

#!/bin/bash
# Wraps arping command
 
if [ $UID -eq 0 ]
then
        #echo "Executing: /usr/bin/arping $@"
        /usr/bin/arping $@
else
        #echo "Executing: sudo /usr/bin/arping $@"
        sudo /usr/bin/arping $@
fi
 
exit 0
chmod 0755 /bin/ip_w
chmod 0755 /usr/bin/arping_w

After that you can confirm that postgres user is able to sudo-execute the commands without being prompted for password:

root@IT-RDBMS01:~# sudo -i -u postgres
postgres@IT-RDBMS01:~$ ip_w a
Executing: sudo /bin/ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN group default
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0:  mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:15:5d:05:05:20 brd ff:ff:ff:ff:ff:ff
    inet 10.1.10.150/16 brd 10.1.255.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::215:5dff:fe05:520/64 scope link
       valid_lft forever preferred_lft forever
postgres@IT-RDBMS01:~$ logout
root@IT-RDBMS01:~#,multicast,up,lower_up>,up,lower_up>

 Finishing pgpool-II Configuration

Add following values in values in pgpool.conf file:

if_cmd_path = '/bin'
if_up_cmd = 'ip_w addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip_w addr del $_IP_$/24 dev eth0'
arping_path = '/usr/bin'
arping_cmd = 'arping_w -U $_IP_$ -w 1'

Authentication, Access Control and pool_hba.conf

There are few things we need to do, so let’s start by setting the following in pgpool.conf file:

enable_pool_hba = on
pool_passwd = 'pool_passwd'

Next we’ll create pool_hba.conf file, by copying from template

vi /etc/pgpool-II/pool_hba.conf

For the purpose of this tutorial We’ll only add one line that allows all users to access all databases from your network (10.1.0.0/16) by using md5 authentication:

host all all 10.1.0.0/16 md5

In order to enable md5 authentication we have to create pool_passwd file. Path and the name of the file are specified in pgpool.conf file

Entries in pool_passwd file should be created in the following way:

pg_md5 -f /etc/pgpool-II/pgpool.conf -m -u postgres postgrespassword

We have added user postgres with password postgrespassword to pool_passwd file.

The command will execute without any command output – it adds user and password to pool_passwd file automatically (if the file doesn’t exist it’ll be created). As you can see it also requires path to pgpool.conf file as input argument (obviously to read from there where pool_passwd file is). After the execution you can check /etc/pgpool-II/pool_passwd file content, and you’ll find something like the following there:

postgres:md55cb5bf77d7027e6c4e50fa4112df4d63

 Starting pgpool-II

Now we can enable and start the service:

service start pgpool2
service enable pgpool2
  • service pgpool2 status should report that the service is running on both machines;
  • ifconfig -a should show that one machine has additional IP address (virtual IP) labeled with eth0:0;
  • You should be able to connect to pgpool-II from any other server by using virtual IP and port 5432. You can try with pgAdmin3 for example.

Installing pgpoolAdmin

pgpool.conf and pcp.conf File Permissions

chown www-data /etc/pgpool-II/pgpool.conf
chown www-data /etc/pgpool-II/pcp.conf

.pcppass File

This file is needed by pgpoolAdmin for authentication for pcp commands.

.pcppass file is very similar to .pgpass file

Format of .pcppass file entries is:

hostname:port:username:password

our case we’ll create .pcppass file as:

*:*:admin:pa55w0rd

You can read this as: all hosts, all ports, user admin, password pa55w0rd.

  • Username / password combination must match to one used while creating pcp.conf file, with a difference that in pcp.conf file we need to use md5 hash of the password, while in .pcppass file we need to use password in plain text.
  • .pcppass file should be placed in user’s home directory. Actually, it is possible to place the file wherever you want and specify its location in an environment variable
  • When the file is used by pgpoolAdmin portal (we are doing right now), it should be placed in home directory of the user account under which Apache runs, meaning in www-data user’s home directory. Home directory of www-data user is /var/www, so we need to place .pcppass file there.

Installing the Portal

wget http://www.pgpool.net/download.php?f=pgpoolAdmin-4.0.1.tar.gz

Extract the file

tar -xzf pgpoolAdmin-4.0.1.tar.gz

Move extracted archive to the new location (under Apache root directory)

mv pgpoolAdmin-4.0.1 /var/www/html/pgpooladmin

Change ownership of the directory

chown root:root -R /var/www/html/pgpooladmin

chmod 0777 /var/www/html/pgpooladmin/templates_c
chown www-data /var/www/html/pgpooladmin/conf/pgmgt.conf.php
chmod 0644 /var/www/html/pgpooladmin/conf/pgmgt.conf.php

Make sure that the webserver(apache) is up and running

Navigate to http://it-rdbms01/pgpooladmin/install/phpinfo.php (you should change hostname appropriately).

Once you’ve ensured that everything is ok with PHP and Apache, start installation wizard by opening http://it-rdbms01/pgpooladmin/install/index.php (again change the host). You should get something like:

image1.jpeg

If you’ve used different directories during pgpool-II installation process – you’ll have to appropriately change the values

After changing the values click “Check” button again and you should get all greens. Click “Next”.

image2.jpeg

As you can see, we should now delete /var/www/html/pgpooladmin/install directory, so do it. Click “top” button, and you’ll end up on a login page.

Enter username / password (we have defined while creating pcp.conf file )

login by using postgres user (recommended)

 

image4.jpeg

 

 

 

 

 

 

 

 

 

 

 

4 thoughts on “PostgreSQL High Availability using PgPool II

Add yours

  1. cp pgpool_adm.sql.in /var/lib/pgsql/11/data/sql/pgpool_adm.sql

    ls -la
    /opt/pgpool-II-4.0.5/src/sql/pgpool_adm/
    drwxrwxr-x. 2 1000 1000 174 May 15 09:08 .
    drwxrwxr-x. 5 1000 1000 109 May 15 09:08 ..
    -rw-rw-r–. 1 1000 1000 846 May 15 09:08 Makefile
    -rw-rw-r–. 1 1000 1000 798 May 15 09:08 pgpool_adm–1.0–1.1.sql
    -rw-rw-r–. 1 1000 1000 2574 May 15 09:02 pgpool_adm–1.0.sql
    -rw-rw-r–. 1 1000 1000 2653 May 15 09:08 pgpool_adm–1.1.sql
    -rw-rw-r–. 1 1000 1000 14709 May 15 09:08 pgpool_adm.c
    -rw-rw-r–. 1 1000 1000 146 May 15 09:08 pgpool_adm.control
    -rw-rw-r–. 1 1000 1000 851 May 15 09:08 pgpool_adm.h

    There is no such file like pgpool_adm.sql.in in pgpool-II-4.0.5/src/sql/pgpool_adm

    Like

  2. Hi,
    Thanks For Document, i’m struggling to form a promote.sh, initiate_replication.sh script, Can you please share the sample script for promote.sh, initiate_replication.sh . It will help me.

    Thanks in advance .

    Like

  3. “cp pgpool_adm.sql.in /var/lib/pgsql/11/data/sql/pgpool_adm.sql”
    There is no such file as pgpool_adm.sql.in

    Files in the directory are:

    [root@pgpool1 pgpool_adm]# ls
    Makefile pgpool_adm–1.0–1.1.sql pgpool_adm–1.0.sql pgpool_adm–1.1–1.2.sql pgpool_adm–1.1.sql pgpool_adm–1.2.sql pgpool_adm.c pgpool_adm.control pgpool_adm.h
    [root@pgpool1 pgpool_adm]#

    Like

  4. I’m very pleased to uncover this great site. I wanted to thank you for your time for this fantastic read!! I definitely enjoyed every bit of it and I have you bookmarked to see new information in your website.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: