mirror of
http://git.nowherejezfoltodf4jiyl6r56jnzintap5vyjlia7fkirfsnfizflqd.onion/nihilist/selfhosting-blogposts.git
synced 2025-05-16 12:16:59 +00:00
367 lines
16 KiB
Markdown
367 lines
16 KiB
Markdown
---
|
|
search:
|
|
exclude: true
|
|
---
|
|
# 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.
|
|
|
|
|
|
|
|
|