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.
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.
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.
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.
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
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.
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.
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:
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.
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;
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.
# 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.