What is Patroni?
Patroni is an open source cluster management tool developed with python that provides a high availability solution for postgresql. It is a very important and useful tool in terms of managing switchover-failover scenarios quite successfully. We will have three postgresql nodes and two haproxy servers, database connections will be established over virtual ip’s directed to the haproxy servers. The architecture is as follows, I will prepare special posts for pgbackrest and percona.
What is ETCD?
ETCD is used as a distributed configuration store (DCS). It stores the PostgreSQL cluster state. Whenever there is a change in the state of any PostgreSQL node, the boss updates the state change in the etcd key-value store. ETCD uses this information to select the leader node and keeps the cluster up and running. For High Availability it is recommended to install 3 etcd for 3 db servers separately. During installation, custom parameters should be defined in patroni.yaml
Servers:
10.30.10.118 - Node1 10.30.10.119 - Node2 10.30.10.120 - Node3 10.30.10.121 - HAProxy1 10.30.10.122 - HAProxy2
Instalasi etcd dari repository apt / package
root@mylabs-db-01:~# sudo apt-get update Hit:1 http://id.archive.ubuntu.com/ubuntu jammy InRelease Get:2 http://id.archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB] Hit:3 http://id.archive.ubuntu.com/ubuntu jammy-backports InRelease Get:4 http://id.archive.ubuntu.com/ubuntu jammy-security InRelease [110 kB] Get:5 http://id.archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [1,268 kB] Get:6 http://id.archive.ubuntu.com/ubuntu jammy-updates/restricted amd64 Packages [1,259 kB] Get:7 http://id.archive.ubuntu.com/ubuntu jammy-updates/restricted Translation-en [205 kB] Get:8 http://id.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [1,021 kB] Get:9 http://id.archive.ubuntu.com/ubuntu jammy-updates/multiverse amd64 Packages [42.1 kB] Get:10 http://id.archive.ubuntu.com/ubuntu jammy-updates/multiverse Translation-en [10.1 kB] Get:11 http://id.archive.ubuntu.com/ubuntu jammy-security/main amd64 Packages [1,058 kB] Get:12 http://id.archive.ubuntu.com/ubuntu jammy-security/main Translation-en [200 kB] Get:13 http://id.archive.ubuntu.com/ubuntu jammy-security/restricted amd64 Packages [1,237 kB] Get:14 http://id.archive.ubuntu.com/ubuntu jammy-security/restricted Translation-en [202 kB] Get:15 http://id.archive.ubuntu.com/ubuntu jammy-security/universe amd64 Packages [824 kB] Get:16 http://id.archive.ubuntu.com/ubuntu jammy-security/multiverse amd64 Packages [37.1 kB] Get:17 http://id.archive.ubuntu.com/ubuntu jammy-security/multiverse Translation-en [7,476 B] Fetched 7,601 kB in 2s (5,025 kB/s) Reading package lists... Done root@mylabs-db-01:~#
root@mylabs-db-01:~# apt install etcd Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: etcd-client etcd-server pipexec The following NEW packages will be installed: etcd etcd-client etcd-server pipexec 0 upgraded, 4 newly installed, 0 to remove and 102 not upgraded. Need to get 10.4 MB of archives. After this operation, 34.9 MB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://id.archive.ubuntu.com/ubuntu jammy/universe amd64 pipexec amd64 2.5.5-2 [17.5 kB] Get:2 http://id.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 etcd-server amd64 3.3.25+dfsg-7ubuntu0.22.04.1 [5,843 kB] Get:3 http://id.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 etcd-client amd64 3.3.25+dfsg-7ubuntu0.22.04.1 [4,575 kB] Get:4 http://id.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 etcd all 3.3.25+dfsg-7ubuntu0.22.04.1 [2,296 B] Fetched 10.4 MB in 2s (6,941 kB/s) Selecting previously unselected package pipexec. (Reading database ... 109337 files and directories currently installed.) Preparing to unpack .../pipexec_2.5.5-2_amd64.deb ... Unpacking pipexec (2.5.5-2) ... Selecting previously unselected package etcd-server. Preparing to unpack .../etcd-server_3.3.25+dfsg-7ubuntu0.22.04.1_amd64.deb ... Unpacking etcd-server (3.3.25+dfsg-7ubuntu0.22.04.1) ... Selecting previously unselected package etcd-client. Preparing to unpack .../etcd-client_3.3.25+dfsg-7ubuntu0.22.04.1_amd64.deb ... Unpacking etcd-client (3.3.25+dfsg-7ubuntu0.22.04.1) ... Selecting previously unselected package etcd. Preparing to unpack .../etcd_3.3.25+dfsg-7ubuntu0.22.04.1_all.deb ... Unpacking etcd (3.3.25+dfsg-7ubuntu0.22.04.1) ... Setting up pipexec (2.5.5-2) ... Setting up etcd-client (3.3.25+dfsg-7ubuntu0.22.04.1) ... Setting up etcd-server (3.3.25+dfsg-7ubuntu0.22.04.1) ... Adding system user `etcd' (UID 113) ... Adding new group `etcd' (GID 118) ... Adding new user `etcd' (UID 113) with group `etcd' ... Creating home directory `/var/lib/etcd/' ... Created symlink /etc/systemd/system/etcd2.service → /lib/systemd/system/etcd.service. Created symlink /etc/systemd/system/multi-user.target.wants/etcd.service → /lib/systemd/system/etcd.service. Setting up etcd (3.3.25+dfsg-7ubuntu0.22.04.1) ... Processing triggers for man-db (2.10.2-1) ... Scanning processes... Scanning candidates... Scanning linux images... Restarting services... /etc/needrestart/restart.d/systemd-manager systemctl restart cron.service irqbalance.service multipathd.service open-vm-tools.service packagekit.service polkit.service rsyslog.service snapd.service systemd-journald.service systemd-networkd.service systemd-resolved.service systemd-timesyncd.service systemd-udevd.service udisks2.service vgauth.service Service restarts being deferred: systemctl restart ModemManager.service /etc/needrestart/restart.d/dbus.service systemctl restart networkd-dispatcher.service systemctl restart systemd-logind.service systemctl restart unattended-upgrades.service systemctl restart [email protected] No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host. root@mylabs-db-01:~#
root@mylabs-db-01:~# systemctl status etcd.service
● etcd.service - etcd - highly-available key value store
Loaded: loaded (/lib/systemd/system/etcd.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2024-01-08 23:40:55 UTC; 32s ago
Docs: https://etcd.io/docs
man:etcd
Main PID: 102660 (etcd)
Tasks: 9 (limit: 9365)
Memory: 5.4M
CPU: 116ms
CGroup: /system.slice/etcd.service
└─102660 /usr/bin/etcd
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: 8e9e05c52164694d received MsgVoteResp from 8e9e05c52164694d at term 2
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: 8e9e05c52164694d became leader at term 2
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: raft.node: 8e9e05c52164694d elected leader 8e9e05c52164694d at term 2
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: setting up the initial cluster version to 3.3
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: ready to serve client requests
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: published {Name:mylabs-db-01 ClientURLs:[http://localhost:2379]} to cluster >
Jan 08 23:40:55 mylabs-db-01 systemd[1]: Started etcd - highly-available key value store.
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: serving insecure client requests on 127.0.0.1:2379, this is strongly dis>
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: set the initial cluster version to 3.3
Jan 08 23:40:55 mylabs-db-01 etcd[102660]: enabled capabilities for version 3.3
root@mylabs-db-01:~#
root@mylabs-db-01:~# etcd --version
etcd Version: 3.3.25
Git SHA: Not provided (use ./build instead of go build)
Go Version: go1.18.1
Go OS/Arch: linux/amd64
root@mylabs-db-01:~#Instalasi etcd dari source
Mendownload binary etcd dari github
root@mylabs-db-01:~# wget -q --show-progress "https://github.com/etcd-io/etcd/releases/download/v3.5.14/etcd-v3.5.14-linux-amd64.tar.gz" etcd-v3.5.14-linux-amd64.tar.gz 100%[=============================================================================================================>] 18.49M 27.4MB/s in 0.7s root@mylabs-db-01:~#
Melakukan extract dan copy binary kedalam directory bin, kemudian memberikan permission executable ke file binary dari etcd sebagaimana console berikut:
root@mylabs-db-01:~# tar zxf etcd-v3.5.14-linux-amd64.tar.gz root@mylabs-db-01:~# root@mylabs-db-01:~# mv etcd-v3.5.14-linux-amd64/etcd* /usr/bin/ root@mylabs-db-01:~# chmod +x /usr/bin/etcd* root@mylabs-db-01:~#
Membuat directory /etc/etcd dan membuat file etcd untuk menyimpan konfigurasi environment
root@mylabs-db-01:~# mkdir /etc/etcd root@mylabs-db-01:~# nano /etc/etcd/etcd root@mylabs-db-01:~#
Membuat user etcd
root@mylabs-db-01:~# groupadd --system etcd root@mylabs-db-01:~# useradd -s /sbin/nologin --system -g etcd etcd root@mylabs-db-01:~#
Membuat directory untuk data etcd
root@mylabs-db-01:~# mkdir -p /var/lib/etcd root@mylabs-db-01:~# chown -R etcd:etcd /var/lib/etcd root@mylabs-db-01:~# chmod -R 700 /var/lib/etcd
Konfigrasi node-1 pada file /etc/etcd/etcd
ETCD_NAME=etcd1 ETCD_DATA_DIR=/var/lib/etcd ETCD_LISTEN_CLIENT_URLS=http://10.30.10.118:2379,http://127.0.0.1:2379 ETCD_LISTEN_PEER_URLS=http://10.30.10.118:2380 ETCD_ADVERTISE_CLIENT_URLS=http://10.30.10.118:2379 ETCD_INITIAL_ADVERTISE_PEER_URLS=http://10.30.10.118:2380 ETCD_INITIAL_CLUSTER=etcd1=http://10.30.10.118:2380,etcd2=http://10.30.10.119:2380,etcd3=http://10.30.10.120:2380 ETCD_INITIAL_CLUSTER_STATE=new ETCD_INITIAL_CLUSTER_TOKEN=etcd-cluster-xpass ETCD_ENABLE_V2=true
Konfigrasi node-2 pada file /etc/etcd/etcd
ETCD_NAME=etcd2 ETCD_DATA_DIR=/var/lib/etcd ETCD_LISTEN_CLIENT_URLS=http://10.30.10.119:2379,http://127.0.0.1:2379 ETCD_LISTEN_PEER_URLS=http://10.30.10.119:2380 ETCD_ADVERTISE_CLIENT_URLS=http://10.30.10.119:2379 ETCD_INITIAL_ADVERTISE_PEER_URLS=http://10.30.10.119:2380 ETCD_INITIAL_CLUSTER=etcd1=http://10.30.10.118:2380,etcd2=http://10.30.10.119:2380,etcd3=http://10.30.10.120:2380 ETCD_INITIAL_CLUSTER_STATE=new ETCD_INITIAL_CLUSTER_TOKEN=etcd-cluster-xpass ETCD_ENABLE_V2=true
Konfigrasi node-3 pada file /etc/etcd/etcd
ETCD_NAME=etcd3 ETCD_DATA_DIR=/var/lib/etcd ETCD_LISTEN_CLIENT_URLS=http://10.30.10.120:2379,http://127.0.0.1:2379 ETCD_LISTEN_PEER_URLS=http://10.30.10.120:2380 ETCD_ADVERTISE_CLIENT_URLS=http://10.30.10.120:2379 ETCD_INITIAL_ADVERTISE_PEER_URLS=http://10.30.10.120:2380 ETCD_INITIAL_CLUSTER=etcd1=http://10.30.10.118:2380,etcd2=http://10.30.10.119:2380,etcd3=http://10.30.10.120:2380 ETCD_INITIAL_CLUSTER_STATE=new ETCD_INITIAL_CLUSTER_TOKEN=etcd-cluster-xpass ETCD_ENABLE_V2=true
Menyalakan service etcd
# cat /etc/systemd/system/etcd.service [Unit] Description=etcd - highly-available key value store Documentation=https://etcd.io/docs Documentation=man:etcd After=network.target Wants=network-online.target [Service] Type=notify EnvironmentFile=/etc/etcd/etcd ExecStart=/usr/bin/etcd Restart=on-failure RestartSec=5 Type=notify User=etcd LimitNOFILE=65536 [Install] WantedBy=multi-user.target Alias=etcd2.service
Mereload daemon systemctl, mensetting enable pada restart dan menyalakan services
root@mylabs-db-01:~# systemctl daemon-reload root@mylabs-db-01:~# systemctl enable etcd root@mylabs-db-01:~# systemctl start etcd
Melakukan pengecekan kesehatan
root@mylabs-db-01:~# etcdctl --cluster=true endpoint health http://10.30.10.120:2379 is healthy: successfully committed proposal: took = 2.034444ms http://10.30.10.119:2379 is healthy: successfully committed proposal: took = 2.056611ms http://10.30.10.118:2379 is healthy: successfully committed proposal: took = 1.858091ms root@mylabs-db-01:~#
Melakukan pengecekan cluster etcd
root@mylabs-db-01:~# ENDPOINTS=10.30.10.118:2379,10.30.10.119:2379,10.30.10.120:2379 root@mylabs-db-01:~# etcdctl endpoint status --write-out=table --endpoints=$ENDPOINTS +-------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | +-------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | 10.30.10.118:2379 | d422b005b01747ce | 3.5.0 | 20 kB | false | false | 3 | 19 | 19 | | | 10.30.10.119:2379 | 3f2ffd9d700ef386 | 3.5.0 | 20 kB | false | false | 3 | 19 | 19 | | | 10.30.10.120:2379 | e91ce6ea3bacdd72 | 3.5.0 | 20 kB | true | false | 3 | 19 | 19 | | +-------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ root@mylabs-db-01:~#
Instalasi database postgresql
root@mylabs-db-01:~# apt-get install postgresql postgresql-contrib -y Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: etcd-client etcd-server libflashrom1 libftdi1-2 pipexec ... ... ... No VM guests are running outdated hypervisor (qemu) binaries on this host. root@mylabs-db-01:~#
root@mylabs-db-01:~# systemctl stop postgresql
root@mylabs-db-01:~# systemctl disable postgresql
root@mylabs-db-01:~# mv /var/lib/postgresql/14/main{,-bck}
root@mylabs-db-01:~#
Lakukan pada node 2 dan node 3 juga
Instalasi pgbackrest
root@mylabs-db-01:~# apt install pgbackrest Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: etcd-client etcd-server libflashrom1 libftdi1-2 pipexec Use 'sudo apt autoremove' to remove them. Suggested packages: pgbackrest-doc check-pgbackrest The following NEW packages will be installed: pgbackrest 0 upgraded, 1 newly installed, 0 to remove and 4 not upgraded. Need to get 426 kB of archives. After this operation, 1,095 kB of additional disk space will be used. Get:1 http://id.archive.ubuntu.com/ubuntu jammy/universe amd64 pgbackrest amd64 2.37-1 [426 kB] Fetched 426 kB in 1s (490 kB/s) Selecting previously unselected package pgbackrest. (Reading database ... 118909 files and directories currently installed.) Preparing to unpack .../pgbackrest_2.37-1_amd64.deb ... Unpacking pgbackrest (2.37-1) ... Setting up pgbackrest (2.37-1) ... Processing triggers for man-db (2.10.2-1) ... Scanning processes... Scanning linux images... Running kernel seems to be up-to-date. No services need to be restarted. No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host. root@mylabs-db-01:~#
Patroni Installation
root@mylabs-db-01:~# apt-get install python3-pip python3-dev libpq-dev -y Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: etcd-client etcd-server libflashrom1 libftdi1-2 pipexec Use 'sudo apt autoremove' to remove them. The following additional packages will be installed: build-essential bzip2 cpp cpp-11 dpkg-dev fakeroot fontconfig-config fonts-dejavu-core g++ g++-11 gcc gcc-11 gcc-11-base javascript-common libalgorithm-diff-perl libalgorithm-diff-xs-perl libalgorithm-merge-perl libasan6 libatomic1 libc-dev-bin libc-devtools libc6-dev libcc1-0 libcrypt-dev libdeflate0 libdpkg-perl libexpat1-dev libfakeroot libfile-fcntllock-perl libfontconfig1 libgcc-11-dev libgd3 libgomp1 libisl23 libitm1 libjbig0 libjpeg-turbo8 libjpeg8 libjs-jquery libjs-sphinxdoc libjs-underscore liblsan0 libmpc3 libnsl-dev libpython3-dev libpython3.10-dev libquadmath0 libssl-dev libstdc++-11-dev libtiff5 libtirpc-dev libtsan0 libubsan1 libwebp7 libxpm4 linux-libc-dev lto-disabled-list make manpages-dev python3-wheel python3.10-dev rpcsvc-proto zlib1g-dev .... .... .... Processing triggers for man-db (2.10.2-1) ... Processing triggers for libc-bin (2.35-0ubuntu3.5) ... Scanning processes... Scanning linux images... Running kernel seems to be up-to-date. No services need to be restarted. No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host. root@mylabs-db-01:~#
Melakukan upgrade pip
root@mylabs-db-01:~# pip3 install --upgrade pip
Requirement already satisfied: pip in /usr/lib/python3/dist-packages (22.0.2)
Collecting pip
Downloading pip-23.3.2-py3-none-any.whl (2.1 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 2.1/2.1 MB 13.3 MB/s eta 0:00:00
Installing collected packages: pip
Attempting uninstall: pip
Found existing installation: pip 22.0.2
Not uninstalling pip at /usr/lib/python3/dist-packages, outside environment /usr
Can't uninstall 'pip'. No files were found to uninstall.
Successfully installed pip-23.3.2
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
Menginstall patroni dengan pip
root@mylabs-db-01:~# pip install patroni
Collecting patroni
Downloading patroni-3.2.1-py3-none-any.whl.metadata (11 kB)
Requirement already satisfied: urllib3!=1.21,>=1.19.1 in /usr/lib/python3/dist-packages (from patroni) (1.26.5)
Requirement already satisfied: PyYAML in /usr/lib/python3/dist-packages (from patroni) (5.4.1)
Requirement already satisfied: click>=4.1 in /usr/lib/python3/dist-packages (from patroni) (8.0.3)
Collecting prettytable>=0.7 (from patroni)
Downloading prettytable-3.9.0-py3-none-any.whl.metadata (26 kB)
Collecting python-dateutil (from patroni)
Downloading python_dateutil-2.8.2-py2.py3-none-any.whl (247 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 247.7/247.7 kB 5.0 MB/s eta 0:00:00
Collecting psutil>=2.0.0 (from patroni)
Downloading psutil-5.9.7-cp36-abi3-manylinux_2_12_x86_64.manylinux2010_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (21 kB)
Collecting ydiff>=1.2.0 (from patroni)
Downloading ydiff-1.2.tar.gz (42 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 42.8/42.8 kB 5.3 MB/s eta 0:00:00
Preparing metadata (setup.py) ... done
Collecting wcwidth (from prettytable>=0.7->patroni)
Downloading wcwidth-0.2.13-py2.py3-none-any.whl.metadata (14 kB)
Requirement already satisfied: six>=1.5 in /usr/lib/python3/dist-packages (from python-dateutil->patroni) (1.16.0)
Downloading patroni-3.2.1-py3-none-any.whl (317 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 317.3/317.3 kB 13.3 MB/s eta 0:00:00
Downloading prettytable-3.9.0-py3-none-any.whl (27 kB)
Downloading psutil-5.9.7-cp36-abi3-manylinux_2_12_x86_64.manylinux2010_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (285 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 285.5/285.5 kB 32.7 MB/s eta 0:00:00
Downloading wcwidth-0.2.13-py2.py3-none-any.whl (34 kB)
Building wheels for collected packages: ydiff
Building wheel for ydiff (setup.py) ... done
Created wheel for ydiff: filename=ydiff-1.2-py3-none-any.whl size=16646 sha256=98bf570701624a3544d39c8d2ab3fc6059f337ceeb8c96bf4b413175506e4546
Stored in directory: /root/.cache/pip/wheels/90/7e/61/6522f5bd67c80b8327ade6945c29042b6cd077a2683b8dd47c
Successfully built ydiff
Installing collected packages: ydiff, wcwidth, python-dateutil, psutil, prettytable, patroni
Successfully installed patroni-3.2.1 prettytable-3.9.0 psutil-5.9.7 python-dateutil-2.8.2 wcwidth-0.2.13 ydiff-1.2
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
root@ocp-db-01:~#
root@ocp-db-01:~# pip install python-etcd pip install psycopg2
Collecting python-etcd
Downloading python-etcd-0.4.5.tar.gz (37 kB)
Preparing metadata (setup.py) ... done
Requirement already satisfied: pip in /usr/local/lib/python3.10/dist-packages (23.3.2)
Collecting install
Downloading install-1.3.5-py3-none-any.whl (3.2 kB)
Collecting psycopg2
Downloading psycopg2-2.9.9.tar.gz (384 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 384.9/384.9 kB 7.2 MB/s eta 0:00:00
Preparing metadata (setup.py) ... done
Collecting dnspython>=1.13.0 (from python-etcd)
Downloading dnspython-2.4.2-py3-none-any.whl.metadata (4.9 kB)
Requirement already satisfied: urllib3>=1.7.1 in /usr/lib/python3/dist-packages (from python-etcd) (1.26.5)
Downloading dnspython-2.4.2-py3-none-any.whl (300 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 300.4/300.4 kB 24.8 MB/s eta 0:00:00
Building wheels for collected packages: python-etcd, psycopg2
Building wheel for python-etcd (setup.py) ... done
Created wheel for python-etcd: filename=python_etcd-0.4.5-py3-none-any.whl size=38501 sha256=c0d89a209f3df05efde9b87d3c4d77d56ac13d5b68ecd4924dbad37e40512d4c
Stored in directory: /root/.cache/pip/wheels/93/5f/1b/056db07a0ab1c0b7efe175928d2a10b614e0e00d7bab0b6496
Building wheel for psycopg2 (setup.py) ... done
Created wheel for psycopg2: filename=psycopg2-2.9.9-cp310-cp310-linux_x86_64.whl size=499025 sha256=57f3718037ae73352f3f85486fba93effe22e5f809d7c42303225991cd48ecf4
Stored in directory: /root/.cache/pip/wheels/7d/75/13/da1c6d88687ae81bf5e3cfa07d702981ba137963163472b050
Successfully built python-etcd psycopg2
Installing collected packages: psycopg2, install, dnspython, python-etcd
Successfully installed dnspython-2.4.2 install-1.3.5 psycopg2-2.9.9 python-etcd-0.4.5
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
root@mylabs-db-01:~#root@mylabs-db-01:~# pip install python-etcd pip install psycopg2
Collecting python-etcd
Downloading python-etcd-0.4.5.tar.gz (37 kB)
Preparing metadata (setup.py) ... done
Requirement already satisfied: pip in /usr/local/lib/python3.10/dist-packages (23.3.2)
Collecting install
Downloading install-1.3.5-py3-none-any.whl (3.2 kB)
Collecting psycopg2
Downloading psycopg2-2.9.9.tar.gz (384 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 384.9/384.9 kB 7.2 MB/s eta 0:00:00
Preparing metadata (setup.py) ... done
Collecting dnspython>=1.13.0 (from python-etcd)
Downloading dnspython-2.4.2-py3-none-any.whl.metadata (4.9 kB)
Requirement already satisfied: urllib3>=1.7.1 in /usr/lib/python3/dist-packages (from python-etcd) (1.26.5)
Downloading dnspython-2.4.2-py3-none-any.whl (300 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 300.4/300.4 kB 24.8 MB/s eta 0:00:00
Building wheels for collected packages: python-etcd, psycopg2
Building wheel for python-etcd (setup.py) ... done
Created wheel for python-etcd: filename=python_etcd-0.4.5-py3-none-any.whl size=38501 sha256=c0d89a209f3df05efde9b87d3c4d77d56ac13d5b68ecd4924dbad37e40512d4c
Stored in directory: /root/.cache/pip/wheels/93/5f/1b/056db07a0ab1c0b7efe175928d2a10b614e0e00d7bab0b6496
Building wheel for psycopg2 (setup.py) ... done
Created wheel for psycopg2: filename=psycopg2-2.9.9-cp310-cp310-linux_x86_64.whl size=499025 sha256=57f3718037ae73352f3f85486fba93effe22e5f809d7c42303225991cd48ecf4
Stored in directory: /root/.cache/pip/wheels/7d/75/13/da1c6d88687ae81bf5e3cfa07d702981ba137963163472b050
Successfully built python-etcd psycopg2
Installing collected packages: psycopg2, install, dnspython, python-etcd
Successfully installed dnspython-2.4.2 install-1.3.5 psycopg2-2.9.9 python-etcd-0.4.5
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
root@mylabs-db-01:~#
root@mylabs-db-01:~# mkdir /etc/patroni
root@mylabs-db-01:~# NAMESPACE="my_xlab"
root@mylabs-db-01:~# SCOPE="cluster_1"
root@mylabs-db-01:~# export NODE_NAME=`hostname -f`
root@mylabs-db-01:~# export NODE_IP=`hostname -i | awk '{print $1}'`
root@mylabs-db-01:~# DATA_DIR="/var/lib/postgresql/14/main"
root@mylabs-db-01:~# PG_BIN_DIR="/usr/lib/postgresql/14/bin"
root@mylabs-db-01:~# echo "
namespace: ${NAMESPACE}
scope: ${SCOPE}
name: ${NODE_NAME}
restapi:
listen: 0.0.0.0:8008
connect_address: ${NODE_IP}:8008
etcd:
host: ${NODE_IP}:2379
bootstrap:
# this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
slots:
percona_cluster_1:
type: physical
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
wal_keep_segments: 10
max_wal_senders: 5
max_replication_slots: 10
wal_log_hints: "on"
logging_collector: 'on'
# some desired options for 'initdb'
initdb: # Note: It needs to be a list (some options need values, others are switches)
- encoding: UTF8
- data-checksums
pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
- host replication replicator 127.0.0.1/32 trust
- host replication replicator 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
- host all all ::0/0 md5
# Additional script to be launched after initial cluster creation (will be passed the connection URL as parameter)
# post_init: /usr/local/bin/setup_cluster.sh
# Some additional users users which needs to be created after initializing new cluster
users:
admin:
password: M4s0kB0sQu^_&
options:
- createrole
- createdb
admins:
password: M4s0kB0sQu^_&
options:
- createrole
- createdb
postgresql:
cluster_name: cluster_1
listen: 0.0.0.0:5432
connect_address: ${NODE_IP}:5432
data_dir: ${DATADIR}
bin_dir: ${PG_BIN_DIR}
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: repsM4s0kB0sQu^_&
superuser:
username: postgres
password: P0sM4s0kB0sQu^_&
parameters:
unix_socket_directories: "/var/run/postgresql/"
create_replica_methods:
- basebackup
basebackup:
checkpoint: 'fast'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
" | sudo tee -a /etc/patroni/patroni.yml
root@mylabs-db-01:~#
root@mylabs-db-01:~# cat /etc/systemd/system/patroni.service [Unit] Description=Runners to orchestrate a high-availability PostgreSQL After=syslog.target network.target [Service] Type=simple User=postgres Group=postgres # Start the patroni process ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml # Send HUP to reload from patroni.yml ExecReload=/bin/kill -s HUP $MAINPID # only kill the patroni process, not its children, so it will gracefully stop postgres KillMode=process # Give a reasonable amount of time for the server to start up/shut down TimeoutSec=30 # Do not restart the service if it crashes, we want to manually inspect database on failure Restart=no [Install] WantedBy=multi-user.target root@mylabs-db-01:~#
Start service patroni pada node 01 s.d 03
root@mylabs-db-01:~# systemctl start patroni root@mylabs-db-01:~# systemctl enable patroni Created symlink /etc/systemd/system/multi-user.target.wants/patroni.service → /etc/systemd/system/patroni.service. root@mylabs-db-01:~# systemctl status patroni
Pengecekan status database
root@mylabs-db-01:~# patronictl -c /etc/patroni/patroni.yml list $SCOPE + Cluster: cluster_1 (7322051624797784337) ------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+--------------+---------+-----------+----+-----------+ | ocp-db-01 | 10.30.10.118 | Leader | running | 1 | | | ocp-db-02 | 10.30.10.119 | Replica | streaming | 1 | 0 | | ocp-db-03 | 10.30.10.120 | Replica | streaming | 1 | 0 | +-----------+--------------+---------+-----------+----+-----------+ root@mylabs-db-01:~#
Instalasi Haproxy
root@mylabs-db-01:~# apt install haproxy Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: liblua5.3-0 Suggested packages: vim-haproxy haproxy-doc The following NEW packages will be installed: haproxy liblua5.3-0 0 upgraded, 2 newly installed, 0 to remove and 102 not upgraded. Need to get 1,788 kB of archives. After this operation, 4,210 kB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://id.archive.ubuntu.com/ubuntu jammy/main amd64 liblua5.3-0 amd64 5.3.6-1build1 [140 kB] ... ... No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host. root@mylabs-db-01:~#
root@ocp-db-haproxy:~# mv /etc/haproxy/haproxy.cfg{,-bck}
root@ocp-db-haproxy:~#
root@ocp-db-haproxy:~# nano /etc/haproxy/haproxy.cfg
root@ocp-db-haproxy:~#
Konfigurasi haproxy
root@ocp-db-haproxy:~# cat /etc/haproxy/haproxy.cfg
global
maxconn 100
defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen primary
bind *:5432
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 ocp-db-01:5432 maxconn 100 check port 8008
server node2 ocp-db-02:5432 maxconn 100 check port 8008
server node3 ocp-db-03:5432 maxconn 100 check port 8008
listen standbys
balance roundrobin
bind *:5433
option httpchk OPTIONS /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 ocp-db-01:5432 maxconn 100 check port 8008
server node2 ocp-db-02:5432 maxconn 100 check port 8008
server node3 ocp-db-03:5432 maxconn 100 check port 8008
root@ocp-db-haproxy:~#
Tampilan statistik Haproxy
Switchover
root@mylabs-db-01:~# patronictl -c /etc/patroni/patroni.yml switchover Current cluster topology + Cluster: cluster_1 (7322051624797784337) ------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------------+--------------+---------+-----------+----+-----------+ | mylabs-db-01 | 10.30.10.118 | Leader | running | 1 | | | mylabs-db-02 | 10.30.10.119 | Replica | streaming | 1 | 0 | | mylabs-db-03 | 10.30.10.120 | Replica | streaming | 1 | 0 | +--------------+--------------+---------+-----------+----+-----------+ Primary [mylabs-db-01]: Candidate ['mylabs-db-01', 'mylabs-db-03'] []: mylabs-db-01 When should the switchover take place (e.g. 2024-01-09T13:42 ) [now]: now Are you sure you want to switchover cluster cluster_1, demoting current leader mylabs-db-01? [y/N]: y 2024-01-09 12:42:48.82044 Successfully switched over to "mylabs-db-02" + Cluster: cluster_1 (7322051624797784337) ------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------------+--------------+---------+-----------+----+-----------+ | mylabs-db-01 | 10.30.10.118 | Replica | stopped | | unknown | | mylabs-db-02 | 10.30.10.119 | Leader | running | 1 | | | mylabs-db-03 | 10.30.10.120 | Replica | streaming | 1 | 0 | +--------------+--------------+---------+-----------+----+-----------+ root@mylabs-db-01:~#
Pengecekan Cluster database
root@mylabs-db-03:~# patronictl -c /etc/patroni/patroni.yml list + Cluster: cluster_1 (7322051624797784337) ------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------------+--------------+---------+-----------+----+-----------+ | mylabs-db-01 | 10.30.10.118 | Replica | streaming | 2 | 0 | | mylabs-db-02 | 10.30.10.119 | Leader | running | 2 | | | mylabs-db-03 | 10.30.10.120 | Replica | streaming | 2 | 0 | +--------------+--------------+---------+-----------+----+-----------+ root@mylabs-db-03:~#
Keepalived Installation
With the Keepalived installation, you will be able to connect to the proxy server via virtual ip
[root@haproxy ~]# dnf install -y keepalived [root@haproxy ~]# cd /etc/keepalived
The following lines are added and edited in the keepalived conf file. If more than one proxy server is to be used, these changes should be made to the others as well. Two proxy nodes were used in this setup
Keepalived services are started for both nodes[root@haproxy keepalived]# systemctl start keepalived
[root@haproxy keepalived]# systemctl enable keepalived
[root@haproxy keepalived]# vi keepalived.conf
global_defs {
}
vrrp_script chk_haproxy {
script "/usr/bin/killall -0 haproxy" # widely used idiom
interval 2 # check every 2 seconds
weight 2 # add 2 points of prio if OK
}
vrrp_instance VI_1 {
interface enp0s3
state MASTER # or "BACKUP" on backup
priority 101 # 101 on master, 100 on backup
virtual_router_id 51
authentication {
auth_type PASS
auth_pass Xtr54sdD
}
virtual_ipaddress {
192.168.1.30
}
unicast_src_ip 192.168.1.28 # This haproxy node
unicast_peer {
192.168.1.29 # Other haproxy nodes
}
track_script {
chk_haproxy
}
}https://docs.percona.com/postgresql/14/solutions/ha-setup-apt.html#next-steps
https://www.techsupportpk.com/2022/01/set-up-highly-available-postgresql13-cluster-ubuntu.html
https://www.alibabacloud.com/blog/how-to-set-up-a-highly-available-postgresql-cluster-using-patroni-on-ubuntu-16-04_594477
https://snapshooter.com/learn/postgresql/postgresql-cluster-patroni
https://www.percona.com/blog/pgbackrest-restoration-scenarios/