...
We have two puppet profiles for installing postgres:
- role::postgrespostgresql::master
- role::postgrespostgresql::slave
Make sure that there are only a single master
...
Code Block | ||||
---|---|---|---|---|
| ||||
# # To check if a database is master or slave: # psql -h <ip-address> -U postgres -c 'SELECT pg_is_in_recovery();' -qtA f = master; t = slave |
Installing new nodes
WARNING: This script deletes the database on the host where it is run. Do NOT run this script on the master node, as that will cause data loss!
When a new node is installed, it needs to be connected to the master manually. This can be done with the following script from our tools-repository:
Code Block | ||||
---|---|---|---|---|
| ||||
# postgres-joinMaster.sh <master_host> Stopping PostgreSQL Cleaning up old cluster directory Starting base backup as replicator pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed 40842/40842 kB (100%), 1/1 tablespace NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup pg_basebackup: base backup completed Startging PostgreSQL |
WARNING: This script deletes the database on the host where it is run. Do NOT run this script on the master node, as that will cause data loss!
Failover
...
When a node is joined to the cluster it generates a configfile at '/var/lib/postgresql/13/main/postgresql.auto.conf' which makes this database server connect to that host everytime it starts.
Failover
If the primary database server fails hard, and is not coming back a new master needs to be selected. Then the remaining slaves needs to be configured to stream from the new master. After this point, if the old master comes back, it is a seperate database-server (ie: split-brain), and it should thus be configured as a slave server listening to the new master.
Promote slave to master
Code Block | ||||
---|---|---|---|---|
| ||||
root@pgsql1:~# touch /var/lib/postgresql/13/main/triggerfile
root@pgsql1:~# sudo -u postgres -i psql
postgres=# ALTER SYSTEM RESET primary_conninfo;
ALTER SYSTEM
postgres=# ALTER SYSTEM RESET promote_trigger_file;
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row) |
Make other slaves follow the new master
Code Block | ||||
---|---|---|---|---|
| ||||
root@pgsql2:~# postgres-joinMaster.sh pgsql1
Stopping PostgreSQL
Cleaning up old cluster directory
Starting base backup as replicator
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
40741/40741 kB (100%), 1/1 tablespace
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
pg_basebackup: base backup completed
Add trigger config
Startging PostgreSQL |
Make sure that the keepalive ip for postgres is assigned to the new master
After a new master is selected, make sure the new master is has gotten the postgres IP from keepalived. (In this case it is the IP-address 192.168.254.41)
Code Block | ||||
---|---|---|---|---|
| ||||
root@pgsql1:~# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:50:56:8d:1f:aa brd ff:ff:ff:ff:ff:ff
inet 192.168.254.5/26 brd 192.168.254.63 scope global ens32
valid_lft forever preferred_lft forever
inet 192.168.254.41/32 scope global ens32
valid_lft forever preferred_lft forever
inet6 2001:700:1d00:ec10:250:56ff:fe8d:1faa/64 scope global mngtmpaddr dynamic
valid_lft 2591813sec preferred_lft 604613sec
inet6 fe80::250:56ff:fe8d:1faa/64 scope link
valid_lft forever preferred_lft forever |
Also, remember to change the hierakey profile::postgres::masterserver
to reflect the new master server. This is important if reinstalling the old master as slave.