MODOP – Partie 2 – PostgreSQL HA – Installation du Cluster PostgreSQL/Patroni

Aucun commentaire

Inventaire des machines

Cluster PostgreSQL/patroni

  • hostname : Node-psql01
    • IP : 192.168.1.103/24
    • OS : Rocky Linux 8.5
    • RAM : 2Go
    • CPU : 1
  • hostname : Node-psql02
    • IP : 192.168.1.104/24
    • OS : Rocky Linux 8.5
    • RAM : 2Go
    • CPU : 1
  • hostname : Node-psql03
    • IP : 192.168.1.105/24
    • OS : Rocky Linux 8.5
    • RAM : 2Go
    • CPU : 1

1. Mise à jour (3 nodes)

[root@node-psql0x ~]# dnf update -y

2. Ajout des Hosts (pas de DNS)

Copy de /etc/hosts node-esql01 vers les hosts psql

[root@node-psql01 ~]# scp root@192.168.1.100:/etc/hosts /etc/hosts
[root@node-psql02 ~]# scp root@192.168.1.100:/etc/hosts /etc/hosts
[root@node-psql03 ~]# scp root@192.168.1.100:/etc/hosts /etc/hosts
[root@node-psql0x ~]# cat /etc/hosts

Check résolution hosts

[root@node-psql0x ~]# ping -c 2 node-esql01
[root@node-psql0x ~]# ping -c 2 node-psql01
[root@node-psql0x ~]# ping -c 2 node-hsql01

3. Désactiver SELinux (3 nodes)

