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
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.
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 IP addresses 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.