PostgreSQL - Replication and High Availability
We were using MySQL as database for Teckportal till now, whereas the Open-source software we use for Teckportal known as loraserver uses PostgreSQL. Maintaining different databases and deploying them with replication and high availability is a big problem. So we decided to switch Teckportal to PostgreSQL as well.
Since we are using liquibase, we didn't have too many problems with migration of tables because liquibase took care of it. The actual table data from MySQL
was exported as CSV and imported into tables in PostgreSQL.
Replication
Repmgr is the tool which I used for replication. It enhances PostgreSQL's built-in hot-standby capabilities with tools to set up standby servers, monitor replication, and perform administrative tasks such as failover or manual switchover operations. Repmgr uses the built in streaming replication of PostgreSQL.
Please note that in PostgreSQL replication, primary node has read and write functionality, whereas the standby node is read only.
Setup
As a first step, remove all the postgresql leftovers from the system:
sudo apt-get --purge remove postgresql-*
sudo rm -Rf /etc/postgresql /var/lib/postgresql
Note: The server I am using has ubuntu 20.04 instaled.
Install PostgreSQL12:
deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-12
Install Repmgr 5.1:
curl https://dl.2ndquadrant.com/default/release/get/deb | sudo bash
sudo apt-get install postgresql-12-repmgr
Let's assume that we have 2 servers - a primary and a standby.Their IPs are listed below:
- Primary (127.0.0.1)
- Standby (127.0.0.2)
After installation use the postgres account to create a specific user and database for Repmgr.
sudo -i -u postgres
createuser --replication --createdb --createrole --superuser repmgr
psql -c 'ALTER USER repmgr SET search_path TO repmgr_test, "$user", public;'
createdb repmgr --owner=repmgr
Now we should configure the SSH access between each servers.
(On Primary)
# Generate id_rsa ssh key
ssh-keygen
# Copy ssh keys to other servers
ssh-copy-id root@127.0.0.2
(On Standby)
# Generate id_rsa ssh key
ssh-keygen
# Copy ssh keys to other servers
ssh-copy-id root@127.0.0.1
Then test the SSH connection. You should be able to log in to each server from the other one without a password.
# On Primary
ssh 127.0.0.2
# On Standby
ssh 127.0.0.1
Edit postgresql.conf file in both primary and standby with the following changes, so that in future, standby acts as primary with minimal changes.
sudo vi /etc/postgresql/12/main/postgresql.conf
listen_address = '*'
shared_preload_libraries = 'repmgr'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 64
max_replication_slots = 5
hot_standby = on
wal_log_hints = on
Also note that the settings like max_wal_senders, wal_keep_segments etc are dependent on your server. The web page gives you optimal values for these properties as per your server configuration.
https://pgtune.leopard.in.ua/#/
Now edit pg_hba.comf in both primary and standby with the following changes:
sudo vi /etc/postgresql/12/main/pg_hba.conf
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 127.0.0.2/32 trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 127.0.0.2/32 trust
Restart Postgresql sudo service Postgresql restart and test the connection on each servers with the following commands:
# On Primary
sudo su postgres
psql 'host=127.0.0.2 dbname=repmgr user=repmgr'
# On Standby
sudo su postgres
psql 'host=127.0.0.1 dbname=repmgr user=repmgr'
Now the PostgreSQL servers on both the servers are ready for clustering.
Cluster creation
Now it's time to create the Repmgr cluster. Do this on both the servers.
Use host=127.0.0.2, node_id=1 and node_name = 'node1' on the first server and host=127.0.0.1, node_id=2 and node_name = 'node2' on the second server.
sudo vi /etc/repmgr.conf
node_id = 1
node_name = 'node1'
conninfo = 'host=127.0.0.2 user=repmgr dbname=repmgr'
data_directory = '/var/lib/postgresql/12/main'
use_replication_slots = yes
reconnect_attempts = 5
reconnect_interval = 1
failover = manual
pg_bindir = '/usr/lib/postgresql/12/bin'
promote_command = 'repmgr standby promote -f /etc/repmgr.conf'
follow_command = 'repmgr standby follow -f /etc/repmgr.conf'
log_level = INFO
log_file = '/var/log/postgresql/repmgr.log
If your firewall/ufw is on, make sure that you allow connections to and from both the servers on postgres port.
Cluster registration
Now it’s time to register the primary node in Repmgr on primary.
repmgr primary register
Then you can check the new cluster with repmgr cluster show.
Now configure the standby server.
sudo service postgresql stop
sudo su postgres
rm -rf /var/lib/postgresql/12/main
repmgr -h 127.0.0.1 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone
exit
sudo service postgresql start
sudo su postgres
repmgr -f /etc/repmgr.conf standby register
If everything went well, then congratulations, you have your Postgresql cluster running!
You can check the cluster status with the following command and you should see your primary node as well as your new standby node.
repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------
1 | node1 | primary | * running | | default | 100 | 1 | host=127.0.0.1 user=repmgr dbname=repmgr
2 | node2 | standby | running | node1 | default | 100 | 1 | host=127.0.0.2 user=repmgr dbname=repmgr
Java connection string
The syntax for the connection url is:
jdbc:postgresql://host1:port1,host2:port2/database
For more details visit: https://jdbc.postgresql.org/documentation/head/connect.html
Load Balancing and High Availability
For Java and Spring, the method stated earlier is more than enough. But chirpstack uses Golang and does not support multiple IPs in connection strings. So I decided to use Pgpool2 which provides high availability as well as load balancing.
Setup
Follow the below instructions on both primary and standby servers. Only change wdhostname , heartbeatdestination0 and otherpgpoolhostname0 on the standby server.
#Install pgpool2
sudo apt-get install pgpool2
#Install pgpool extensions
sudo apt-get install postgresql-12-pgpool2
Now edit the config file for Pgpool2.
sudo vi /etc/pgpool2/pgpool.conf
listen_addresses = '*'
port = 5433
backend_hostname0 = '127.0.0.1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/12/main/'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'node1'
backend_hostname1 = '127.0.0.2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/12/main/'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'node2'
connection_cache = on
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'repmgr'
sr_check_database = 'repmgr'
delay_threshold = 10000000
health_check_period = 10
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = ''
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 10
connect_timeout = 10000
failover_command = '/opt/pgpool/scripts/failover.sh %d %h %p %D %m %H %M %P %r %R'
fail_over_on_backend_error = off
auto_failback = on
use_watchdog = on
wd_hostname = '127.0.0.1'
wd_port = 9999
wd_heartbeat_port = 9694
heartbeat_destination0 = '127.0.0.2'
heartbeat_destination_port0 = 9694
other_pgpool_hostname0 = '127.0.0.2'
other_pgpool_port0 = 5433
other_wd_port0 = 9999
Please make sure that backend_application_name0 and backend_application_name1 are same as the node_name in repmgr.conf file for each of the servers.
Create Pgpool user and Pgpool database.
sudo su postgres
psql
create user pgpool;
create database pgpool;
GRANT ALL PRIVILEGES ON DATABASE "pgpool" to pgpool;
GRANT pg_monitor TO pgpool;
GRANT pg_monitor TO repmgr;
Edit pg_hba.conf
sudo vi /etc/postgresql/12/main/pg_hba.conf
host pgpool pgpool 127.0.0.1/32 trust
host pgpool pgpool 127.0.0.1/32 trust
Please note that the above setup for pgpool includes watchdog setup.Watchdog is a sub process of Pgpool-II to add high availability for pgpool itself. Watchdog is used to resolve the single point of failure by coordinating multiple Pgpool-II nodes.
*We can setup watchdog with delegate ip and use that ip to connect to postgres. But the delegate ip is a virtual ip and the condition here is that all the pgpool instances are inside the same subnet. This does not work in our case and I am not setting up watchdog with delegate ip here. In my case I am just using watchdog to keep both the pgpool instances in sync.
*
Setup failover sciprt
cd /opt
mkdir pgpool
cd pgpool
mkdir scripts
cd scripts
vi failover.sh
#!/bin/bash
LOGFILE=/var/log/pgpool/failover.log
if [ ! -f $LOGFILE ] ; then
> $LOGFILE
fi
PGVER=${PGVER:-12}
echo $@
# Special values:
# %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %m = new master node id
# %H = hostname of the new master node
# %M = old master node id
# %P = old primary node id
# %r = new master port number
# %R = new master database cluster path
FALLING_NODE=$1 # %d
FALLING_HOST=$2 # %h
FALLING_PORT_NUMBER=$3 # %p
FALLING_CLUSTER_PATH=$4 # %D
NEW_MASTER_ID=$5 # %m
NEW_HOST=$6 # %H
OLD_MASTER_ID=$7 # %M
OLD_PRIMARY_ID=$8 # %P
NEW_PORT=$9 # %r
NEW_CLUSTER_PATH=$10 # %R
(
date
echo "FALLING_NODE: $FALLING_NODE"
echo "FALLING_HOST: $FALLING_HOST"
echo "FALLING_PORT_NUMBER: $FALLING_PORT_NUMBER"
echo "FALLING_CLUSTER_PATH: $FALLING_CLUSTER_PATH"
echo "NEW_MASTER_ID: $NEW_MASTER_ID"
echo "NEW_HOST: $NEW_HOST"
echo "OLD_MASTER_ID: $OLD_MASTER_ID"
echo "OLD_PRIMARY_ID: $OLD_PRIMARY_ID"
echo "NEW_PORT: $NEW_PORT"
echo "NEW_CLUSTER_PATH: $NEW_CLUSTER_PATH"
#ssh_options="ssh -p 22 -n -T -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no"
set -x
if [ $FALLING_NODE = $OLD_PRIMARY_ID ] ; then
ssh root@${NEW_HOST} "sudo -u postgres /usr/lib/postgresql/12/bin/repmgr --log-to-file -f /etc/repmgr.conf standby promote -v"
else
echo old primary id is $OLD_PRIMARY_ID and falling node is $FALLING_NODE
fi
exit 0;
) 2>&1 | tee -a ${LOGFILE}
sudo chowm -R postgres:postgres /opt/pgpool
Enable password less ssh access from root user and postgres user of both the servers:
Server 1
sudo su postgres
ssh-keygen
ssh-copy-id root@127.0.0.1
ssh-copy-id root@127.0.0.2
Server 2
sudo su postgres
ssh-keygen
ssh-copy-id root@127.0.0.1
ssh-copy-id root@127.0.0.2
Now restart Pgpool on both the servers.
sudo service pgpool2 restart
Now check the pgpool status using the following commands
sudo su postgres
psql -h 127.0.0.1 -p 5433 -U pgpool
show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 127.0.0.1 | 5432 | up | 0.500000 | primary | 29 | true | 0 | | | 2020-06-05 11:54:40
1 | 127.0.0.2 | 5432 | up | 0.500000 | standby | 52343 | false | 0 | streaming |async | 2020-06-05 11:54:40
In your connection string you just need to give the Pgpool IP and port. When your application connects to Pgpool, it will send the write requests to PostgreSQL primary server. The read requests meanwhile will be load balanced between the primary and standby servers. So you don't need multiple IPs in your connection string.
Failover
-
What happens in pgpool if primary node fails?
Pgpool will initiate failover. It will execute the script mentioned in failover_command in the pgpool.conf file, which will promote the old standby into new primary. -
What happens if the previous primary node comes back online?
We have to manually attach the old primary as new standby. Run the following commands:sudo service postgresql stop sudo su postgres rm -rf /var/lib/postgresql/12/main repmgr -h 127.0.0.2 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone exit sudo service postgresql start sudo su postgres repmgr -f /etc/repmgr.conf standby registerIf we have set auto_failback as on in pgpool.conf, then pgpool will update its records to set standby as up again.
-
What happens in pgpool if stanby node fails?
Pgpool will update its records and set standby node as down. If the standby node comes back online, if we have set auto_failback as on in pgpool.conf, then pgpool will update its records to set standby as up again.
That's it! If you face any issues while setting up replication for PostgreSQL, please feel free to contact me.