[root@node-psql0x ~]# setenforce 0
[root@node-psql0x ~]# getenforce
Permissive
[root@node-psql0x ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

4. Synchroniser DateTime sur le fuseau de PARIS (3 nodes)

[root@node-psql0x ~]# timedatectl set-timezone Europe/Paris
[root@node-psql0x ~]# timedatectl

5. Installation des middlewares (3 nodes)

[root@node-esql0x ~]# dnf install epel-release net-tools nmap curl wget tar -y

6. Installation Cluster PostgreSQL v12 (3 nodes)

Installation Repository PostgreSQL

[root@node-psql0x ~]# dnf -y install yum-utils
[root@node-psql0x ~]# dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Installation PostgreSQL v12

[root@node-psql0x ~]# dnf module list postgresql

[root@node-psql0x ~]# dnf module enable postgresql:12

[root@node-psql0x ~]# dnf --enablerepo=powertools install perl-IPC-Run
[root@node-psql0x ~]# dnf -qy module disable postgresql
[root@node-psql0x ~]# dnf -y install postgresql12 postgresql12-server postgresql12-devel
[root@node-psql0x ~]# ls /usr/pgsql-12/bin
[root@node-psql0x ~]# ln -s /usr/pgsql-12/bin/* /usr/sbin/

7. Installation Patroni (3 nodes)

Installation des prérequis (3 nodes)

[root@node-psql0x ~]# dnf -y install python3 python3-devel python3-pip gcc libpq-devel
[root@node-psql0x ~]# pip3 install --upgrade testresources
[root@node-psql0x ~]# pip3 install --upgrade setuptools

Installation patroni et dépendances (3 nodes)

[root@node-psql0x ~]# pip3 install psycopg2
[root@node-psql0x ~]# dnf -y install python3-etcd
[root@node-psql0x ~]# dnf -y install patroni patroni-etcd watchdog

Configuration patroni (3 nodes)

[root@node-psql0x ~]# mkdir -p /etc/patroni
[root@node-psql0x ~]# cp -v /usr/share/doc/patroni/postgres0.yml /etc/patroni/patroni.yml
'/usr/share/doc/patroni/postgres0.yml' -> '/etc/patroni/patroni.yml'

Configuration patroni – node-psql01

[root@node-psql01 ~]# vi /etc/patroni/patroni.yml

scope: psql_cluster
namespace: /service/
name: node-psql01

restapi:
 listen: 0.0.0.0:8008
 connect_address: 192.168.1.103:8008

etcd:
 hosts:
 - 192.168.1.100:2379
 - 192.168.1.101:2379
 - 192.168.1.102:2379

bootstrap:
 dcs:
 ttl: 30
 loop_wait: 10
 retry_timeout: 10
 maximum_lag_on_failover: 1048576

 postgresql:
 use_pg_rewind: true
 use_slots: true
 parameters:

 initdb:
 - encoding: UTF8
 - data-checksums

 pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
 - host replication replicator 127.0.0.1/32 md5
 - host replication replicator 192.168.1.103/0 md5
 - host replication replicator 192.168.1.104/0 md5
 - host replication replicator 192.168.1.105/0 md5
 - host all all 0.0.0.0/0 md5
 - host all all 192.168.1.0/24 md5

 users:
 admin:
 password: admin
 options:
 - createrole
 - createdb

postgresql:
 listen: 192.168.1.103:5432
 connect_address: 192.168.1.103:5432
 data_dir: /var/lib/pgsql/12/data
 bin_dir: /usr/pgsql-12/bin
 pgpass: /tmp/pgpass

 authentication:
 replication:
 username: replicator
 password: replicator2023
 superuser:
username: postgres
 password: chris@2023

watchdog:
 mode: required
 device: /dev/watchdog
 safety_margin: 5

tags:
 nofailover: false
 noloadbalance: false
 clonefrom: false
 nosync: false

Configuration patroni – node-esql02

[root@node-psql02 ~]# vi /etc/patroni/patroni.yml

scope: psql_cluster
namespace: /service/
name: node-psql02

restapi:
 listen: 0.0.0.0:8008
 connect_address: 192.168.1.104:8008

etcd:
 hosts:
 - 192.168.1.100:2379
 - 192.168.1.101:2379
 - 192.168.1.102:2379

bootstrap:
 dcs:
 ttl: 30
 loop_wait: 10
 retry_timeout: 10
 maximum_lag_on_failover: 1048576

 postgresql:
 use_pg_rewind: true
 use_slots: true

 parameters:
 initdb:
 - encoding: UTF8
 - data-checksums

 pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
 - host replication replicator 127.0.0.1/32 md5
 - host replication replicator 192.168.1.103/0 md5
 - host replication replicator 192.168.1.104/0 md5
 - host replication replicator 192.168.1.105/0 md5
 - host all all 0.0.0.0/0 md5
 - host all all 192.168.1.0/24 md5

 users:
 admin:
 password: admin
 options:
 - createrole
 - createdb

postgresql:
 listen: 0.0.0.0:5432
 connect_address: 192.168.1.104:5432
 data_dir: /var/lib/pgsql/12/data
 bin_dir: /usr/pgsql-12/bin
 pgpass: /tmp/pgpass

 authentication:
 replication:
 username: replicator
 password: replicator2023
 superuser:
username: postgres
 password: chris@2023

watchdog:
 mode: required
 device: /dev/watchdog
 safety_margin: 5

tags:
 nofailover: false
 noloadbalance: false
 clonefrom: false
 nosync: false

Configuration patroni – node-esql03

[root@node-psql03 ~]# vi /etc/patroni/patroni.yml

scope: psql_cluster
namespace: /service/
name: node-psql03

restapi:
 listen: 0.0.0.0:8008
 connect_address: 192.168.1.105:8008

etcd:
 hosts:
 - 192.168.1.100:2379
 - 192.168.1.101:2379
 - 192.168.1.102:2379

bootstrap:
 dcs:
 ttl: 30
 loop_wait: 10
 retry_timeout: 10
 maximum_lag_on_failover: 1048576

 postgresql:
 use_pg_rewind: true
 use_slots: true

 parameters:
 initdb:
 - encoding: UTF8
 - data-checksums

 pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
 - host replication replicator 127.0.0.1/32 md5
 - host replication replicator 192.168.1.103/0 md5
 - host replication replicator 192.168.1.104/0 md5
 - host replication replicator 192.168.1.105/0 md5
 - host all all 0.0.0.0/0 md5
 - host all all 192.168.1.0/24 md5

 users:
 admin:
 password: admin
 options:
 - createrole
 - createdb

postgresql:
 listen: 0.0.0.0:5432
 connect_address: 192.168.1.105:5432
 data_dir: /var/lib/pgsql/12/data
 bin_dir: /usr/pgsql-12/bin
 pgpass: /tmp/pgpass

 authentication:
 replication:
 username: replicator
 password: replicator2023
 superuser:
username: postgres
 password: chris@2023

watchdog:
 mode: required
 device: /dev/watchdog
 safety_margin: 5

tags:
 nofailover: false
 noloadbalance: false
 clonefrom: false
 nosync: false

8. Activation Watchdogs pour postgres (3 nodes)

[root@node-psql0x ~]# vi /etc/watchdog.conf

Dé-commenter la ligne « watchdog-device »

[root@node-psql0x ~]# mknod /dev/watchdog c 10 130
[root@node-psql0x ~]# modprobe softdog
[root@node-psql0x ~]# chown postgres /dev/watchdog
[root@node-psql0x ~]# ls -al /dev/watchdog

9. Règles firewall (3 nodes)

[root@node-psql0x ~]# firewall-cmd --zone=public --add-port=8008/tcp --permanent
[root@node-psql0x ~]# firewall-cmd --zone=public --add-port=5432/tcp --permanent
[root@node-psql0x ~]# firewall-cmd --remove-service={cockpit,dhcpv6-client} –permanent
[root@node-psql0x ~]# firewall-cmd --reload
[root@node-psql0x ~]# firewall-cmd --list-all

10. Lancement de Patroni (3 nodes)

Node-psql01

[root@node-psql01 ~]# systemctl start patroni
[root@node-psql01 ~]# systemctl enable patroni
[root@node-psql01 ~]# systemctl status patroni

Node-psql02

[root@node-psql02 ~]# systemctl start patroni
[root@node-psql02 ~]# systemctl enable patroni
[root@node-psql02 ~]# systemctl status patroni

Node-psql03

[root@node-psql03 ~]# systemctl start patroni
[root@node-psql03 ~]# systemctl enable patroni
[root@node-psql03 ~]# systemctl status patroni

11. Check cluster Patroni

[root@node-psql01 ~]# patronictl -d etcd://192.168.1.100:2379 list psql_cluster

[root@node-psql01 ~]# curl -s http://node-psql01:8008

[root@node-psql01 ~]# curl -s http://node-psql02:8008

[root@node-psql01 ~]# curl -s http://node-psql03:8008

12. Check cluster SQL HA

Création données sur Node-psql01 (node Master)

[root@node-psql01 ~]# psql -U postgres
postgres-# \du

postgres=# SELECT schema_name FROM information_schema.schemata;

postgres=# CREATE DATABASE chris2023;
CREATE DATABASE
postgres=# \l

postgres=# CREATE ROLE chris SUPERUSER LOGIN PASSWORD 'chris2023';
CREATE ROLE
postgres=# \du

Check réplication données sur Node-psql02 et node-psql03 ( replication)

[root@node-psql0x~]# psql -U postgres
postgres=# \du

postgres=# \l

Views: 3

Étiquettes

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *