PostgreSQL Master-Slave repmgr triple Replication
In this tutorial we're going to setup a triple master-slave postgreSQL replication using repmgr:
The idea of using the aforementionned full mesh topology is in case the master node fails, the slave nodes would elect a new master node amongst themselves. We will first setup our first debian CT on proxmox, setup the latest postgresql version on it and then configure it accordingly to act as the master node.:::
s
Initial setup
Once the CT is created, get in it's console to setup SSH properly:
Debian GNU/Linux 10 debian-psql-1 tty1
debian-psql-1 login: root
Password:
Linux debian-psql-1 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
root@debian-psql-1:~# apt update -y ; apt upgrade -y ; apt install vim -y ; vim /etc/ssh/sshd_config
PermitRootLogin yes
:wq
root@debian-psql-1:~# systemctl restart sshd ; exit
Once that's done login via ssh:
[ 10.0.0.10/16 ] [ /dev/pts/58 ] [~]
→ ssh root@10.0.0.161
The authenticity of host '10.0.0.161 (10.0.0.161)' can't be established.
ED25519 key fingerprint is SHA256:s6HbZSbkMR4DSr8AnFn/owHl82+gQvuomQ++Oe8S6cw.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.161' (ED25519) to the list of known hosts.
root@10.0.0.161's password:
Linux debian-psql-1 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Mon Apr 5 10:28:43 2021
root@debian-psql-1:~#
Now from here we're going to install the latest postgresql:
root@debian-psql-1:~# apt install lsb-release gnupg2 -y
root@debian-psql-1:~# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@debian-psql-1:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
OK
root@debian-psql-1:~# apt update -y
Hit:1 http://security.debian.org buster/updates InRelease
Hit:2 http://ftp.debian.org/debian buster InRelease
Hit:3 http://ftp.debian.org/debian buster-updates InRelease
Get:4 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease [104 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 Packages [219 kB]
Fetched 322 kB in 2s (181 kB/s)
Reading package lists... Done
Building dependency tree
Reading state information... Done
All packages are up to date.
root@debian-psql-1:~# apt install postgresql-13 postgresql-13-repmgr -y
root@debian-psql-1:~# systemctl stop postgresql
Once that's done we're going to clone this CT 4 times to end up with the following topology:
Basically, each of the database server nodes are going to send queries to the pgpooler node and the pgpooler node is going to send them to the master node. So let's first clone our debian machine after we shut it down:
root@debian-psql-1:~# shutdown now
root@debian-psql-1:~# Connection to 10.0.0.161 closed by remote host.
Connection to 10.0.0.161 closed.
[ 10.0.0.10/16 ] [ /dev/pts/58 ] [~]
→
and clone it 4 times:
Once cloned, make sure you set their local IP correctly:
Once that's done, start them all and connect to them via SSH:
Now we want to create a .ssh directory owned by the postgres user, and we will do so FROM THE PGBOUNCER node, the other nodes won't communicate to each other by themselves, it will anyway happen through the pgbouncer node. Before we do that, we're going to install pgbouncer:
root@debian-pgbouncer:~# apt search pgbouncer
Sorting... Done
Full Text Search... Done
hobbit-plugins/stable 20190129 all
plugins for the Xymon network monitor
pgbouncer/buster-pgdg 1.15.0-1.pgdg100+1 amd64
lightweight connection pooler for PostgreSQL
pgbouncer-dbgsym/buster-pgdg 1.15.0-1.pgdg100+1 amd64
debug symbols for pgbouncer
pgstat/buster-pgdg 1.0.1-1.pgdg100+1 amd64
Collects PostgreSQL statistics the same way as a vmstat tool
prometheus-pgbouncer-exporter/stable 1.7-1 all
Export metrics from pgbouncer to Prometheus
root@debian-pgbouncer:~# apt install pgbouncer -y
Once that's done, create pgbouncer's ~/.ssh directory
root@debian-pgbouncer:~# su - postgres
postgres@debian-pgbouncer:~$ ls -lash
total 16K
4.0K drwxr-xr-x 3 postgres postgres 4.0K Apr 5 11:43 .
4.0K drwxr-xr-x 21 root root 4.0K Apr 5 10:50 ..
4.0K -rw------- 1 postgres postgres 32 Apr 5 11:43 .bash_history
4.0K drwxr-xr-x 3 postgres postgres 4.0K Apr 5 10:38 13
postgres@debian-pgbouncer:~$ mkdir ~/.ssh
postgres@debian-pgbouncer:~$ cd ~/.ssh
postgres@debian-pgbouncer:~/.ssh$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/postgresql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/postgresql/.ssh/id_rsa.
Your public key has been saved in /var/lib/postgresql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:jkRXPz3sIgIfhefwluU4HKLzRPBwI0jnxCq0nWQkwhY postgres@debian-pgbouncer
The key's randomart image is:
+---[RSA 2048]----+
|oEoooo*.o.o |
| +..++.==+o..o |
|.. = o+o+B *o + |
| o +.o+..O .o . |
| . .+S.... . |
| . o.. . . |
| . . |
| |
| |
+----[SHA256]-----+
postgres@debian-pgbouncer:~/.ssh$
and we will do the same for the other nodes but from our pgbouncer node:
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo ${i}; done
1
2
3
4
5
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo 10.0.0.16${i}; done
10.0.0.161
10.0.0.162
10.0.0.163
10.0.0.164
10.0.0.165
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo 10.0.0.16${i} ; ssh root@10.0.0.16${i} ; done
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo 10.0.0.16${i} ; ssh root@10.0.0.16${i} "mkdir /var/lib/postgresql/.ssh && chown postgres:postgres /var/lib/postgresql/.ssh" ; done
10.0.0.161
root@10.0.0.161's password:
10.0.0.162
root@10.0.0.162's password:
10.0.0.163
root@10.0.0.163's password:
10.0.0.164
root@10.0.0.164's password:
10.0.0.165
root@10.0.0.165's password:
mkdir: cannot create directory '/var/lib/postgresql/.ssh': File exists
postgres@debian-pgbouncer:~/.ssh$
Now that's done, we're going to make sure each of the 4 servers can be accessed by the pgbouncer node:
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo 10.0.0.16${i} ; scp id_rsa* root@10.0.0.16${i}:/var/lib/postgresql/.ssh/ ; done
10.0.0.161
root@10.0.0.161's password:
id_rsa 100% 1831 1.3MB/s 00:00
id_rsa.pub 100% 407 590.4KB/s 00:00
10.0.0.162
root@10.0.0.162's password:
id_rsa 100% 1831 1.7MB/s 00:00
id_rsa.pub 100% 407 593.3KB/s 00:00
10.0.0.163
root@10.0.0.163's password:
id_rsa 100% 1831 758.0KB/s 00:00
id_rsa.pub 100% 407 375.6KB/s 00:00
10.0.0.164
root@10.0.0.164's password:
id_rsa 100% 1831 1.2MB/s 00:00
id_rsa.pub 100% 407 541.9KB/s 00:00
10.0.0.165
root@10.0.0.165's password:
id_rsa 100% 1831 1.8MB/s 00:00
id_rsa.pub 100% 407 579.9KB/s 00:00
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo 10.0.0.16${i} ; scp id_rsa.pub root@10.0.0.16${i}:/var/lib/postgresql/.ssh/authorized_keys ; done
10.0.0.161
root@10.0.0.161's password:
id_rsa.pub 100% 407 512.1KB/s 00:00
10.0.0.162
root@10.0.0.162's password:
id_rsa.pub 100% 407 306.8KB/s 00:00
10.0.0.163
root@10.0.0.163's password:
id_rsa.pub 100% 407 455.7KB/s 00:00
10.0.0.164
root@10.0.0.164's password:
id_rsa.pub 100% 407 357.0KB/s 00:00
10.0.0.165
root@10.0.0.165's password:
id_rsa.pub 100% 407 221.5KB/s 00:00
postgres@debian-pgbouncer:~/.ssh$
Now that's done we can see that the postgres user from the pgbouncer node can log in via ssh to all the other 4 nodes as the postgres user without needing a password:
postgres@debian-pgbouncer:~/.ssh$ for i in {1..5} ; do echo 10.0.0.16${i} ; ssh postgres@10.0.0.16${i} "id" ; done
10.0.0.161
uid=107(postgres) gid=115(postgres) groups=115(postgres),102(ssl-cert)
10.0.0.162
uid=107(postgres) gid=115(postgres) groups=115(postgres),102(ssl-cert)
10.0.0.163
uid=107(postgres) gid=115(postgres) groups=115(postgres),102(ssl-cert)
10.0.0.164
uid=107(postgres) gid=115(postgres) groups=115(postgres),102(ssl-cert)
10.0.0.165
uid=107(postgres) gid=115(postgres) groups=115(postgres),102(ssl-cert)
Now this means that we have passwordless connectivity between all of the machines for the postgres user. Now for our next steps, we will need to apply the following changes primary host (node1 at 10.0.0.161)
root@debian-psql-1:~# apt install sudo -y ; usermod -aG sudo postgres ; echo "postgres ALL = NOPASSWD: /usr/bin/pg_ctlcluster" > /etc/sudoers.d/postgres
root@debian-psql-2:~# apt install sudo -y ; usermod -aG sudo postgres ; echo "postgres ALL = NOPASSWD: /usr/bin/pg_ctlcluster" > /etc/sudoers.d/postgres
root@debian-psql-3:~# apt install sudo -y ; usermod -aG sudo postgres ; echo "postgres ALL = NOPASSWD: /usr/bin/pg_ctlcluster" > /etc/sudoers.d/postgres
root@debian-psql-4:~# apt install sudo -y ; usermod -aG sudo postgres ; echo "postgres ALL = NOPASSWD: /usr/bin/pg_ctlcluster" > /etc/sudoers.d/postgres
once that's done, we're going to edit the postgresql.conf config file for our 4 hosts:
root@debian-psql-1:~# su - postgres
postgres@debian-psql-1:~$ vim /etc/postgresql/13/main/postgresql.conf
[...]
listen_addresses = '*'
[...]
shared_preload_libraries = 'repmgr'
[...]
include 'postgresql.replication.conf'
:wq to save and quit, then create the file /etc/postgresql/13/main/postgresql.replication.conf with this:
postgres@debian-psql-1:~$ vim /etc/postgresql/13/main/postgresql.replication.conf
max_wal_senders = 15
max_replication_slots = 15
wal_level = 'replica'
hot_standby = on
archive_mode = on
archive_command = '/bin/true'
wal_keep_segments = 500
:wq to save and quit out of vim, then edit pg_hba.conf:
postgres@debian-psql-1:~$ vim /etc/postgresql/13/main/pg_hba.conf
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 10.0.0.0/16 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 10.0.0.0/16 trust
once that's done for all of the 4 hosts, restart postgresql:
postgres@debian-psql-2:~$ vim /etc/postgresql/13/main/pg_hba.conf
postgres@debian-psql-2:~$ exit
logout
root@debian-psql-2:~# systemctl restart postgresql ; systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; v
Active: active (exited) since Mon 2021-04-05 15:20:27 UTC; 38ms ag
Process: 1001 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1001 (code=exited, status=0/SUCCESS)
Apr 05 15:20:27 debian-psql-2 systemd[1]: Starting PostgreSQL RDBMS..
Apr 05 15:20:27 debian-psql-2 systemd[1]: Started PostgreSQL RDBMS.
Nihilist
Donate XMR to Nihilist:
8AUYjhQeG3D5aodJDtqG499N5jXXM71gYKD8LgSsFB9BUV1o
7muLv3DXHoydRTK4SZaaUBq4EAUqpZHLrX2VZLH71Jrd9k8