This blog is about a fast, script based, setup of a repmgr two node cluster with auto failover. This setup is very easy and the requirements comparing to a patroni setup very low, for exapmle as open port on the firewall we need only port 5432 which ist the default port of PostgreSQL.
At first some requirements, in my case two VMs with fixed IPs:
[root@el8-repmgr-1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.198.131 el8-repmgr-1 el8-repmgr-1.localdomain
192.168.198.132 el8-repmgr-2 el8-repmgr-2.localdomain
[root@el8-repmgr-1 ~]#
For the whole installation and cofiguration process I have written some shell scripts, for PostgreSQL installation it is this one:
###########################################################################################
# Istalling PostgreSQL on AlmaLinux 8 / CentOS 8 / Oracle Linux 8 / RHEL 8 /Rocky Linux 8 #
# By Karsten Lenz dbi-services sa 2020.05.14 #
###########################################################################################
#!/bin/bash
echo "PostgreSQL Installation on AlmaLinux 8 / CentOS 8 / Oracle Linxu 8 / RHEL 8 / Rocky Linux 8"
echo ""
function printHelp() {
printf "Usage:\n"
printf "${progName} [OPTION]\n\n"
printf "Options:\n"
printf "\t -v <PostgreSQL Version>\t\tMajor Release of Postgresql (required)\n"
printf "\t -t <PostgreSQL Node Type>\t\tIf the System is Primary / Single (p) or Secondary (s) in a Cluster (required)\n"
printf "\t -h <Help>\t\t\t\tprints this help\n"
}
while getopts v:t:h option 2>/dev/null
do
case "${option}"
in
v) VERSION=${OPTARG};;
t) TYPE=${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
echo "Disabling PostgreSQL Packages provided by CentOS 8 or RedHat 8"
dnf -y module disable postgresql
echo "Adding PostgreSQL Repository"
dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
echo "Installing PostgreSQL Packages including barman, repmgr and pg_stat_kcache"
# adaptation needed in fact renamed packages in postgresql 14 for repmgr and pg_stat_kcache
if [ $VERSION == '14' ]
then
dnf -y install postgresql$VERSION postgresql$VERSION-libs postgresql$VERSION-server postgresql$VERSION-contrib pg_stat_kcache_$VERSION repmgr_$VERSION barman
elif [ $VERSION != '14' ]
then
dnf -y install postgresql$VERSION postgresql$VERSION-libs postgresql$VERSION-server postgresql$VERSION-contrib pg_stat_kcache$VERSION repmgr$VERSION barman
fi
echo "Creating override.conf"
# creating empty override.conf for postgresql service
mkdir /etc/systemd/system/postgresql-$VERSION.service.d
# define vriable for tee
OVERRIDE_CONF=/etc/systemd/system/postgresql-$VERSION.service.d/override.conf
touch $OVERRIDE_CONF
# adding values to override.conf
echo "[Service]" | tee -a $OVERRIDE_CONF
echo "Environment=PGDATA=/pgdata/$VERSION/data" | tee -a $OVERRIDE_CONF
if [ $TYPE == 'p' ]
then
echo "Initialyze PostgreSQL $VERSION"
/usr/pgsql-$VERSION/bin/postgresql-$VERSION-setup initdb
echo "Enable PostgreSQL Service"
systemctl enable postgresql-$VERSION.service
echo "Start PostgreSQL $VERSION"
systemctl start postgresql-$VERSION.service
elif [ $TYPE == 's' ]
then
echo "Initialyze PostgreSQL $VERSION"
/usr/pgsql-$VERSION/bin/postgresql-$VERSION-setup initdb
echo "Enable PostgreSQL Service"
systemctl enable postgresql-$VERSION.service
fi
# add user to sudoers
usermod -aG wheel postgres
# set rights on /pgdata and /etc/repmgr
chown -R postgres:postgres /pgdata
chown -R postgres:postgres /etc/repmgr
# open firewall port 5432 for Potsgresql
firewall-cmd --zone=public --permanent --add-port 5432/tcp
firewall-cmd --reload
The scripts have a help function -h, so they say how to use it:
[root@el8-repmgr-1 install]# sh pg_install.sh -h
PostgreSQL Installation on AlmaLinux 8 / CentOS 8 / Oracle Linxu 8 / RHEL 8 / Rocky Linux 8
Usage:
[OPTION]
Options:
-v <PostgreSQL Version> Major Release of Postgresql (required)
-t <PostgreSQL Node Type> If the System is Primary / Single (p) or Secondary (s) in a Cluster (required)
-h <Help> prints this help
[root@el8-repmgr-1 install]#
So we wil first start wht the primary or leader node:
[root@el8-repmgr-1 install]# sh pg_setup.sh -v 14 -t p
PostgreSQL Installation on AlmaLinux 8 / CentOS 8 / Oracle Linxu 8 / RHEL 8 / Rocky Linux 8
Disabling PostgreSQL Packages provided by CentOS 8 or RedHat 8
Last metadata expiration check: 6:31:10 ago on Thu 22 Dec 2022 08:48:47 AM CET.
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Disabling modules:
postgresql
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Complete!
Adding PostgreSQL Repository
Last metadata expiration check: 6:31:10 ago on Thu 22 Dec 2022 08:48:47 AM CET.
pgdg-redhat-repo-latest.noarch.rpm 82 kB/s | 13 kB 00:00
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-28 @commandline 13 k
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 14 k
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-28.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-28.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-28.noarch
Complete!
Installing PostgreSQL Packages including barman, repmgr and pg_stat_kcache
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 596 B/s | 195 B 00:00
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 1.0 MB/s | 702 kB 00:00
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 647 B/s | 195 B 00:00
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 280 kB/s | 161 kB 00:00
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 546 B/s | 195 B 00:00
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 721 kB/s | 459 kB 00:00
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 607 B/s | 195 B 00:00
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 1.2 MB/s | 752 kB 00:00
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 600 B/s | 195 B 00:00
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 1.1 MB/s | 899 kB 00:00
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 616 B/s | 195 B 00:00
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.1 MB 00:00
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 644 B/s | 195 B 00:00
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 1.0 MB/s | 700 kB 00:00
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Installing:
barman noarch 3.3.0-1.rhel8 pgdg-common 48 k
pg_stat_kcache_14 x86_64 2.2.1-2.rhel8 pgdg14 47 k
postgresql14 x86_64 14.6-1PGDG.rhel8 pgdg14 1.5 M
postgresql14-contrib x86_64 14.6-1PGDG.rhel8 pgdg14 726 k
postgresql14-libs x86_64 14.6-1PGDG.rhel8 pgdg14 278 k
postgresql14-server x86_64 14.6-1PGDG.rhel8 pgdg14 5.7 M
repmgr_14 x86_64 5.3.3-1.rhel8 pgdg14 284 k
Installing dependencies:
libicu x86_64 60.3-2.el8_1 baseos 8.8 M
lz4 x86_64 1.8.3-3.el8_4 baseos 102 k
perl-Carp noarch 1.42-396.el8 baseos 30 k
perl-Data-Dumper x86_64 2.167-399.el8 baseos 58 k
perl-Digest noarch 1.17-395.el8 appstream 27 k
perl-Digest-MD5 x86_64 2.55-396.el8 appstream 37 k
perl-Encode x86_64 4:2.97-3.el8 baseos 1.5 M
perl-Errno x86_64 1.28-421.el8 baseos 75 k
perl-Exporter noarch 5.72-396.el8 baseos 34 k
perl-File-Path noarch 2.15-2.el8 baseos 38 k
perl-File-Temp noarch 0.230.600-1.el8 baseos 62 k
perl-Getopt-Long noarch 1:2.50-4.el8 baseos 63 k
perl-HTTP-Tiny noarch 0.074-1.el8 baseos 58 k
perl-IO x86_64 1.38-421.el8 baseos 141 k
perl-MIME-Base64 x86_64 3.15-396.el8 baseos 30 k
perl-Net-SSLeay x86_64 1.88-2.module_el8.6.0+2811+fe6c84b0 appstream 378 k
perl-PathTools x86_64 3.74-1.el8 baseos 90 k
perl-Pod-Escapes noarch 1:1.07-395.el8 baseos 20 k
perl-Pod-Perldoc noarch 3.28-396.el8 baseos 86 k
perl-Pod-Simple noarch 1:3.35-395.el8 baseos 213 k
perl-Pod-Usage noarch 4:1.69-395.el8 baseos 34 k
perl-Scalar-List-Utils x86_64 3:1.49-2.el8 baseos 68 k
perl-Socket x86_64 4:2.027-3.el8 baseos 59 k
perl-Storable x86_64 1:3.11-3.el8 baseos 98 k
perl-Term-ANSIColor noarch 4.06-396.el8 baseos 46 k
perl-Term-Cap noarch 1.17-395.el8 baseos 23 k
perl-Text-ParseWords noarch 3.30-395.el8 baseos 18 k
perl-Text-Tabs+Wrap noarch 2013.0523-395.el8 baseos 24 k
perl-Time-Local noarch 1:1.280-1.el8 baseos 33 k
perl-URI noarch 1.73-3.el8 appstream 116 k
perl-Unicode-Normalize x86_64 1.25-396.el8 baseos 82 k
perl-constant noarch 1.33-396.el8 baseos 25 k
perl-interpreter x86_64 4:5.26.3-421.el8 baseos 6.3 M
perl-libnet noarch 3.11-3.el8 appstream 121 k
perl-libs x86_64 4:5.26.3-421.el8 baseos 1.6 M
perl-macros x86_64 4:5.26.3-421.el8 baseos 71 k
perl-parent noarch 1:0.237-1.el8 baseos 20 k
perl-podlators noarch 4.11-1.el8 baseos 118 k
perl-threads x86_64 1:2.21-2.el8 baseos 61 k
perl-threads-shared x86_64 1.58-2.el8 baseos 47 k
python3-argcomplete noarch 1.9.3-6.el8 appstream 60 k
python3-barman noarch 3.3.0-1.rhel8 pgdg-common 455 k
python3-pip noarch 9.0.3-22.el8 appstream 19 k
python3-psycopg2 x86_64 2.8.6-1.rhel8 pgdg-common 178 k
python3-setuptools noarch 39.2.0-6.el8 baseos 162 k
python36 x86_64 3.6.8-38.module_el8.5.0+2569+5c5719bc appstream 18 k
rsync x86_64 3.1.3-19.el8 baseos 409 k
Installing weak dependencies:
perl-IO-Socket-IP noarch 0.39-5.el8 appstream 47 k
perl-IO-Socket-SSL noarch 2.066-4.module_el8.6.0+2811+fe6c84b0 appstream 297 k
perl-Mozilla-CA noarch 20160104-7.module_el8.5.0+2812+ed912d05 appstream 14 k
Enabling module streams:
perl 5.26
perl-IO-Socket-SSL 2.066
perl-libwww-perl 6.34
python36 3.6
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Install 57 Packages
Total download size: 31 M
Installed size: 107 M
Downloading Packages:
(1/57): perl-Carp-1.42-396.el8.noarch.rpm 291 kB/s | 30 kB 00:00
(2/57): lz4-1.8.3-3.el8_4.x86_64.rpm 682 kB/s | 102 kB 00:00
(3/57): perl-Data-Dumper-2.167-399.el8.x86_64.rpm 1.2 MB/s | 58 kB 00:00
(4/57): perl-Errno-1.28-421.el8.x86_64.rpm 1.5 MB/s | 75 kB 00:00
(5/57): perl-Exporter-5.72-396.el8.noarch.rpm 1.1 MB/s | 34 kB 00:00
(6/57): perl-File-Path-2.15-2.el8.noarch.rpm 1.3 MB/s | 38 kB 00:00
(7/57): perl-Encode-2.97-3.el8.x86_64.rpm 11 MB/s | 1.5 MB 00:00
(8/57): perl-File-Temp-0.230.600-1.el8.noarch.rpm 2.0 MB/s | 62 kB 00:00
(9/57): perl-HTTP-Tiny-0.074-1.el8.noarch.rpm 1.5 MB/s | 58 kB 00:00
(10/57): perl-Getopt-Long-2.50-4.el8.noarch.rpm 1.5 MB/s | 63 kB 00:00
(11/57): perl-IO-1.38-421.el8.x86_64.rpm 3.8 MB/s | 141 kB 00:00
(12/57): perl-MIME-Base64-3.15-396.el8.x86_64.rpm 783 kB/s | 30 kB 00:00
(13/57): perl-Pod-Escapes-1.07-395.el8.noarch.rpm 658 kB/s | 20 kB 00:00
(14/57): perl-PathTools-3.74-1.el8.x86_64.rpm 2.4 MB/s | 90 kB 00:00
(15/57): perl-Pod-Perldoc-3.28-396.el8.noarch.rpm 2.8 MB/s | 86 kB 00:00
(16/57): perl-Pod-Simple-3.35-395.el8.noarch.rpm 5.1 MB/s | 213 kB 00:00
(17/57): libicu-60.3-2.el8_1.x86_64.rpm 19 MB/s | 8.8 MB 00:00
(18/57): perl-Pod-Usage-1.69-395.el8.noarch.rpm 912 kB/s | 34 kB 00:00
(19/57): perl-Scalar-List-Utils-1.49-2.el8.x86_64.rpm 2.5 MB/s | 68 kB 00:00
(20/57): perl-Term-ANSIColor-4.06-396.el8.noarch.rpm 1.9 MB/s | 46 kB 00:00
(21/57): perl-Storable-3.11-3.el8.x86_64.rpm 3.2 MB/s | 98 kB 00:00
(22/57): perl-Socket-2.027-3.el8.x86_64.rpm 1.6 MB/s | 59 kB 00:00
(23/57): perl-Term-Cap-1.17-395.el8.noarch.rpm 756 kB/s | 23 kB 00:00
(24/57): perl-Text-ParseWords-3.30-395.el8.noarch.rpm 547 kB/s | 18 kB 00:00
(25/57): perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch.rpm 794 kB/s | 24 kB 00:00
(26/57): perl-Time-Local-1.280-1.el8.noarch.rpm 1.3 MB/s | 33 kB 00:00
(27/57): perl-Unicode-Normalize-1.25-396.el8.x86_64.rpm 2.6 MB/s | 82 kB 00:00
(28/57): perl-constant-1.33-396.el8.noarch.rpm 820 kB/s | 25 kB 00:00
(29/57): perl-macros-5.26.3-421.el8.x86_64.rpm 2.4 MB/s | 71 kB 00:00
(30/57): perl-parent-0.237-1.el8.noarch.rpm 629 kB/s | 20 kB 00:00
(31/57): perl-podlators-4.11-1.el8.noarch.rpm 3.3 MB/s | 118 kB 00:00
(32/57): perl-threads-2.21-2.el8.x86_64.rpm 2.2 MB/s | 61 kB 00:00
(33/57): perl-threads-shared-1.58-2.el8.x86_64.rpm 1.8 MB/s | 47 kB 00:00
(34/57): python3-setuptools-39.2.0-6.el8.noarch.rpm 5.1 MB/s | 162 kB 00:00
(35/57): rsync-3.1.3-19.el8.x86_64.rpm 11 MB/s | 409 kB 00:00
(36/57): perl-Digest-1.17-395.el8.noarch.rpm 711 kB/s | 27 kB 00:00
(37/57): perl-libs-5.26.3-421.el8.x86_64.rpm 5.9 MB/s | 1.6 MB 00:00
(38/57): perl-interpreter-5.26.3-421.el8.x86_64.rpm 21 MB/s | 6.3 MB 00:00
(39/57): perl-Digest-MD5-2.55-396.el8.x86_64.rpm 1.0 MB/s | 37 kB 00:00
(40/57): perl-IO-Socket-IP-0.39-5.el8.noarch.rpm 1.4 MB/s | 47 kB 00:00
(41/57): perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch.rpm 11 MB/s | 297 kB 00:00
(42/57): perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch.rpm 516 kB/s | 14 kB 00:00
(43/57): perl-libnet-3.11-3.el8.noarch.rpm 4.5 MB/s | 121 kB 00:00
(44/57): perl-URI-1.73-3.el8.noarch.rpm 3.7 MB/s | 116 kB 00:00
(45/57): perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64.rpm 6.0 MB/s | 378 kB 00:00
(46/57): python3-argcomplete-1.9.3-6.el8.noarch.rpm 2.0 MB/s | 60 kB 00:00
(47/57): python3-pip-9.0.3-22.el8.noarch.rpm 578 kB/s | 19 kB 00:00
(48/57): python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64.rpm 673 kB/s | 18 kB 00:00
(49/57): barman-3.3.0-1.rhel8.noarch.rpm 241 kB/s | 48 kB 00:00
(50/57): pg_stat_kcache_14-2.2.1-2.rhel8.x86_64.rpm 1.7 MB/s | 47 kB 00:00
(51/57): python3-psycopg2-2.8.6-1.rhel8.x86_64.rpm 679 kB/s | 178 kB 00:00
(52/57): python3-barman-3.3.0-1.rhel8.noarch.rpm 1.4 MB/s | 455 kB 00:00
(53/57): postgresql14-libs-14.6-1PGDG.rhel8.x86_64.rpm 3.0 MB/s | 278 kB 00:00
(54/57): postgresql14-contrib-14.6-1PGDG.rhel8.x86_64.rpm 2.8 MB/s | 726 kB 00:00
(55/57): repmgr_14-5.3.3-1.rhel8.x86_64.rpm 3.3 MB/s | 284 kB 00:00
(56/57): postgresql14-14.6-1PGDG.rhel8.x86_64.rpm 3.0 MB/s | 1.5 MB 00:00
(57/57): postgresql14-server-14.6-1PGDG.rhel8.x86_64.rpm 8.8 MB/s | 5.7 MB 00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 9.0 MB/s | 31 MB 00:03
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : perl-Digest-1.17-395.el8.noarch 1/57
Installing : perl-Digest-MD5-2.55-396.el8.x86_64 2/57
Installing : perl-Data-Dumper-2.167-399.el8.x86_64 3/57
Installing : perl-libnet-3.11-3.el8.noarch 4/57
Installing : perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64 5/57
Installing : perl-URI-1.73-3.el8.noarch 6/57
Installing : perl-Pod-Escapes-1:1.07-395.el8.noarch 7/57
Installing : perl-Time-Local-1:1.280-1.el8.noarch 8/57
Installing : perl-IO-Socket-IP-0.39-5.el8.noarch 9/57
Installing : perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch 10/57
Installing : perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch 11/57
Installing : perl-Term-ANSIColor-4.06-396.el8.noarch 12/57
Installing : perl-Term-Cap-1.17-395.el8.noarch 13/57
Installing : perl-File-Temp-0.230.600-1.el8.noarch 14/57
Installing : perl-Pod-Simple-1:3.35-395.el8.noarch 15/57
Installing : perl-HTTP-Tiny-0.074-1.el8.noarch 16/57
Installing : perl-podlators-4.11-1.el8.noarch 17/57
Installing : perl-Pod-Perldoc-3.28-396.el8.noarch 18/57
Installing : perl-Text-ParseWords-3.30-395.el8.noarch 19/57
Installing : perl-Pod-Usage-4:1.69-395.el8.noarch 20/57
Installing : perl-MIME-Base64-3.15-396.el8.x86_64 21/57
Installing : perl-Storable-1:3.11-3.el8.x86_64 22/57
Installing : perl-Getopt-Long-1:2.50-4.el8.noarch 23/57
Installing : perl-Errno-1.28-421.el8.x86_64 24/57
Installing : perl-Socket-4:2.027-3.el8.x86_64 25/57
Installing : perl-Encode-4:2.97-3.el8.x86_64 26/57
Installing : perl-Exporter-5.72-396.el8.noarch 27/57
Installing : perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 28/57
Installing : perl-macros-4:5.26.3-421.el8.x86_64 29/57
Installing : perl-parent-1:0.237-1.el8.noarch 30/57
Installing : perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch 31/57
Installing : perl-Unicode-Normalize-1.25-396.el8.x86_64 32/57
Installing : perl-File-Path-2.15-2.el8.noarch 33/57
Installing : perl-IO-1.38-421.el8.x86_64 34/57
Installing : perl-PathTools-3.74-1.el8.x86_64 35/57
Installing : perl-constant-1.33-396.el8.noarch 36/57
Installing : perl-threads-1:2.21-2.el8.x86_64 37/57
Installing : perl-threads-shared-1.58-2.el8.x86_64 38/57
Installing : perl-libs-4:5.26.3-421.el8.x86_64 39/57
Installing : perl-Carp-1.42-396.el8.noarch 40/57
Installing : perl-interpreter-4:5.26.3-421.el8.x86_64 41/57
Installing : postgresql14-libs-14.6-1PGDG.rhel8.x86_64 42/57
Running scriptlet: postgresql14-libs-14.6-1PGDG.rhel8.x86_64 42/57
Installing : python3-setuptools-39.2.0-6.el8.noarch 43/57
Installing : python3-pip-9.0.3-22.el8.noarch 44/57
Installing : python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64 45/57
Running scriptlet: python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64 45/57
Installing : libicu-60.3-2.el8_1.x86_64 46/57
Running scriptlet: libicu-60.3-2.el8_1.x86_64 46/57
Installing : python3-psycopg2-2.8.6-1.rhel8.x86_64 47/57
Installing : python3-argcomplete-1.9.3-6.el8.noarch 48/57
Installing : python3-barman-3.3.0-1.rhel8.noarch 49/57
Installing : rsync-3.1.3-19.el8.x86_64 50/57
Installing : lz4-1.8.3-3.el8_4.x86_64 51/57
Installing : postgresql14-14.6-1PGDG.rhel8.x86_64 52/57
Running scriptlet: postgresql14-14.6-1PGDG.rhel8.x86_64 52/57
Running scriptlet: postgresql14-server-14.6-1PGDG.rhel8.x86_64 53/57
Installing : postgresql14-server-14.6-1PGDG.rhel8.x86_64 53/57
Running scriptlet: postgresql14-server-14.6-1PGDG.rhel8.x86_64 53/57
Installing : pg_stat_kcache_14-2.2.1-2.rhel8.x86_64 54/57
Running scriptlet: pg_stat_kcache_14-2.2.1-2.rhel8.x86_64 54/57
Installing : postgresql14-contrib-14.6-1PGDG.rhel8.x86_64 55/57
Running scriptlet: repmgr_14-5.3.3-1.rhel8.x86_64 56/57
Installing : repmgr_14-5.3.3-1.rhel8.x86_64 56/57
Running scriptlet: repmgr_14-5.3.3-1.rhel8.x86_64 56/57
Running scriptlet: barman-3.3.0-1.rhel8.noarch 57/57
Installing : barman-3.3.0-1.rhel8.noarch 57/57
Running scriptlet: barman-3.3.0-1.rhel8.noarch 57/57
Verifying : libicu-60.3-2.el8_1.x86_64 1/57
Verifying : lz4-1.8.3-3.el8_4.x86_64 2/57
Verifying : perl-Carp-1.42-396.el8.noarch 3/57
Verifying : perl-Data-Dumper-2.167-399.el8.x86_64 4/57
Verifying : perl-Encode-4:2.97-3.el8.x86_64 5/57
Verifying : perl-Errno-1.28-421.el8.x86_64 6/57
Verifying : perl-Exporter-5.72-396.el8.noarch 7/57
Verifying : perl-File-Path-2.15-2.el8.noarch 8/57
Verifying : perl-File-Temp-0.230.600-1.el8.noarch 9/57
Verifying : perl-Getopt-Long-1:2.50-4.el8.noarch 10/57
Verifying : perl-HTTP-Tiny-0.074-1.el8.noarch 11/57
Verifying : perl-IO-1.38-421.el8.x86_64 12/57
Verifying : perl-MIME-Base64-3.15-396.el8.x86_64 13/57
Verifying : perl-PathTools-3.74-1.el8.x86_64 14/57
Verifying : perl-Pod-Escapes-1:1.07-395.el8.noarch 15/57
Verifying : perl-Pod-Perldoc-3.28-396.el8.noarch 16/57
Verifying : perl-Pod-Simple-1:3.35-395.el8.noarch 17/57
Verifying : perl-Pod-Usage-4:1.69-395.el8.noarch 18/57
Verifying : perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 19/57
Verifying : perl-Socket-4:2.027-3.el8.x86_64 20/57
Verifying : perl-Storable-1:3.11-3.el8.x86_64 21/57
Verifying : perl-Term-ANSIColor-4.06-396.el8.noarch 22/57
Verifying : perl-Term-Cap-1.17-395.el8.noarch 23/57
Verifying : perl-Text-ParseWords-3.30-395.el8.noarch 24/57
Verifying : perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch 25/57
Verifying : perl-Time-Local-1:1.280-1.el8.noarch 26/57
Verifying : perl-Unicode-Normalize-1.25-396.el8.x86_64 27/57
Verifying : perl-constant-1.33-396.el8.noarch 28/57
Verifying : perl-interpreter-4:5.26.3-421.el8.x86_64 29/57
Verifying : perl-libs-4:5.26.3-421.el8.x86_64 30/57
Verifying : perl-macros-4:5.26.3-421.el8.x86_64 31/57
Verifying : perl-parent-1:0.237-1.el8.noarch 32/57
Verifying : perl-podlators-4.11-1.el8.noarch 33/57
Verifying : perl-threads-1:2.21-2.el8.x86_64 34/57
Verifying : perl-threads-shared-1.58-2.el8.x86_64 35/57
Verifying : python3-setuptools-39.2.0-6.el8.noarch 36/57
Verifying : rsync-3.1.3-19.el8.x86_64 37/57
Verifying : perl-Digest-1.17-395.el8.noarch 38/57
Verifying : perl-Digest-MD5-2.55-396.el8.x86_64 39/57
Verifying : perl-IO-Socket-IP-0.39-5.el8.noarch 40/57
Verifying : perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch 41/57
Verifying : perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch 42/57
Verifying : perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64 43/57
Verifying : perl-URI-1.73-3.el8.noarch 44/57
Verifying : perl-libnet-3.11-3.el8.noarch 45/57
Verifying : python3-argcomplete-1.9.3-6.el8.noarch 46/57
Verifying : python3-pip-9.0.3-22.el8.noarch 47/57
Verifying : python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64 48/57
Verifying : barman-3.3.0-1.rhel8.noarch 49/57
Verifying : python3-barman-3.3.0-1.rhel8.noarch 50/57
Verifying : python3-psycopg2-2.8.6-1.rhel8.x86_64 51/57
Verifying : pg_stat_kcache_14-2.2.1-2.rhel8.x86_64 52/57
Verifying : postgresql14-14.6-1PGDG.rhel8.x86_64 53/57
Verifying : postgresql14-contrib-14.6-1PGDG.rhel8.x86_64 54/57
Verifying : postgresql14-libs-14.6-1PGDG.rhel8.x86_64 55/57
Verifying : postgresql14-server-14.6-1PGDG.rhel8.x86_64 56/57
Verifying : repmgr_14-5.3.3-1.rhel8.x86_64 57/57
Installed:
barman-3.3.0-1.rhel8.noarch libicu-60.3-2.el8_1.x86_64 lz4-1.8.3-3.el8_4.x86_64 perl-Carp-1.42-396.el8.noarch perl-Data-Dumper-2.167-399.el8.x86_64
perl-Digest-1.17-395.el8.noarch perl-Digest-MD5-2.55-396.el8.x86_64 perl-Encode-4:2.97-3.el8.x86_64 perl-Errno-1.28-421.el8.x86_64 perl-Exporter-5.72-396.el8.noarch
perl-File-Path-2.15-2.el8.noarch perl-File-Temp-0.230.600-1.el8.noarch perl-Getopt-Long-1:2.50-4.el8.noarch perl-HTTP-Tiny-0.074-1.el8.noarch perl-IO-1.38-421.el8.x86_64
perl-IO-Socket-IP-0.39-5.el8.noarch perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch perl-MIME-Base64-3.15-396.el8.x86_64 perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64
perl-PathTools-3.74-1.el8.x86_64 perl-Pod-Escapes-1:1.07-395.el8.noarch perl-Pod-Perldoc-3.28-396.el8.noarch perl-Pod-Simple-1:3.35-395.el8.noarch perl-Pod-Usage-4:1.69-395.el8.noarch
perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 perl-Socket-4:2.027-3.el8.x86_64 perl-Storable-1:3.11-3.el8.x86_64 perl-Term-ANSIColor-4.06-396.el8.noarch perl-Term-Cap-1.17-395.el8.noarch
perl-Text-ParseWords-3.30-395.el8.noarch perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch perl-Time-Local-1:1.280-1.el8.noarch perl-URI-1.73-3.el8.noarch perl-Unicode-Normalize-1.25-396.el8.x86_64
perl-constant-1.33-396.el8.noarch perl-interpreter-4:5.26.3-421.el8.x86_64 perl-libnet-3.11-3.el8.noarch perl-libs-4:5.26.3-421.el8.x86_64 perl-macros-4:5.26.3-421.el8.x86_64
perl-parent-1:0.237-1.el8.noarch perl-podlators-4.11-1.el8.noarch perl-threads-1:2.21-2.el8.x86_64 perl-threads-shared-1.58-2.el8.x86_64 pg_stat_kcache_14-2.2.1-2.rhel8.x86_64
postgresql14-14.6-1PGDG.rhel8.x86_64 postgresql14-contrib-14.6-1PGDG.rhel8.x86_64 postgresql14-libs-14.6-1PGDG.rhel8.x86_64 postgresql14-server-14.6-1PGDG.rhel8.x86_64 python3-argcomplete-1.9.3-6.el8.noarch
python3-barman-3.3.0-1.rhel8.noarch python3-pip-9.0.3-22.el8.noarch python3-psycopg2-2.8.6-1.rhel8.x86_64 python3-setuptools-39.2.0-6.el8.noarch python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64
repmgr_14-5.3.3-1.rhel8.x86_64 rsync-3.1.3-19.el8.x86_64
Complete!
Creating override.conf
[Service]
Environment=PGDATA=/pgdata/14/data
Initialyze PostgreSQL 14
Initializing database ... OK
Enable PostgreSQL Service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
Start PostgreSQL 14
success
success
[root@el8-repmgr-1 install]#
On the master / leader node ist PostgreSQL now running, s we can configure PostgreSQL accoring to given CPU and Memory ressources by the config.sh script, all following scripts need to be uses as postgres user created by the package installation, so we need to change owner on the other scripts.
[root@el8-repmgr-1 pgsql]# pwd
/opt/pgsql
[root@el8-repmgr-1 pgsql]# ls -la
total 0
drwxr-xr-x. 4 root root 35 Dec 22 14:36 .
drwxr-xr-x. 3 root root 19 Dec 22 14:36 ..
drwxr-xr-x. 2 root root 154 Dec 22 14:38 config
drwxr-xr-x. 2 root root 52 Dec 22 15:15 install
[root@el8-repmgr-1 pgsql]# chown -R postgres:postgres config/
[root@el8-repmgr-1 pgsql]#
The configuration script:
[postgres@el8-repmgr-1 config]$ 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@el8-repmgr-1 config]$
So lets config the master / leader node, given are 4 vCPU and 8GB RAM. The script will restart the postgresql service, so it is needed that postgres user is wihtin sudoers.
Adapt sudoers with visudo:
## Allows people in group wheel to run all commands
# %wheel ALL=(ALL) ALL
## Same thing without a password
%wheel ALL=(ALL) NOPASSWD: ALL
Add postgres user to sudoers:
[root@el8-repmgr-1 pgsql]# usermod -aG wheel postgres
[root@el8-repmgr-1 pgsql]#
Now we can use the config script.
[postgres@el8-repmgr-1 config]$ 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@el8-repmgr-1 config]$
[postgres@el8-repmgr-1 config]$ sh config.sh -c 4 -m 8 -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 : 2
effective_cache_size : 6
work_mem : 20
maintenance_work_mem : 256
max_worker_processes : 4
max_parallel_workers : 4
max_parallel_workers_per_gather : 2
max_parallel_maintenance_workers : 2
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@el8-repmgr-1 config]$
Now we can do the preperation for repmgr, at first the setup of .pgpass using a script.
[postgres@el8-repmgr-1 config]$ cat 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@el8-repmgr-1 config]$
[postgres@el8-repmgr-1 config]$ sh 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@el8-repmgr-1 config]$ sh pgpass.sh -p 192.168.198.131 -s 192.168.198.132
2022.12.22-16:01:18 [6166]INFO: #host:port:database:user:password in /var/lib/pgsql/.pgpass
#host:port:database:user:password
2022.12.22-16:01:18 [6166]INFO: Setting localhost in
localhost:5432:*:repmgr:PutYourPasswordHere
2022.12.22-16:01:18 [6166]INFO: Setting 127.0.0.1 in /var/lib/pgsql/.pgpass
127.0.0.1:5432:*:repmgr:PutYourPasswordHere
2022.12.22-16:01:18 [6166]INFO: Setting Primary 192.168.198.131 in /var/lib/pgsql/.pgpass
192.168.198.131.localdomain:5432:*:repmgr:PutYourPasswordHere
2022.12.22-16:01:18 [6166]INFO: Setting Primary 192.168.198.132 in /var/lib/pgsql/.pgpass
192.168.198.132.localdomain:5432:*:repmgr:PutYourPasswordHere
[postgres@el8-repmgr-1 config]$
Time for repmgr on the master / leader node using the setup script for the master / leader node:
#!/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
[postgres@el8-repmgr-1 config]$ sh repMgrMasterNEW_setup.sh -p el8-repmgr-1 -s el8-repmgr-2 -v 14
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ERROR: role "repmgr" already exists
2022.12.22-16:19:58 [7333]INFO: create user repmgr with superuser
ALTER ROLE
2022.12.22-16:19:58 [7333]INFO: alter user repmgr set password
2022.12.22-16:19:58 [7333]INFO: Create database repmgrdb with owner repmgr
2022.12.22-16:19:58 [7333]INFO: Reloading postgres returned 0
2022.12.22-16:19:58 [7333]INFO: Setting node_id=1 in /etc/repmgr/14/repmgr.conf
node_id=1
2022.12.22-16:19:58 [7333]INFO: Setting node_name=el8-repmgr-1 in /etc/repmgr/14/repmgr.conf
node_name=el8-repmgr-1
2022.12.22-16:19:58 [7333]INFO: Setting conninfo='host=el8-repmgr-1.localdomain user=repmgr dbname=repmgrdb' in /etc/repmgr/14/repmgr.conf
conninfo='host=el8-repmgr-1.localdomain user=repmgr dbname=repmgrdb'
2022.12.22-16:19:58 [7333]INFO: Setting use_replication_slots=true
use_replication_slots=true
2022.12.22-16:19:58 [7333]INFO: Setting data_directory='/pgdata/14/data' in /etc/repmgr/14/repmgr.conf
data_directory='/pgdata/14/data'
ALTER ROLE
2022.12.22-16:19:58 [7333]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.12.22-16:19:58 [7333]INFO: Registering master returned 0
[postgres@el8-repmgr-1 config]$
[postgres@el8-repmgr-1 config]$ /usr/pgsql-14/bin/repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+--------------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------
1 | el8-repmgr-1 | primary | * running | | default | 100 | 1 | host=el8-repmgr-1.localdomain user=repmgr dbname=repmgrdb
[postgres@el8-repmgr-1 config]$
Leader / Master is running now, lets install the Replica.
[root@el8-repmgr-2 install]# sh pg_setup.sh -h
PostgreSQL Installation on AlmaLinux 8 / CentOS 8 / Oracle Linxu 8 / RHEL 8 / Rocky Linux 8
Usage:
[OPTION]
Options:
-v <PostgreSQL Version> Major Release of Postgresql (required)
-t <PostgreSQL Node Type> If the System is Primary / Single (p) or Secondary (s) in a Cluster (required)
-h <Help> prints this help
[root@el8-repmgr-2 install]#
[root@el8-repmgr-2 install]# sh pg_setup.sh -v14 -t s
PostgreSQL Installation on AlmaLinux 8 / CentOS 8 / Oracle Linxu 8 / RHEL 8 / Rocky Linux 8
Disabling PostgreSQL Packages provided by CentOS 8 or RedHat 8
Last metadata expiration check: 7:03:32 ago on Thu 22 Dec 2022 02:59:34 PM CET.
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Disabling modules:
postgresql
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Complete!
Adding PostgreSQL Repository
Last metadata expiration check: 7:03:33 ago on Thu 22 Dec 2022 02:59:34 PM CET.
pgdg-redhat-repo-latest.noarch.rpm 14 kB/s | 13 kB 00:00
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-28 @commandline 13 k
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 14 k
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-28.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-28.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-28.noarch
Complete!
Installing PostgreSQL Packages including barman, repmgr and pg_stat_kcache
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 90 B/s | 195 B 00:02
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 159 kB/s | 702 kB 00:04
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 82 B/s | 195 B 00:02
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 15 for RHEL / Rocky 8 - x86_64 48 kB/s | 164 kB 00:03
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 83 B/s | 195 B 00:02
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 112 kB/s | 463 kB 00:04
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 115 B/s | 195 B 00:01
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 212 kB/s | 755 kB 00:03
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 104 B/s | 195 B 00:01
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 217 kB/s | 901 kB 00:04
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 105 B/s | 195 B 00:01
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 288 kB/s | 1.1 MB 00:03
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 100 B/s | 195 B 00:01
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 194 kB/s | 700 kB 00:03
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================================================================================================
Installing:
barman noarch 3.3.0-1.rhel8 pgdg-common 48 k
pg_stat_kcache_14 x86_64 2.2.1-2.rhel8 pgdg14 47 k
postgresql14 x86_64 14.6-1PGDG.rhel8 pgdg14 1.5 M
postgresql14-contrib x86_64 14.6-1PGDG.rhel8 pgdg14 726 k
postgresql14-libs x86_64 14.6-1PGDG.rhel8 pgdg14 278 k
postgresql14-server x86_64 14.6-1PGDG.rhel8 pgdg14 5.7 M
repmgr_14 x86_64 5.3.3-1.rhel8 pgdg14 284 k
Installing dependencies:
libicu x86_64 60.3-2.el8_1 baseos 8.8 M
lz4 x86_64 1.8.3-3.el8_4 baseos 102 k
perl-Carp noarch 1.42-396.el8 baseos 30 k
perl-Data-Dumper x86_64 2.167-399.el8 baseos 58 k
perl-Digest noarch 1.17-395.el8 appstream 27 k
perl-Digest-MD5 x86_64 2.55-396.el8 appstream 37 k
perl-Encode x86_64 4:2.97-3.el8 baseos 1.5 M
perl-Errno x86_64 1.28-421.el8 baseos 75 k
perl-Exporter noarch 5.72-396.el8 baseos 34 k
perl-File-Path noarch 2.15-2.el8 baseos 38 k
perl-File-Temp noarch 0.230.600-1.el8 baseos 62 k
perl-Getopt-Long noarch 1:2.50-4.el8 baseos 63 k
perl-HTTP-Tiny noarch 0.074-1.el8 baseos 58 k
perl-IO x86_64 1.38-421.el8 baseos 141 k
perl-MIME-Base64 x86_64 3.15-396.el8 baseos 30 k
perl-Net-SSLeay x86_64 1.88-2.module_el8.6.0+2811+fe6c84b0 appstream 378 k
perl-PathTools x86_64 3.74-1.el8 baseos 90 k
perl-Pod-Escapes noarch 1:1.07-395.el8 baseos 20 k
perl-Pod-Perldoc noarch 3.28-396.el8 baseos 86 k
perl-Pod-Simple noarch 1:3.35-395.el8 baseos 213 k
perl-Pod-Usage noarch 4:1.69-395.el8 baseos 34 k
perl-Scalar-List-Utils x86_64 3:1.49-2.el8 baseos 68 k
perl-Socket x86_64 4:2.027-3.el8 baseos 59 k
perl-Storable x86_64 1:3.11-3.el8 baseos 98 k
perl-Term-ANSIColor noarch 4.06-396.el8 baseos 46 k
perl-Term-Cap noarch 1.17-395.el8 baseos 23 k
perl-Text-ParseWords noarch 3.30-395.el8 baseos 18 k
perl-Text-Tabs+Wrap noarch 2013.0523-395.el8 baseos 24 k
perl-Time-Local noarch 1:1.280-1.el8 baseos 33 k
perl-URI noarch 1.73-3.el8 appstream 116 k
perl-Unicode-Normalize x86_64 1.25-396.el8 baseos 82 k
perl-constant noarch 1.33-396.el8 baseos 25 k
perl-interpreter x86_64 4:5.26.3-421.el8 baseos 6.3 M
perl-libnet noarch 3.11-3.el8 appstream 121 k
perl-libs x86_64 4:5.26.3-421.el8 baseos 1.6 M
perl-macros x86_64 4:5.26.3-421.el8 baseos 71 k
perl-parent noarch 1:0.237-1.el8 baseos 20 k
perl-podlators noarch 4.11-1.el8 baseos 118 k
perl-threads x86_64 1:2.21-2.el8 baseos 61 k
perl-threads-shared x86_64 1.58-2.el8 baseos 47 k
python3-argcomplete noarch 1.9.3-6.el8 appstream 60 k
python3-barman noarch 3.3.0-1.rhel8 pgdg-common 455 k
python3-pip noarch 9.0.3-22.el8 appstream 19 k
python3-psycopg2 x86_64 2.8.6-1.rhel8 pgdg-common 178 k
python3-setuptools noarch 39.2.0-6.el8 baseos 162 k
python36 x86_64 3.6.8-38.module_el8.5.0+2569+5c5719bc appstream 18 k
rsync x86_64 3.1.3-19.el8 baseos 409 k
Installing weak dependencies:
perl-IO-Socket-IP noarch 0.39-5.el8 appstream 47 k
perl-IO-Socket-SSL noarch 2.066-4.module_el8.6.0+2811+fe6c84b0 appstream 297 k
perl-Mozilla-CA noarch 20160104-7.module_el8.5.0+2812+ed912d05 appstream 14 k
Enabling module streams:
perl 5.26
perl-IO-Socket-SSL 2.066
perl-libwww-perl 6.34
python36 3.6
Transaction Summary
==========================================================================================================================================================================================================================================================================================
Install 57 Packages
Total download size: 31 M
Installed size: 107 M
Downloading Packages:
(1/57): perl-Carp-1.42-396.el8.noarch.rpm 292 kB/s | 30 kB 00:00
(2/57): lz4-1.8.3-3.el8_4.x86_64.rpm 696 kB/s | 102 kB 00:00
(3/57): perl-Data-Dumper-2.167-399.el8.x86_64.rpm 1.1 MB/s | 58 kB 00:00
(4/57): perl-Errno-1.28-421.el8.x86_64.rpm 1.4 MB/s | 75 kB 00:00
(5/57): perl-Exporter-5.72-396.el8.noarch.rpm 991 kB/s | 34 kB 00:00
(6/57): perl-File-Path-2.15-2.el8.noarch.rpm 1.2 MB/s | 38 kB 00:00
(7/57): perl-File-Temp-0.230.600-1.el8.noarch.rpm 2.4 MB/s | 62 kB 00:00
(8/57): perl-Getopt-Long-2.50-4.el8.noarch.rpm 2.0 MB/s | 63 kB 00:00
(9/57): perl-HTTP-Tiny-0.074-1.el8.noarch.rpm 1.6 MB/s | 58 kB 00:00
(10/57): perl-IO-1.38-421.el8.x86_64.rpm 2.2 MB/s | 141 kB 00:00
(11/57): perl-MIME-Base64-3.15-396.el8.x86_64.rpm 1.0 MB/s | 30 kB 00:00
(12/57): perl-PathTools-3.74-1.el8.x86_64.rpm 3.2 MB/s | 90 kB 00:00
(13/57): perl-Pod-Escapes-1.07-395.el8.noarch.rpm 828 kB/s | 20 kB 00:00
(14/57): perl-Pod-Perldoc-3.28-396.el8.noarch.rpm 2.9 MB/s | 86 kB 00:00
(15/57): perl-Pod-Simple-3.35-395.el8.noarch.rpm 3.8 MB/s | 213 kB 00:00
(16/57): perl-Encode-2.97-3.el8.x86_64.rpm 3.1 MB/s | 1.5 MB 00:00
(17/57): perl-Pod-Usage-1.69-395.el8.noarch.rpm 1.3 MB/s | 34 kB 00:00
(18/57): perl-Scalar-List-Utils-1.49-2.el8.x86_64.rpm 2.1 MB/s | 68 kB 00:00
(19/57): perl-Socket-2.027-3.el8.x86_64.rpm 1.5 MB/s | 59 kB 00:00
(20/57): perl-Storable-3.11-3.el8.x86_64.rpm 2.9 MB/s | 98 kB 00:00
(21/57): perl-Term-ANSIColor-4.06-396.el8.noarch.rpm 1.3 MB/s | 46 kB 00:00
(22/57): perl-Term-Cap-1.17-395.el8.noarch.rpm 712 kB/s | 23 kB 00:00
(23/57): perl-Text-ParseWords-3.30-395.el8.noarch.rpm 681 kB/s | 18 kB 00:00
(24/57): perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch.rpm 965 kB/s | 24 kB 00:00
(25/57): perl-Time-Local-1.280-1.el8.noarch.rpm 1.2 MB/s | 33 kB 00:00
(26/57): perl-Unicode-Normalize-1.25-396.el8.x86_64.rpm 3.0 MB/s | 82 kB 00:00
(27/57): perl-constant-1.33-396.el8.noarch.rpm 846 kB/s | 25 kB 00:00
(28/57): perl-libs-5.26.3-421.el8.x86_64.rpm 6.5 MB/s | 1.6 MB 00:00
(29/57): perl-macros-5.26.3-421.el8.x86_64.rpm 2.4 MB/s | 71 kB 00:00
(30/57): perl-parent-0.237-1.el8.noarch.rpm 698 kB/s | 20 kB 00:00
(31/57): libicu-60.3-2.el8_1.x86_64.rpm 8.0 MB/s | 8.8 MB 00:01
(32/57): perl-podlators-4.11-1.el8.noarch.rpm 3.9 MB/s | 118 kB 00:00
(33/57): perl-threads-shared-1.58-2.el8.x86_64.rpm 1.9 MB/s | 47 kB 00:00
(34/57): perl-threads-2.21-2.el8.x86_64.rpm 2.2 MB/s | 61 kB 00:00
(35/57): python3-setuptools-39.2.0-6.el8.noarch.rpm 5.2 MB/s | 162 kB 00:00
(36/57): rsync-3.1.3-19.el8.x86_64.rpm 11 MB/s | 409 kB 00:00
(37/57): perl-Digest-1.17-395.el8.noarch.rpm 803 kB/s | 27 kB 00:00
(38/57): perl-Digest-MD5-2.55-396.el8.x86_64.rpm 1.4 MB/s | 37 kB 00:00
(39/57): perl-IO-Socket-IP-0.39-5.el8.noarch.rpm 1.5 MB/s | 47 kB 00:00
(40/57): perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch.rpm 7.9 MB/s | 297 kB 00:00
(41/57): perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch.rpm 486 kB/s | 14 kB 00:00
(42/57): perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64.rpm 9.7 MB/s | 378 kB 00:00
(43/57): perl-URI-1.73-3.el8.noarch.rpm 5.1 MB/s | 116 kB 00:00
(44/57): perl-libnet-3.11-3.el8.noarch.rpm 4.3 MB/s | 121 kB 00:00
(45/57): python3-argcomplete-1.9.3-6.el8.noarch.rpm 2.1 MB/s | 60 kB 00:00
(46/57): python3-pip-9.0.3-22.el8.noarch.rpm 851 kB/s | 19 kB 00:00
(47/57): python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64.rpm 692 kB/s | 18 kB 00:00
(48/57): perl-interpreter-5.26.3-421.el8.x86_64.rpm 9.6 MB/s | 6.3 MB 00:00
(49/57): barman-3.3.0-1.rhel8.noarch.rpm 30 kB/s | 48 kB 00:01
(50/57): python3-barman-3.3.0-1.rhel8.noarch.rpm 281 kB/s | 455 kB 00:01
(51/57): python3-psycopg2-2.8.6-1.rhel8.x86_64.rpm 116 kB/s | 178 kB 00:01
(52/57): pg_stat_kcache_14-2.2.1-2.rhel8.x86_64.rpm 103 kB/s | 47 kB 00:00
(53/57): postgresql14-14.6-1PGDG.rhel8.x86_64.rpm 2.9 MB/s | 1.5 MB 00:00
(54/57): postgresql14-contrib-14.6-1PGDG.rhel8.x86_64.rpm 1.3 MB/s | 726 kB 00:00
(55/57): repmgr_14-5.3.3-1.rhel8.x86_64.rpm 1.4 MB/s | 284 kB 00:00
(56/57): postgresql14-server-14.6-1PGDG.rhel8.x86_64.rpm 9.8 MB/s | 5.7 MB 00:00
(57/57): postgresql14-libs-14.6-1PGDG.rhel8.x86_64.rpm 408 kB/s | 278 kB 00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 5.5 MB/s | 31 MB 00:05
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
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
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : perl-Digest-1.17-395.el8.noarch 1/57
Installing : perl-Digest-MD5-2.55-396.el8.x86_64 2/57
Installing : perl-Data-Dumper-2.167-399.el8.x86_64 3/57
Installing : perl-libnet-3.11-3.el8.noarch 4/57
Installing : perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64 5/57
Installing : perl-URI-1.73-3.el8.noarch 6/57
Installing : perl-Pod-Escapes-1:1.07-395.el8.noarch 7/57
Installing : perl-Time-Local-1:1.280-1.el8.noarch 8/57
Installing : perl-IO-Socket-IP-0.39-5.el8.noarch 9/57
Installing : perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch 10/57
Installing : perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch 11/57
Installing : perl-Term-ANSIColor-4.06-396.el8.noarch 12/57
Installing : perl-Term-Cap-1.17-395.el8.noarch 13/57
Installing : perl-File-Temp-0.230.600-1.el8.noarch 14/57
Installing : perl-Pod-Simple-1:3.35-395.el8.noarch 15/57
Installing : perl-HTTP-Tiny-0.074-1.el8.noarch 16/57
Installing : perl-podlators-4.11-1.el8.noarch 17/57
Installing : perl-Pod-Perldoc-3.28-396.el8.noarch 18/57
Installing : perl-Text-ParseWords-3.30-395.el8.noarch 19/57
Installing : perl-Pod-Usage-4:1.69-395.el8.noarch 20/57
Installing : perl-MIME-Base64-3.15-396.el8.x86_64 21/57
Installing : perl-Storable-1:3.11-3.el8.x86_64 22/57
Installing : perl-Getopt-Long-1:2.50-4.el8.noarch 23/57
Installing : perl-Errno-1.28-421.el8.x86_64 24/57
Installing : perl-Socket-4:2.027-3.el8.x86_64 25/57
Installing : perl-Encode-4:2.97-3.el8.x86_64 26/57
Installing : perl-Exporter-5.72-396.el8.noarch 27/57
Installing : perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 28/57
Installing : perl-macros-4:5.26.3-421.el8.x86_64 29/57
Installing : perl-parent-1:0.237-1.el8.noarch 30/57
Installing : perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch 31/57
Installing : perl-Unicode-Normalize-1.25-396.el8.x86_64 32/57
Installing : perl-File-Path-2.15-2.el8.noarch 33/57
Installing : perl-IO-1.38-421.el8.x86_64 34/57
Installing : perl-PathTools-3.74-1.el8.x86_64 35/57
Installing : perl-constant-1.33-396.el8.noarch 36/57
Installing : perl-threads-1:2.21-2.el8.x86_64 37/57
Installing : perl-threads-shared-1.58-2.el8.x86_64 38/57
Installing : perl-libs-4:5.26.3-421.el8.x86_64 39/57
Installing : perl-Carp-1.42-396.el8.noarch 40/57
Installing : perl-interpreter-4:5.26.3-421.el8.x86_64 41/57
Installing : postgresql14-libs-14.6-1PGDG.rhel8.x86_64 42/57
Running scriptlet: postgresql14-libs-14.6-1PGDG.rhel8.x86_64 42/57
Installing : python3-setuptools-39.2.0-6.el8.noarch 43/57
Installing : python3-pip-9.0.3-22.el8.noarch 44/57
Installing : python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64 45/57
Running scriptlet: python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64 45/57
Installing : libicu-60.3-2.el8_1.x86_64 46/57
Running scriptlet: libicu-60.3-2.el8_1.x86_64 46/57
Installing : python3-psycopg2-2.8.6-1.rhel8.x86_64 47/57
Installing : python3-argcomplete-1.9.3-6.el8.noarch 48/57
Installing : python3-barman-3.3.0-1.rhel8.noarch 49/57
Installing : rsync-3.1.3-19.el8.x86_64 50/57
Installing : lz4-1.8.3-3.el8_4.x86_64 51/57
Installing : postgresql14-14.6-1PGDG.rhel8.x86_64 52/57
Running scriptlet: postgresql14-14.6-1PGDG.rhel8.x86_64 52/57
Running scriptlet: postgresql14-server-14.6-1PGDG.rhel8.x86_64 53/57
Installing : postgresql14-server-14.6-1PGDG.rhel8.x86_64 53/57
Running scriptlet: postgresql14-server-14.6-1PGDG.rhel8.x86_64 53/57
Installing : pg_stat_kcache_14-2.2.1-2.rhel8.x86_64 54/57
Running scriptlet: pg_stat_kcache_14-2.2.1-2.rhel8.x86_64 54/57
Installing : postgresql14-contrib-14.6-1PGDG.rhel8.x86_64 55/57
Running scriptlet: repmgr_14-5.3.3-1.rhel8.x86_64 56/57
Installing : repmgr_14-5.3.3-1.rhel8.x86_64 56/57
Running scriptlet: repmgr_14-5.3.3-1.rhel8.x86_64 56/57
Running scriptlet: barman-3.3.0-1.rhel8.noarch 57/57
Installing : barman-3.3.0-1.rhel8.noarch 57/57
Running scriptlet: barman-3.3.0-1.rhel8.noarch 57/57
Verifying : libicu-60.3-2.el8_1.x86_64 1/57
Verifying : lz4-1.8.3-3.el8_4.x86_64 2/57
Verifying : perl-Carp-1.42-396.el8.noarch 3/57
Verifying : perl-Data-Dumper-2.167-399.el8.x86_64 4/57
Verifying : perl-Encode-4:2.97-3.el8.x86_64 5/57
Verifying : perl-Errno-1.28-421.el8.x86_64 6/57
Verifying : perl-Exporter-5.72-396.el8.noarch 7/57
Verifying : perl-File-Path-2.15-2.el8.noarch 8/57
Verifying : perl-File-Temp-0.230.600-1.el8.noarch 9/57
Verifying : perl-Getopt-Long-1:2.50-4.el8.noarch 10/57
Verifying : perl-HTTP-Tiny-0.074-1.el8.noarch 11/57
Verifying : perl-IO-1.38-421.el8.x86_64 12/57
Verifying : perl-MIME-Base64-3.15-396.el8.x86_64 13/57
Verifying : perl-PathTools-3.74-1.el8.x86_64 14/57
Verifying : perl-Pod-Escapes-1:1.07-395.el8.noarch 15/57
Verifying : perl-Pod-Perldoc-3.28-396.el8.noarch 16/57
Verifying : perl-Pod-Simple-1:3.35-395.el8.noarch 17/57
Verifying : perl-Pod-Usage-4:1.69-395.el8.noarch 18/57
Verifying : perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 19/57
Verifying : perl-Socket-4:2.027-3.el8.x86_64 20/57
Verifying : perl-Storable-1:3.11-3.el8.x86_64 21/57
Verifying : perl-Term-ANSIColor-4.06-396.el8.noarch 22/57
Verifying : perl-Term-Cap-1.17-395.el8.noarch 23/57
Verifying : perl-Text-ParseWords-3.30-395.el8.noarch 24/57
Verifying : perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch 25/57
Verifying : perl-Time-Local-1:1.280-1.el8.noarch 26/57
Verifying : perl-Unicode-Normalize-1.25-396.el8.x86_64 27/57
Verifying : perl-constant-1.33-396.el8.noarch 28/57
Verifying : perl-interpreter-4:5.26.3-421.el8.x86_64 29/57
Verifying : perl-libs-4:5.26.3-421.el8.x86_64 30/57
Verifying : perl-macros-4:5.26.3-421.el8.x86_64 31/57
Verifying : perl-parent-1:0.237-1.el8.noarch 32/57
Verifying : perl-podlators-4.11-1.el8.noarch 33/57
Verifying : perl-threads-1:2.21-2.el8.x86_64 34/57
Verifying : perl-threads-shared-1.58-2.el8.x86_64 35/57
Verifying : python3-setuptools-39.2.0-6.el8.noarch 36/57
Verifying : rsync-3.1.3-19.el8.x86_64 37/57
Verifying : perl-Digest-1.17-395.el8.noarch 38/57
Verifying : perl-Digest-MD5-2.55-396.el8.x86_64 39/57
Verifying : perl-IO-Socket-IP-0.39-5.el8.noarch 40/57
Verifying : perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch 41/57
Verifying : perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch 42/57
Verifying : perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64 43/57
Verifying : perl-URI-1.73-3.el8.noarch 44/57
Verifying : perl-libnet-3.11-3.el8.noarch 45/57
Verifying : python3-argcomplete-1.9.3-6.el8.noarch 46/57
Verifying : python3-pip-9.0.3-22.el8.noarch 47/57
Verifying : python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64 48/57
Verifying : barman-3.3.0-1.rhel8.noarch 49/57
Verifying : python3-barman-3.3.0-1.rhel8.noarch 50/57
Verifying : python3-psycopg2-2.8.6-1.rhel8.x86_64 51/57
Verifying : pg_stat_kcache_14-2.2.1-2.rhel8.x86_64 52/57
Verifying : postgresql14-14.6-1PGDG.rhel8.x86_64 53/57
Verifying : postgresql14-contrib-14.6-1PGDG.rhel8.x86_64 54/57
Verifying : postgresql14-libs-14.6-1PGDG.rhel8.x86_64 55/57
Verifying : postgresql14-server-14.6-1PGDG.rhel8.x86_64 56/57
Verifying : repmgr_14-5.3.3-1.rhel8.x86_64 57/57
Installed:
barman-3.3.0-1.rhel8.noarch libicu-60.3-2.el8_1.x86_64 lz4-1.8.3-3.el8_4.x86_64 perl-Carp-1.42-396.el8.noarch perl-Data-Dumper-2.167-399.el8.x86_64
perl-Digest-1.17-395.el8.noarch perl-Digest-MD5-2.55-396.el8.x86_64 perl-Encode-4:2.97-3.el8.x86_64 perl-Errno-1.28-421.el8.x86_64 perl-Exporter-5.72-396.el8.noarch
perl-File-Path-2.15-2.el8.noarch perl-File-Temp-0.230.600-1.el8.noarch perl-Getopt-Long-1:2.50-4.el8.noarch perl-HTTP-Tiny-0.074-1.el8.noarch perl-IO-1.38-421.el8.x86_64
perl-IO-Socket-IP-0.39-5.el8.noarch perl-IO-Socket-SSL-2.066-4.module_el8.6.0+2811+fe6c84b0.noarch perl-MIME-Base64-3.15-396.el8.x86_64 perl-Mozilla-CA-20160104-7.module_el8.5.0+2812+ed912d05.noarch perl-Net-SSLeay-1.88-2.module_el8.6.0+2811+fe6c84b0.x86_64
perl-PathTools-3.74-1.el8.x86_64 perl-Pod-Escapes-1:1.07-395.el8.noarch perl-Pod-Perldoc-3.28-396.el8.noarch perl-Pod-Simple-1:3.35-395.el8.noarch perl-Pod-Usage-4:1.69-395.el8.noarch
perl-Scalar-List-Utils-3:1.49-2.el8.x86_64 perl-Socket-4:2.027-3.el8.x86_64 perl-Storable-1:3.11-3.el8.x86_64 perl-Term-ANSIColor-4.06-396.el8.noarch perl-Term-Cap-1.17-395.el8.noarch
perl-Text-ParseWords-3.30-395.el8.noarch perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch perl-Time-Local-1:1.280-1.el8.noarch perl-URI-1.73-3.el8.noarch perl-Unicode-Normalize-1.25-396.el8.x86_64
perl-constant-1.33-396.el8.noarch perl-interpreter-4:5.26.3-421.el8.x86_64 perl-libnet-3.11-3.el8.noarch perl-libs-4:5.26.3-421.el8.x86_64 perl-macros-4:5.26.3-421.el8.x86_64
perl-parent-1:0.237-1.el8.noarch perl-podlators-4.11-1.el8.noarch perl-threads-1:2.21-2.el8.x86_64 perl-threads-shared-1.58-2.el8.x86_64 pg_stat_kcache_14-2.2.1-2.rhel8.x86_64
postgresql14-14.6-1PGDG.rhel8.x86_64 postgresql14-contrib-14.6-1PGDG.rhel8.x86_64 postgresql14-libs-14.6-1PGDG.rhel8.x86_64 postgresql14-server-14.6-1PGDG.rhel8.x86_64 python3-argcomplete-1.9.3-6.el8.noarch
python3-barman-3.3.0-1.rhel8.noarch python3-pip-9.0.3-22.el8.noarch python3-psycopg2-2.8.6-1.rhel8.x86_64 python3-setuptools-39.2.0-6.el8.noarch python36-3.6.8-38.module_el8.5.0+2569+5c5719bc.x86_64
repmgr_14-5.3.3-1.rhel8.x86_64 rsync-3.1.3-19.el8.x86_64
Complete!
Creating override.conf
[Service]
Environment=PGDATA=/pgdata/14/data
Initialyze PostgreSQL 14
Initializing database ... OK
Enable PostgreSQL Service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
success
success
[root@el8-repmgr-2 install]#
The difference to the Master / Leader installation is that the service is not started, we need an empty pgdata for creating the replica. Again after installation of PostgreSQL we need to change the ownership of the config scripts to postgresql.
[root@el8-repmgr-2 pgsql]# chown -R postgres:postgres config/
Again the setup of .pgpass.
[postgres@el8-repmgr-2 config]$ sh 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@el8-repmgr-2 config]$
[postgres@el8-repmgr-2 config]$ sh pgpass.sh -p el8-repmgr-1 -s el8-repmgr2
2022.12.22-22:19:53 [4695]INFO: #host:port:database:user:password in /var/lib/pgsql/.pgpass
#host:port:database:user:password
2022.12.22-22:19:53 [4695]INFO: Setting localhost in
localhost:5432:*:repmgr:PutYourPasswordHere
2022.12.22-22:19:53 [4695]INFO: Setting 127.0.0.1 in /var/lib/pgsql/.pgpass
127.0.0.1:5432:*:repmgr:PutYourPasswordHere
2022.12.22-22:19:53 [4695]INFO: Setting Primary el8-repmgr-1 in /var/lib/pgsql/.pgpass
el8-repmgr-1.localdomain:5432:*:repmgr:PutYourPasswordHere
2022.12.22-22:19:53 [4695]INFO: Setting Primary el8-repmgr2 in /var/lib/pgsql/.pgpass
el8-repmgr2.localdomain:5432:*:repmgr:PutYourPasswordHere
[postgres@el8-repmgr-2 config]$
Now we can start the setup of the replica node, again, the script contains a help wiht -h.
[postgres@el8-repmgr-2 config]$ sh repMgrStandbyNEW_setup.sh -h
Usage:
repMgrStandbyNEW_setup.sh [OPTION]
Options:
-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 like 11 or 12 (required)
-h <Help> prints this help
[postgres@el8-repmgr-2 config]$
[root@el8-repmgr-2 config]# [postgres@el8-repmgr-2 config]$ sh repMgrStandbyNEW_setup.sh -p el8-repmgr-1 -s el8-repmgr-2 -v 14
2022.12.22-22:58:50 [6741]INFO: Setting node_id=2 in /etc/repmgr/14/repmgr.conf
node_id=2
2022.12.22-22:58:50 [6741]INFO: Setting node_name=el8-repmgr-2 in /etc/repmgr/14/repmgr.conf
node_name=el8-repmgr-2
2022.12.22-22:58:50 [6741]INFO: Setting conninfo='host=el8-repmgr-2.localdomain user=repmgr dbname=repmgrdb' in /etc/repmgr/14/repmgr.conf
conninfo='host=el8-repmgr-2.localdomain user=repmgr dbname=repmgrdb'
2022.12.22-22:58:50 [6741]Info: Setting 'use_replication_slots=true' in /etc/repmgr/14/repmgr.conf
use_replication_slots=true
2022.12.22-22:58:50 [6741]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=el8-repmgr-1.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 el8-repmgr-1.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.12.22-22:58:51 [6741]INFO: Registering standby returned 0
INFO: connecting to local node "el8-repmgr-2" (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 "el8-repmgr-2" (ID: 2) successfully registered
setup of standby successfully completed
[postgres@el8-repmgr-2 config]$
Now the cluster is up and running.
[postgres@el8-repmgr-1 ~]$ /usr/pgsql-14/bin/repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+--------------+---------+-----------+--------------+----------+----------+----------+-----------------------------------------------------------
1 | el8-repmgr-1 | primary | * running | | default | 100 | 1 | host=el8-repmgr-1.localdomain user=repmgr dbname=repmgrdb
2 | el8-repmgr-2 | standby | running | el8-repmgr-1 | default | 100 | 1 | host=el8-repmgr-2.localdomain user=repmgr dbname=repmgrdb
[postgres@el8-repmgr-1 ~]$
The auto failover trick in repmgr.conf:
node_id=1
node_name=el8-repmgr-1
conninfo='host=el8-repmgr-1.localdomain user=repmgr dbname=repmgrdb'
use_replication_slots=true
data_directory='/pgdata/14/data'
failover='automatic'
promote_command='/usr/pgsql-14/bin/repmgr standby promote -f /etc/repmgr/14/repmgr.conf --log-to-file'
follow_command='/usr/pgsql-14/bin/repmgr standby follow -f /etc/repmgr/14/repmgr.conf --log-to-file --upstream-node-id=%n'
monitor_interval_secs=2
connection_check_type='ping'
reconnect_attempts=6
reconnect_interval=10
standby_disconnect_on_failover=true
service_start_command='sudo /usr/bin/systemctl start postgresql-14.service'
service_stop_command='sudo /usr/bin/systemctl stop postgresql-14.service'
service_restart_command='sudo /usr/bin/systemctl restart postgresql-14.service'
service_reload_command='sudo /usr/bin/systemctl reload postgresql-14.service'
failover
The failover parameter is one of the mandatory parameters for the repmgr daemon. This parameter tells the daemon if it should initiate an automatic failover when a failover situation is detected. It can have either of two values: “manual” or “automatic”. We will set this to automatic in each node.
promote_command
This is another mandatory parameter for the repmgr daemon. This parameter tells the repmgr daemon what command it should run to promote a standby. The value of this parameter will be typically the “repmgr standby promote” command, or the path to a shell script that calls the command
follow_command
This is the third mandatory parameter for the repmgr daemon. This parameter tells a standby node to follow the new primary. The repmgr daemon replaces the %n placeholder with the node ID of the new primary at run time.
monitor_interval_secs
This parameter tells the repmgr daemon how often (in number of seconds) it should check the availability of the upstream node. In our case, there is only one upstream node: the primary node. The default value is 2 seconds, but we will explicitly set this anyhow in each node.
connection_check_type
The connection_check_type parameter dictates the protocol repmgr daemon will use to reach out to the upstream node. This parameter can take three values:
- ping: repmgr uses the PQPing() method
- connection: repmgr tries to create a new connection to the upstream node
- query: repmgr tries to run a SQL query on the upstream node using the existing connection
Again, we will set this parameter to the default value of ping in each node.
reconnect_attempts and reconnect_interval
When the primary becomes unavailable, the repmgr daemon in the standby nodes will try to reconnect to the primary for reconnect_attempts times. The default value for this parameter is 6. Between each reconnect attempt, it will wait for reconnect_interval seconds, which has a default value of 10. For demonstration purposes, we will use a short interval and fewer reconnect attempts. We set this parameter in every node.
standby_disconnect_on_failover
When the standby_disconnect_on_failover parameter is set to “true” in a standby node, the repmgr daemon will ensure its WAL receiver is disconnected from the primary and not receiving any WAL segments. It will also wait for the WAL receivers of other standby nodes to stop before making a failover decision. This parameter should be set to the same value in each node. We are setting this to “true”.
This is a small two node cluster, the next blog wil be extending to multi node cluster and upgrading to PostgreSQL 15.
![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)
Markoni
17.04.2024Can you please share the script: repMgrStandbyNEW_setup.sh, me too? Thank you very much for great tutorial and scripts!
Karsten Lenz
18.04.2024Hello, I have send the scripts to you. Regards