Fault tolerant PostgreSQL cluster with automatic failover using Repmgr and Barman. Part 1. Introduction.


Part 1. Introduction.
Part 2. Replication. Repmgr.
Part 3. Auto-failover. Repmgr daemons.
Part 4. HA. Backup. Repmgr daemons. Barman.
Part 5. Testing. Ansible. Final thoughts.

Right off the bat, this is what you’ll get in the end:

  • Synhronous standby server using repmgr
  • Repmgr daemons running not on db servers
    • No need of witness server with own PostgreSQL instance
    • Split-brain tolerant auto-failover
    • High available configuration without additional utils, such as HAProxy
  • Backup server using barman
    • Asynchronous WAL streaming
    • Became synchronous, if standby will fall. At least two nodes in cluster work synchronously at any time.
    • Using high availability feature to store master and standby backups in the same place and save disk space
    • Using barman as unified WAL storage and restore point
  • Ansible playbook to automate deploying

Software

  • Debian 9.7
  • PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1)
  • Repmgr 4.2
  • Barman 2.7
  • OpenVPN 2.4.0

Topology


Run-up

First, add necessary repos and install PostgreSQL on all servers.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)"-pgdg main | tee  /etc/apt/sources.list.d/pgdg.list
apt update
apt install postgresql-11
update-rc.d postgresql enable
systemctl restart postgresql

Change password for postgres user on db1 and db2 servers. We will need it in Part 3.

passwd postgres

Second, add 2ndQuadrant(repmgr and barman maintainer) repos on all servers.

apt install curl apt-transport-https
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 | apt-key add -
apt update

Now, install following utils on all servers. barman-cli is a set of scripts helping us to archive and retrieve WALs from barman remotely and safely.

apt install postgresql-11-repmgr barman-cli sudo

Install barman on backup server.

apt install barman

Change password for barman user on backup server. We will need it in Part 4.

passwd barman

Join servers (optionally)

To keep our replication and backup processes safe, we need to join our servers together using secure connection.
You are probably no need it, if all of your servers already joined using secure data center’s local network. However, I’m not recommending you to keep all eggs in one basket.
Anyway, just keep in mind you should replace following IPs with your ones anywhere you will see them.

10.8.1.1
10.8.2.1
10.8.3.1

OK, let’s join our servers using simple point-to-point OpenVPN connection.
First, install OpenVPN binaries and generate server key.

apt install openvpn
openvpn --genkey --secret /etc/openvpn/server.key

Second, copy server key to client remotely.
From db1 to db2 server.

scp /etc/openvpn/server.key root@db2_ip_or_host:/etc/openvpn/client.key

From db2 to backup server.

scp /etc/openvpn/server.key root@backup_ip_or_host:/etc/openvpn/client.key

From backup to db1 server.

scp /etc/openvpn/server.key root@db1_ip_or_host:/etc/openvpn/client.key

Next, create server and client configs.
db1
/etc/openvpn/server.conf

dev tun
ifconfig 10.8.1.1 10.8.2.1
secret server.key
cipher AES-256-CBC
keepalive 3 9
ping-timer-rem

/etc/openvpn/client.conf

remote backup_ip_or_host
dev tun
ifconfig 10.8.1.1 10.8.3.1
secret client.key
cipher AES-256-CBC
keepalive 3 18
nobind

db2
/etc/openvpn/server.conf

dev tun
ifconfig 10.8.2.1 10.8.3.1
secret server.key
cipher AES-256-CBC
keepalive 3 9
ping-timer-rem

/etc/openvpn/client.conf

remote db1_ip_or_host
dev tun
ifconfig 10.8.2.1 10.8.1.1
secret client.key
cipher AES-256-CBC
keepalive 3 18
nobind

backup
/etc/openvpn/server.conf

dev tun
ifconfig 10.8.3.1 10.8.1.1
secret server.key
cipher AES-256-CBC
keepalive 3 9
ping-timer-rem

/etc/openvpn/client.conf

remote db2_ip_or_host
dev tun
ifconfig 10.8.3.1 10.8.2.1
secret client.key
cipher AES-256-CBC
keepalive 3 18
nobind

Reload configs and restart OpenVPN.

update-rc.d openvpn enable
systemctl daemon-reload
systemctl restart openvpn

Excellent! Now we are ready to setup replication via repmgr.

Leave a Reply

Your email address will not be published. Required fields are marked *