With this blog I describe the installation of a PostgreSQL Cluster using repmgr instead of Patroni. Repmgr was originally developed by 2ndQuadrant which is now part of EDB and EDB decided for the 2ndQuadrant tools for the future, barman as backup solution from 2ndQ survives, Bart is canceled by EDB.
At first I do a setup of three virtual machines using Rocky Linux 8.6 minimal installation including EPEL Repository for htop. Than I make sure that networking between these three VMs is working by adapting /etc/hosts. Without a functional network a cluster won’t work.
[root@repmgr-01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.198.130 repmgr-01 repmgr-01.localdomain
192.168.198.131 repmgr-02 repmgr-02.localdomain
192.168.198.132 repmgr-03 repmgr-03.localdomain
[root@repmgr-01 ~]# ping repmgr-01
PING repmgr-01 (192.168.198.130) 56(84) bytes of data.
64 bytes from repmgr-01 (192.168.198.130): icmp_seq=1 ttl=64 time=0.032 ms
64 bytes from repmgr-01 (192.168.198.130): icmp_seq=2 ttl=64 time=0.057 ms
64 bytes from repmgr-01 (192.168.198.130): icmp_seq=3 ttl=64 time=0.183 ms
--- repmgr-01 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2074ms
rtt min/avg/max/mdev = 0.032/0.090/0.183/0.066 ms
[root@repmgr-01 ~]# ping repmgr-02
PING repmgr-02 (192.168.198.131) 56(84) bytes of data.
64 bytes from repmgr-02 (192.168.198.131): icmp_seq=1 ttl=64 time=0.550 ms
64 bytes from repmgr-02 (192.168.198.131): icmp_seq=2 ttl=64 time=0.757 ms
64 bytes from repmgr-02 (192.168.198.131): icmp_seq=3 ttl=64 time=0.838 ms
--- repmgr-02 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2074ms
rtt min/avg/max/mdev = 0.550/0.715/0.838/0.121 ms
[root@repmgr-01 ~]# ping repmgr-03
PING repmgr-03 (192.168.198.132) 56(84) bytes of data.
64 bytes from repmgr-03 (192.168.198.132): icmp_seq=1 ttl=64 time=0.541 ms
64 bytes from repmgr-03 (192.168.198.132): icmp_seq=2 ttl=64 time=0.479 ms
64 bytes from repmgr-03 (192.168.198.132): icmp_seq=3 ttl=64 time=0.439 ms
--- repmgr-03 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2058ms
rtt min/avg/max/mdev = 0.439/0.486/0.541/0.045 ms
[root@repmgr-01 ~]# The PostgreSQL installation is following the steps I have described at my Article at heise.de:
https://www.heise.de/ratgeber/PostgreSQL-installieren-mit-den-Community-Paketen-4877556.html
But with different packages added like repmgr and barman and of course PostgreSQL 14 latest.
The installation starts with adding the Postgresql.org repository and disabling the OS PostgreSQL modules on the two database nodes, the third machine is planned as witness for auto failover.
[root@repmgr-01 ~]# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:20:49 ago on Thu 11 Aug 2022 11:06:40 AM CEST.
pgdg-redhat-repo-latest.noarch.rpm 62 kB/s | 13 kB 00:00
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-24 @commandline 13 k
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 12 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-24.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-24.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-24.noarch
Complete!
[root@repmgr-01 ~]# dnf -qy module disable postgresql
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
[root@repmgr-01 ~]# dnf install -y postgresql14 postgresql14-server postgresql14-contrib postgresql14-libs repmgr_14 barmanI personally disable all postgresql releases within the postgresql repo file that I don’t want to use before installation, this speed up the whole installation process.
The next step is adapting the service file for a non standard PGDATA, for that I’m using systemctl edit to create a override.conf for the postgresql-14.service file.
[root@repmgr-03 ~]# systemctl edit postgresql-14.service
[Service]
Environment=PGDATA=/pgdata/14/dataInitialization should be done on the planned Leader / Master node only, by crating the Replica node later pgbasebackup is used.
[root@repmgr-01 ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OKFor recurring jobs I have a set of shell scripts, one for setup CPU and Memory parameters including SSL using server self created certificate and key, required is a directory /pgdata/ssl owned by postgres user.
It configures PostgreSQL using alter system set commands within postgresql.auto.conf.
[postgres@repmgr-01 pgsql]$ cat config.sh
##############################################
# PostgreSQL configuration #
# by Karsten Lenz dbi services sa 04.29.2022 #
##############################################
#!/bin/bash
echo "PostgreSQL Configuration"
echo ""
function printHelp {
printf "Usage:\n"
printf "${progName} [OPTION]\n\n"
printf "Options:\n"
printf "\t -c <Count of CPU used>\t\t\tAmount of CPU used by this system (required)\n"
printf "\t -m <Amount of Memory>\t\t\tAmount of Memory of this system (required)\n"
printf "\t -o <Max Connections>\t\t\tAmount of Connections of this system (default = 100))\n"
printf "\t -v <PostgreSQL Version>\t\tMajor Release of Postgresql (default = 14)\n"
printf "\t -h <Help>\t\t\t\tprints this help\n"
}
while getopts c:m:o:v:h option 2>/dev/null
do
case "${option}"
in
c) CPU=${OPTARG};;
m) RAM=${OPTARG};;
o) CONNECTIONS=${OPTARG:=100};;
v) VERSION=${OPTARG:=14};;
h) printHelp; exit 2;;
*) printf "Unsupported option or parameter value missing '$*'\n";
printf "Run ${printHelp} -h to print help\n"; exit 1;;
esac
done
# create ssl certificate and ssl key
openssl req -new -newkey rsa:4096 -nodes -x509 -subj "/C=CH/ST=DBAAS/L=ZUERICH/O=Dis/CN=www.dbi-services.com" -keyout /pgdata/ssl/pgsql.key -out /pgdata/ssl/pgsql.crt
# define parameters
rootdir=/opt/pgsql/config
cd ${rootdir}
# connections
max_connections=($CONNECTIONS)
echo max_connections : $max_connections
# memory
let shared_buffers=($RAM/4)
echo shared_buffers : $shared_buffers
let effective_cache_size=($RAM-$shared_buffers)
echo effective_cache_size : $effective_cache_size
let work_mem=($RAM*256/$CONNECTIONS)
echo work_mem : $work_mem
let maintenance_work_mem=($RAM*256/8)
echo maintenance_work_mem : $maintenance_work_mem
# cpu
let max_worker_processes=($CPU)
echo max_worker_processes : $max_worker_processes
let max_parallel_workers=($CPU)
echo max_parallel_workers : $max_parallel_workers
let max_parallel_workers_per_gather=($CPU/2)
echo max_parallel_workers_per_gather : $max_parallel_workers_per_gather
let max_parallel_maintenance_workers=($CPU/2)
echo max_parallel_maintenance_workers : $max_parallel_maintenance_workers
# cpu and memory configuration
psql -c "alter system set listen_addresses = '*';"
psql -c "alter system set max_connections = '$max_connections';"
psql -c "alter system set effective_cache_size = '$effective_cache_size GB';"
psql -c "alter system set shared_buffers = '$shared_buffers GB';"
psql -c "alter system set work_mem = '$work_mem MB';"
psql -c "alter system set maintenance_work_mem = '$maintenance_work_mem MB';"
psql -c "alter system set max_worker_processes = '$max_worker_processes';"
psql -c "alter system set max_parallel_workers = '$max_parallel_workers';"
psql -c "alter system set max_parallel_workers_per_gather = '$max_parallel_workers_per_gather';"
psql -c "alter system set max_parallel_maintenance_workers = '$max_parallel_maintenance_workers';"
psql -c "alter system set ssl_cert_file = '/pgdata/ssl/pgsql.crt';"
psql -c "alter system set ssl_key_file = '/pgdata/ssl/pgsql.key';"
psql -c "alter system set ssl = on;"
psql -c "alter system set ssl_ciphers = 'HIGH';"
psql -c "alter system set ssl_min_protocol_version = 'TLSv1.2';"
psql -c "alter system set shared_preload_libraries = pg_stat_statements;"
sudo service postgresql-$VERSION restart
exit
[postgres@repmgr-01 pgsql]$ sh config.sh -h
PostgreSQL Configuration
Usage:
[OPTION]
Options:
-c <Count of CPU used> Amount of CPU used by this system (required)
-m <Amount of Memory> Amount of Memory of this system (required)
-o <Max Connections> Amount of Connections of this system (default = 100))
-v <PostgreSQL Version> Major Release of Postgresql (default = 14)
-h <Help> prints this help
[postgres@repmgr-01 pgsql]$ sh config.sh -c 2 -m 4 -o 100 -v 14
PostgreSQL Configuration
Generating a RSA private key
..........................................................++++
.................................++++
writing new private key to '/pgdata/ssl/pgsql.key'
-----
max_connections : 100
shared_buffers : 1
effective_cache_size : 3
work_mem : 10
maintenance_work_mem : 128
max_worker_processes : 2
max_parallel_workers : 2
max_parallel_workers_per_gather : 1
max_parallel_maintenance_workers : 1
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
Redirecting to /bin/systemctl restart postgresql-14.service
[postgres@repmgr-01 pgsql]$ cat /pgdata/14/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
max_connections = '100'
effective_cache_size = '3 GB'
shared_buffers = '1 GB'
work_mem = '10 MB'
maintenance_work_mem = '128 MB'
max_worker_processes = '2'
max_parallel_workers = '2'
max_parallel_workers_per_gather = '1'
max_parallel_maintenance_workers = '1'
ssl_cert_file = '/pgdata/ssl/pgsql.crt'
ssl_key_file = '/pgdata/ssl/pgsql.key'
ssl = 'on'
ssl_ciphers = 'HIGH'
ssl_min_protocol_version = 'TLSv1.2'
shared_preload_libraries = 'pg_stat_statements'
[postgres@repmgr-01 pgsql]$ The system has 2 vCPU and 4GB RAM as visible within this configuration.
For repmgr I would like to use password less authentication using pgpass, also for that I have written a small shell script, again with -h for help.
[postgres@repmgr-01 /]$ cat /opt/pgsql/config/pgpass.sh
#!/bin/sh
########################################
# #
# pgpass setup script #
# #
# Author: Karsten Lenz / 2020.05.28 #
# #
########################################
progName=$(basename $0)
# postgresVersion=12
domain=localdomain
# pgData=/pgdata/$postgresVersion/data
# postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
postgresHome=/var/lib/pgsql
# postgresBin=/usr/pgsql-$postgresVersion/bin
pgpass=$postgresHome/.pgpass
password=PutYourPasswordHere
function printHelp() {
printf "Usage:\n"
printf "${progName} [OPTION]\n\n"
printf "Options:\n"
printf "\t -p <Primary Server>\t\t\tserver where the primary host is running on (required)\n"
printf "\t -s <Secondary Server>\t\t\tserver where the secondary host is running on (required)\n"
printf "\t -h <Help>\t\t\t\tprints this help\n"
}
while getopts p:s:h option 2>/dev/null
do
case "${option}"
in
p) primServer=${OPTARG};;
s) secdServer=${OPTARG};;
h) printHelp; exit 2;;
*) printf "Unsupported option or parameter value missing '$*'\n";
printf "Run ${progName} -h to print help\n"; exit 1;;
esac
done
############ Log function ############
logFile=/tmp/pgpass_install.log
function log() {
echo "$(date +%Y.%m.%d-%H:%M:%S) [$$]$*" | tee -a $logFile
}
if [ -f $logFile ]; then
continue
else
touch $logFile
chmod -R 774 $logFile
sleep 2
fi
#clean .pgpass
rm -f $pgpass
#set values in .pgpass
log "INFO: #host:port:database:user:password in $pgpass"
echo "#host:port:database:user:password" | tee -a $pgpass
log "INFO: Setting localhost in $pgass"
echo "localhost:5432:*:repmgr:$password" | tee -a $pgpass
log "INFO: Setting 127.0.0.1 in $pgpass"
echo "127.0.0.1:5432:*:repmgr:$password" | tee -a $pgpass
log "INFO: Setting Primary $primServer in $pgpass"
echo "$primServer.$domain:5432:*:repmgr:$password" | tee -a $pgpass
log "INFO: Setting Primary $secdServer in $pgpass"
echo "$secdServer.$domain:5432:*:repmgr:$password" | tee -a $pgpass
#set .pgpass 0600
chmod 0600 $pgpass
#export PGPASSFILE
export PGPASSFILE='/var/lib/pgsql/.pgpass'
[postgres@repmgr-01 /]$
[postgres@repmgr-01 /]$ sh /opt/pgsql/config/pgpass.sh -h
Usage:
pgpass.sh [OPTION]
Options:
-p <Primary Server> server where the primary host is running on (required)
-s <Secondary Server> server where the secondary host is running on (required)
-h <Help> prints this help
[postgres@repmgr-01 /]$
[postgres@repmgr-01 /]$ sh /opt/pgsql/config/pgpass.sh -p 192.168.198.130 -s 192.168.198.131
2022.08.11-14:50:52 [10902]INFO: #host:port:database:user:password in /var/lib/pgsql/.pgpass
#host:port:database:user:password
2022.08.11-14:50:52 [10902]INFO: Setting localhost in
localhost:5432:*:repmgr:PutYourPasswordHere
2022.08.11-14:50:52 [10902]INFO: Setting 127.0.0.1 in /var/lib/pgsql/.pgpass
127.0.0.1:5432:*:repmgr:PutYourPasswordHere
2022.08.11-14:50:52 [10902]INFO: Setting Primary 192.168.198.130 in /var/lib/pgsql/.pgpass
192.168.198.130.localdomain:5432:*:repmgr:PutYourPasswordHere
2022.08.11-14:50:52 [10902]INFO: Setting Primary 192.168.198.131 in /var/lib/pgsql/.pgpass
192.168.198.131.localdomain:5432:*:repmgr:PutYourPasswordHere
[postgres@repmgr-01 /]$
[postgres@repmgr-01 /]$ cat /var/lib/pgsql/.pgpass
#host:port:database:user:password
localhost:5432:*:repmgr:PutYourPasswordHere
127.0.0.1:5432:*:repmgr:PutYourPasswordHere
192.168.198.130.localdomain:5432:*:repmgr:PutYourPasswordHere
192.168.198.131.localdomain:5432:*:repmgr:PutYourPasswordHere
[postgres@repmgr-01 /]$ Now the setup of repmgr itself, also for that I have written shell scripts for recurring operation, originally the scrpts where written for a customer project for a DBaaS environment within a private cloud.
Setting up the Leader / Master node:
[postgres@repmgr-01 /]$ cat /opt/pgsql/config/repMgrMasterSetup.sh
#!/bin/sh
########################################
# RepMgr setup script #
# Rework: Karsten Lenz / 2022.08.11 #
########################################
progName=$(basename $0)
# postgresVersion=14
domain=localdomain
# repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
# pgData=/pgdata/$postgresVersion/data
# postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
# postgresHome=/var/lib/pgsql/$postgresVersion
# postgresBin=/usr/pgsql-$postgresVersion/bin
password=PutYourPasswordHere
function printHelp() {
printf "Usage:\n"
printf "${progName} [OPTION]\n\n"
printf "Options:\n"
printf "\t -p <Primary Server>\t\t\thost where the primary server is running on (required)\n"
printf "\t -s <Standby Server>\t\t\thost where the standby server is running on (required)\n"
printf "\t -v <PostgreSQL Major Release>\t\tMajor Release Number default 14 (required)\n"
printf "\t -h <Help>\t\t\t\tprints this help\n"
}
while getopts c:p:s:v:h option 2>/dev/null
do
case "${option}"
in
p) primServer=${OPTARG};;
s) secdServer=${OPTARG};;
v) postgresVersion=${OPTARG:=14};;
h) printHelp; exit 2;;
*) printf "Unsupported option or parameter value missing '$*'\n";
printf "Run ${progName} -h to print help\n"; exit 1;;
esac
done
### Building Variables according to inputs ###
repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
pgData=/pgdata/$postgresVersion/data
postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
postgresHome=/var/lib/pgsql/$postgresVersion
postgresBin=/usr/pgsql-$postgresVersion/bin
rootDir=/opt/pgsql
############ Log function ############
logFile=/tmp/repMaster_install.log
function log() {
echo "$(date +%Y.%m.%d-%H:%M:%S) [$$]$*" | tee -a $logFile
}
if [ -f $logFile ]; then
continue
else
touch $logFile
chmod -R 774 $logFile
sleep 2
fi
############ MAIN ############
psql -c "alter system set max_replication_slots = 10;"
psql -c "alter system set archive_mode = 'on';"
psql -c "alter system set archive_command = '/bin/true';"
psql -c "alter system set wal_level = 'replica';"
psql -c "alter system set max_wal_senders = 2;"
psql -c "create user repmgr with superuser"
log "INFO: create user repmgr with superuser"
psql -c "alter user repmgr with password '$password'"
log "INFO: alter user repmgr set password"
$postgresBin/createdb repmgrdb -O repmgr
log "INFO: Create database repmgrdb with owner repmgr"
$postgresBin/pg_ctl reload -D $pgData -W -s
if [ $? == 0 ]; then
log "INFO: Reloading postgres returned $?"
else
log "ERROR: Reloading postgres returned $?"
exit 8
fi
> $repmgr_conf
#log "INFO: Setting cluster=$repCluster in $repmgr_conf"
#echo "cluster=$repCluster" | tee -a $repmgr_conf
log "INFO: Setting node_id=1 in $repmgr_conf"
echo "node_id=1" | tee -a $repmgr_conf
log "INFO: Setting node_name=$primServer in $repmgr_conf"
echo "node_name=$primServer" | tee -a $repmgr_conf
log "INFO: Setting conninfo='host=$primServer.$domain user=repmgr dbname=repmgrdb' in $repmgr_conf"
echo "conninfo='host=$primServer.$domain user=repmgr dbname=repmgrdb'" | tee -a $repmgr_conf
log "INFO: Setting use_replication_slots=true"
echo "use_replication_slots=true" | tee -a $repmgr_conf
log "INFO: Setting data_directory='$pgData' in $repmgr_conf"
echo "data_directory='$pgData'" | tee -a $repmgr_conf
#/usr/psql-14/bin repmgrdb repmgr <<EOF
psql -c "ALTER USER repmgr SET search_path TO repmgr, public;"
log "INFO: ALTER USER repmgr SET search_path TO repmgr, public;"
$postgresBin/repmgr -f $repmgr_conf -F master register
if [ $? == 0 ]; then
log "INFO: Registering master returned $?"
else
log "ERROR: Registering master returned $?"
exit 8
fi
echo "setup of primary successfully completed"
[postgres@repmgr-01 /]$
postgres@repmgr-01 /]$ sh /opt/pgsql/config/repMgrMasterSetup.sh -p repmgr-01 -s repmgr-02 -v 14
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
2022.08.11-16:06:28 [14145]INFO: create user repmgr with superuser
ALTER ROLE
2022.08.11-16:06:28 [14145]INFO: alter user repmgr set password
2022.08.11-16:06:28 [14145]INFO: Create database repmgrdb with owner repmgr
2022.08.11-16:06:28 [14145]INFO: Reloading postgres returned 0
2022.08.11-16:06:28 [14145]INFO: Setting node_id=1 in /etc/repmgr/14/repmgr.conf
node_id=1
2022.08.11-16:06:28 [14145]INFO: Setting node_name=repmgr-01 in /etc/repmgr/14/repmgr.conf
node_name=repmgr-01
2022.08.11-16:06:28 [14145]INFO: Setting conninfo='host=repmgr-01.localdomain user=repmgr dbname=repmgrdb' in /etc/repmgr/14/repmgr.conf
conninfo='host=repmgr-01.localdomain user=repmgr dbname=repmgrdb'
2022.08.11-16:06:28 [14145]INFO: Setting use_replication_slots=true
use_replication_slots=true
2022.08.11-16:06:28 [14145]INFO: Setting data_directory='/pgdata/14/data' in /etc/repmgr/14/repmgr.conf
data_directory='/pgdata/14/data'
ALTER ROLE
2022.08.11-16:06:28 [14145]INFO: ALTER USER repmgr SET search_path TO repmgr, public;
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered
2022.08.11-16:06:28 [14145]INFO: Registering master returned 0
setup of primary successfully completed
postgres@repmgr-01 /]$And now the master is running.
[postgres@repmgr-01 /]$ /usr/pgsql-14/bin/repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-----------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
1 | repmgr-01 | primary | * running | | default | 100 | 1 | host=repmgr-01.localdomain user=repmgr dbname=repmgrdb
[postgres@repmgr-01 /]$For the replica we need to copy the psql.crt and psql.key using scp to /pgdata/ssl on the replica, after that we can use a repMgrSteupStandby.sh script to attach the replica to the leader.
[postgres@repmgr-02 /]$ cat /opt/pgsql/config/repMgrStanbySetup.sh
#!/bin/sh
#################################################
# RepMgr Standby setup script #
# Author: Karsten Lenz dbi-services 2022.08.11 #
#################################################
progName=$(basename $0)
# postgresVersion=14
domain=localdomain
# repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
# pgData=/pgdata/$postgresVersion/data
# postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
# postgresHome=/var/lib/pgsql/$postgresVersion
# postgresBin=/usr/pgsql-$postgresVersion/bin
password=PutYourPasswordHere
function printHelp() {
printf "Usage:\n"
printf "${progName} [OPTION]\n\n"
printf "Options:\n"
printf "\t -c <Container Name>\t\t\tname of the container/cluster (required)\n"
printf "\t -p <Primary Server>\t\t\thost where the primary server is running on (required)\n"
printf "\t -s <Standby Server>\t\t\thost where the standby server is running on (required)\n"
printf "\t -v <PostgreSQL Major Release>\t\tMajor Release Number 14 default (required)\n"
printf "\t -h <Help>\t\t\t\tprints this help\n"
}
while getopts c:p:s:v:h option 2>/dev/null
do
case "${option}"
in
c) container=${OPTARG};;
p) primServer=${OPTARG};;
s) secdServer=${OPTARG};;
v) postgresVersion=${OPTARG:=14};;
h) printHelp; exit 2;;
*) printf "Unsupported option or parameter value missing '$*'\n";
printf "Run ${progName} -h to print help\n"; exit 1;;
esac
done
### Building Definitions according to inputs ###
repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
pgData=/pgdata/$postgresVersion/data
postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
postgresHome=/var/lib/pgsql/$postgresVersion
postgresBin=/usr/pgsql-$postgresVersion/bin
rootDir=/opt/pgsql
############ Log function ############
logFile=/tmp/repSecondary_install.log
function log() {
echo "$(date +%Y.%m.%d-%H:%M:%S) [$$]$*" | tee -a $logFile
}
if [ -f $logFile ]; then
continue
else
touch $logFile
chmod -R 774 $logFile
sleep 2
fi
############ MAIN ############
# change cert and key file via alter system set command
# not necessary - will be copied with base dump??
#psql -c "alter system set ssl_cert_file = '/pgdata/security/ssl/${container}.pem'; "
#psql -c "alter system set ssl_key_file = '/pgdata/security/ssl/${container}.key'; "
>$repmgr_conf
log "INFO: Setting node_id=2 in $repmgr_conf"
echo "node_id=2" | tee -a $repmgr_conf
log "INFO: Setting node_name=$secdServer in $repmgr_conf"
echo "node_name=$secdServer" | tee -a $repmgr_conf
log "INFO: Setting conninfo='host=$secdServer.$domain user=repmgr dbname=repmgrdb' in $repmgr_conf"
echo "conninfo='host=$secdServer.$domain user=repmgr dbname=repmgrdb'" | tee -a $repmgr_conf
log "Info: Setting 'use_replication_slots=true' in $repmgr_conf"
echo "use_replication_slots=true" | tee -a $repmgr_conf
log "INFO: Setting data_directory='$pgData' in $repmgr_conf"
echo "data_directory='$pgData'" | tee -a $repmgr_conf
#/usr/psql-14/bin repmgrdb repmgr <<EOF
$postgresBin/repmgr -h $primServer.$domain -U repmgr -d repmgrdb -F standby clone
if [ $? == 0 ]; then
log "INFO: Registering standby returned $?"
else
log "ERROR: Registering standby returned $?"
exit 8
fi
#start postgresql
sudo systemctl start postgresql-${postgresVersion}.service
## # set path
## psql -c "ALTER USER repmgr SET search_path TO repmgr, public;"
## log "INFO: ALTER USER repmgr SET search_path TO repmgr, public;"
#register standby
$postgresBin/repmgr standby register
echo "setup of standby successfully completed"
[postgres@repmgr-02 /]$ The script has an help function -h to tell how it is used.
[postgres@repmgr-02 /]$ sh /opt/pgsql/config/repMgrStanbySetup.sh -h
Usage:
repMgrStanbySetup.sh [OPTION]
Options:
-c <Container Name> name of the container/cluster (required)
-p <Primary Server> host where the primary server is running on (required)
-s <Standby Server> host where the standby server is running on (required)
-v <PostgreSQL Major Release> Major Release Number 14 default (required)
-h <Help> prints this help
[postgres@repmgr-02 /]$ OK, let it run.
[postgres@repmgr-02 data]$ sh /opt/pgsql/config/repMgrStanbySetup.sh -c Cluster-01 -p repmgr-01 -s repmgr-02 -v 14
2022.08.11-17:39:53 [13124]INFO: Setting node_id=2 in /etc/repmgr/14/repmgr.conf
node_id=2
2022.08.11-17:39:53 [13124]INFO: Setting node_name=repmgr-02 in /etc/repmgr/14/repmgr.conf
node_name=repmgr-02
2022.08.11-17:39:53 [13124]INFO: Setting conninfo='host=repmgr-02.localdomain user=repmgr dbname=repmgrdb' in /etc/repmgr/14/repmgr.conf
conninfo='host=repmgr-02.localdomain user=repmgr dbname=repmgrdb'
2022.08.11-17:39:53 [13124]Info: Setting 'use_replication_slots=true' in /etc/repmgr/14/repmgr.conf
use_replication_slots=true
2022.08.11-17:39:53 [13124]INFO: Setting data_directory='/pgdata/14/data' in /etc/repmgr/14/repmgr.conf
data_directory='/pgdata/14/data'
NOTICE: destination directory "/pgdata/14/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=repmgr-01.localdomain user=repmgr dbname=repmgrdb
DETAIL: current installation size is 34 MB
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
WARNING: directory "/pgdata/14/data" exists but is not empty
NOTICE: -F/--force provided - deleting existing data directory "/pgdata/14/data"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
pg_basebackup -l "repmgr base backup" -D /pgdata/14/data -h repmgr-01.localdomain -p 5432 -U repmgr -X stream -S repmgr_slot_2
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /pgdata/14/data start
HINT: after starting the server, you need to register this standby with "repmgr standby register"
2022.08.11-17:39:53 [13124]INFO: Registering standby returned 0
INFO: connecting to local node "repmgr-02" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1)
INFO: standby registration complete
NOTICE: standby node "repmgr-02" (ID: 2) successfully registered
setup of standby successfully completed
[postgres@repmgr-02 data]$ The Cluster is up and running now.
[postgres@repmgr-01 /]$ /usr/pgsql-14/bin/repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-----------+---------+-----------+-----------+----------+----------+----------+--------------------------------------------------------
1 | repmgr-01 | primary | * running | | default | 100 | 1 | host=repmgr-01.localdomain user=repmgr dbname=repmgrdb
2 | repmgr-02 | standby | running | repmgr-01 | default | 100 | 1 | host=repmgr-02.localdomain user=repmgr dbname=repmgrdb
[postgres@repmgr-01 /]$ ![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/KLE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)