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, master 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

Install PostgreSQL and Repmgr:

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' >> /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-10
sudo sh -c 'echo "deb https://apt.2ndquadrant.com/ $(lsb_release -cs)-2ndquadrant main" > /etc/apt/sources.list.d/2ndquadrant.list'
curl https://apt.2ndquadrant.com/site/keys/9904CD4BD6BAF0C3.asc | sudo apt-key add -
sudo apt-get update
apt-get install postgresql-10-repmgr

Let's assume that we have 2 servers - a master and a standby.Their IPs are listed below:

  • Master (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.

# Generate id_rsa ssh key
ssh-keygen
# Paste ssh key id_rsa.pub on other servers
vim .ssh/authorized_keys

Then test the SSH connection. You should be able to log in to each server from the other one without a password.

# On Master
ssh 127.0.0.2
# On Standby
ssh 127.0.0.1

Edit postgresql.conf file in both master and standby with the following changes, so that in future, standby acts as master with minimal changes.

sudo vi /etc/postgresql/10/main/postgresql.conf
listen_address = '*'
shared_preload_libraries = 'repmgr'
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
max_wal_senders = 5
wal_keep_segments = 64
max_replication_slots = 5
hot_standby = on

Please make sure that the folder used in archive_command exists in your server.

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 master and standby with the following changes:

sudo vi /etc/postgresql/10/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 Master
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/10/main'
use_replication_slots = 1
reconnect_attempts=5
reconnect_interval=1
failover=automatic
pg_bindir='/usr/lib/postgresql/10/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

In order to use automatic failover with Repmgrd, update the /etc/default/repmgrd file and restart Repmgrd with sudo service repmgrd restart

REPMGRD_ENABLED=yes
REPMGRD_CONF="/etc/repmgr.conf"

Now restart Repmgrd using sudo service repmgrd restart.

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 master node in Repmgr on master.

repmgr primary register

Then you can check the new cluster with repmgr cluster show.

Now configure the standby server.

sudo service postgresql stop
sudo service repmgrd stop
sudo su postgres
rm -rf /var/lib/postgresql/10/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 Repmgr cluster show and you should see your master node as well as your new standby node.

Failover
In order to test the failover and that Repmgrd is working, you can stop the PostgreSQl server on your master. Now the standby will take over as the new master.

If you need to bring back the old master as the new standby, run the following commands:

sudo su postgres
rm -rf /var/lib/postgresql/10/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 register

Now the old master becomes the new standby.

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 Loraserver 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. My advice to you if only have a Java/Spring application is to just use Repmgr for replication and pgbouncer for load balancing and skip Pgpool2, which is way too complex. I am using it only because I don't have another option.

Setup

sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'
sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | >   sudo apt-key add -
sudo apt-get update
# Check your os version before running the below command using lsb_relase -a
sudo apt-get install pgpool2=3.7.4-1.pgdg16.04+1

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/10/main/'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '127.0.0.2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/10/main/'
backend_flag1 = 'ALLOW_TO_FAILOVER'

load_balance_mode = on

master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_user = 'pgpool'
sr_check_database = 'pgpool'

health_check_period = 30
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = ''
health_check_database = 'pgpool'
health_check_max_retries = 5
health_check_retry_delay = 20
connect_timeout = 10000

fail_over_on_backend_error = off

use_watchdog = on
wd_hostname = '127.0.0.1'
wd_port = 9999

delegate_IP = '192.168.1.172'
if_cmd_path = '/usr/bin'
if_up/down_cmd exists 
if_up_cmd = 'sudo ip addr add 192.168.1.172 dev eth0 label eth0:0'
if_down_cmd = 'sudo ip addr del 192.168.1.172 dev eth0 label eth0:0'
arping_path = '/usr/bin'
arping_cmd = 'sudo arping -U 192.168.1.172 -w 1'

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

Create Pgpool user and Pgpool database.

sudo su postgres
psql 
create user pgpool;
create database pgpool;
GRANT ALL PRIVILEGES ON DATABASE "pgpool" to pgpool;

Edit pg_hba.conf

sudo vi /etc/postgresql/10/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.

Watchdog delegate ip works only if all the pgpool instances are in the same subnet.

Pgpool requires root access to work, if it uses Watchdog. This is to run the ip addr command and arping commands. You could run Pgpool as root, but that is not a good practice. Instead do this.

sudo visudo
# Cmnd alias specification
Cmnd_Alias IP_ADDR_CMD = /sbin/ip
Cmnd_Alias ARPING_CMD= /usr/sbin/arping

postgres  ALL=(ALL) NOPASSWD: IP_ADDR_CMD, ARPING_CMD

Now Pgpool can run arping and ip addr commands without specifying root password.

It's also possible that arping is not installed in your system. If that's the case, please install it using:

sudo apt-get install arping

Now restart Pgpool

sudo service pgpool2 restart

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 master server. The read requests meanwhile will be load balanced between the master and standby servers. So you don't need multiple IPs in your connection string.

That's it! If you face any issues while setting up replication for PostgreSQL, please feel free to contact me.