Introduction
Patroni est un outil open source qui permet de créer et gérer un cluster PostgreSQL hautement disponible. Il peut être utilisé pour gérer des tâches telles que la réplication, les sauvegardes et les restaurations. En utilisant une configuration HAProxy, Patroni offre un point d'accès unique pour se connecter au serveur primaire du cluster, indépendamment de l'emplacement de la base de données primaire.
Si vous avez besoin de déployer rapidement un cluster PostgreSQL hautement disponible dans votre data centre, n'hésitez pas à nous contacter pour obtenir de l'aide.
Pré-requis
Il est recommandé d'utiliser une machine virtuelle équipée d'Ubuntu avec au moins 8 GB de mémoire RAM, avec Docker et Docker Compose préalablement installés.
Dans cet article, nous allons configurer un cluster de haute disponibilité pour PostgreSQL en utilisant les binaires de PostgreSQL 12, PATRONI, HAProxy et un client PostgreSQL.
Pour docker, nous allons utiliser le sous-réseau 172.18.56.0/24. les adresses IP suivantes seront utilisées:
- postgres1: 172.18.56.41 => Services: PostgreSQL, patroni, etcd, pgbouncer
- postgres2: 172.18.56.42 => Services: PostgreSQL, patroni, etcd, pgbouncer
- postgres3: 172.18.56.43 => Services: PostgreSQL, patroni, etcd, pgbouncer
- haproxy1: 172.18.56.50 => Services: HAProxy
Création et configuration des conteneurs docker
1. Créer un répertoire de travail /app/postgres.
root@data-resilience:/app# mkdir -p /app/postgres root@data-resilience:/app# cd /app/postgres/
2. Créer un Dockerfile avec le contenu suivant:
root@data-resilience:/app/postgres# cat Dockerfile FROM ubuntu:20.04 LABEL maintainer="data-resilience" LABEL version="1" LABEL description="Image docker pour postgresql 12" ARG DEBIAN_FRONTEND=noninteractive RUN apt update RUN apt install -y vim wget ca-certificates curl gnupg lsb-release dnsutils systemd systemctl RUN apt clean
3. Construire l'image docker postgres12-img avec la commande suivante:
docker build -t postgres12-img .
4. Vérifier éventuellement que l'image a bien été créée.
root@data-resilience:/app/postgres# docker images REPOSITORY TAG IMAGE ID CREATED SIZE postgres12-img latest a3117b8d00fb 34 seconds ago 169MB <--- elle est bien là! couchbase/server enterprise-7.0.5 1a64a8235967 4 weeks ago 1.33GB ubuntu 20.04 d5447fc01ae6 5 weeks ago 72.8MB
5. Lancer un conteneur à partir de notre image Docker.
root@data-resilience:/app/postgres# docker run -tid --name postgres12-server postgres12-img fcc80f0d88e7f5000f0e6348acd8413105043f243716a8234e41ca0573379b32 root@data-resilience:/app/postgres#
6. Se connecter au conteneur postgres12-server avec la commande suivante:
docker exec -ti postgres12-server sh
7. Ajouter le dépôt de PostgreSQL.
# curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg # sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list' # apt update Hit:1 http://archive.ubuntu.com/ubuntu focal InRelease Get:2 http://archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB] Get:3 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB] Get:4 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [91.6 kB] Get:5 http://archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB] Get:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [419 kB] Fetched 846 kB in 1s (1152 kB/s) Reading package lists... Done Building dependency tree Reading state information... Done All packages are up to date.
8. Installer postgreSQL 12 en exécutant les commandes suivantes:
apt -y install postgresql-12 ln -s /usr/lib/postgresql/12/bin/* /usr/sbin/ rm -rf /var/lib/postgresql/12/main/*
9. Installer etcd avec la commande suivante:
apt install -y etcd
10. Installer patroni en exécutant les commandes suivantes:
apt -y install python3 python3-pip python3-dev libpq-dev python3-etcd ln -s /usr/bin/python3 /usr/bin/python pip3 install launchpadlib --upgrade setuptools psycopg2 apt install -y patroni
11. Installer pgbouncer.
apt install -y pgbouncer
12. Depuis le conteneur Docker, utiliser la commande echo pour ajouter les variables d'environnement de Patroni dans le fichier /etc/environment.
# echo 'PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin" export PGDATA="/var/lib/postgresql/12/main" export ETCDCTL_API="3" export PATRONI_ETCD_URL="http://127.0.0.1:2379" export PATRONI_SCOP> > > > E="pg_cluster" postgres1=172.18.56.41 postgres2=172.18.56.42 postgres3=172.18.56.43 ENDPOINTS=$postgres1:2379,$postgres2:2379,$postgres3:2379 ' > /etc/environment
13. Nous allons utiliser /etc/hosts pour la résolution des noms des machines. Ajouter les lignes suivantes dans /etc/hosts:
172.18.56.41 postgres1 172.18.56.42 postgres2 172.18.56.43 postgres3
14. Créez une nouvelle image Docker nommée postgres12-img à partir du conteneur postgres12-server.
root@data-resilience:/app/postgres# docker commit postgres12-server postgres12-img sha256:4039441833b32be1f9b7b6dd4a889066ecbcf7bf6508ebbdedb5c193dfbec29b root@data-resilience:/app/postgres#
15. Supprimer le conteneur postgres12-server car il n'est plus nécessaire pour la suite de l'article.
root@data-resilience:/app/postgres# docker rm -f postgres12-server postgres12-server root@data-resilience:/app/postgres#
16. Créer un fichier docker-compose.yml avec le contenu suivant:
root@data-resilience:/app/postgres# cat docker-compose.yml
version: '3.5'
services:
postgres1:
tty: true
hostname: postgres1
container_name: postgres1
image: postgres12-img
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
volumes:
- type: volume
source: pg_data1
target: /var/lib/postgresql/data
networks:
dbNetwork:
ipv4_address: 172.18.56.41
ports:
- '5432:5432'
postgres2:
tty: true
hostname: postgres2
container_name: postgres2
image: postgres12-img
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
volumes:
- type: volume
source: pg_data2
target: /var/lib/postgresql/data
networks:
dbNetwork:
ipv4_address: 172.18.56.42
ports:
- '5433:5432'
postgres3:
tty: true
hostname: postgres3
container_name: postgres3
image: postgres12-img
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
volumes:
- type: volume
source: pg_data3
target: /var/lib/postgresql/data
networks:
dbNetwork:
ipv4_address: 172.18.56.43
ports:
- '5434:5432'
networks:
dbNetwork:
name: dbNetwork
driver: bridge
ipam:
config:
- subnet: 172.18.56.0/24
volumes:
pg_data1:
pg_data2:
pg_data3:
17. Lancer docker-compose avec la commande suivante:
root@data-resilience:/app/postgres# docker-compose up -d Creating volume "postgres_pg_data1" with default driver Creating volume "postgres_pg_data2" with default driver Creating volume "postgres_pg_data3" with default driver Creating postgres2 ... done Creating postgres3 ... done Creating postgres1 ... done
18. Vérifier que les 3 nœuds sont en cours d'exécution.
root@data-resilience:/app/postgres# docker-compose ps Name Command State Ports ---------------------------------------------------------------------- postgres1 bash Up 0.0.0.0:5432->5432/tcp,:::5432->5432/tcp postgres2 bash Up 0.0.0.0:5433->5432/tcp,:::5433->5432/tcp postgres3 bash Up 0.0.0.0:5434->5432/tcp,:::5434->5432/tcp
Configuration du service etcd
Etcd est un magasin de clés-valeurs distribué qui fournit un moyen fiable de stocker les données qui doivent être accessibles par un système distribué ou un groupe de machines. Dans notre cas, il va servir à stocker les données de configuration du cluster patroni. La configuration d'etcd est gérée dans le fichier /etc/default/etcd.
19. Se connecter au serveur postgres1, puis lancer la commande echo pour ajouter les varables de configuration etcd dans le fichier /etc/default/etcd.
root@data-resilience:/app/postgres# docker exec -ti postgres1 sh #echo 'ETCD_NAME=postgres1 ETCD_DATA_DIR="/var/lib/etcd/postgres1" ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.18.56.41:2380" ETCD_INITIAL_CLUSTER="postgres1=http://172.18.56.41:2380,postgres2=http://172.18.56.42:2380,postgres3=http://172.18.56.43:2380" ETCD_INITIAL_CLUSTER_STATE="new" ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster" ETCD_ADVERTISE_CLIENT_URLS="http://0.0.0.0:2379" ETCD_ENABLE_V2="true" ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"' >> /etc/default/etcd
20. Se connecter au serveur postgres2, puis lancer la commande echo pour ajouter les varables de configuration etcd dans le fichier /etc/default/etcd.
root@data-resilience:/app/postgres# docker exec -ti postgres2 sh #echo 'ETCD_NAME=postgres2 ETCD_DATA_DIR="/var/lib/etcd/postgres2" ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.18.56.42:2380" ETCD_INITIAL_CLUSTER="postgres1=http://172.18.56.41:2380,postgres2=http://172.18.56.42:2380,postgres3=http://172.18.56.43:2380" ETCD_INITIAL_CLUSTER_STATE="new" ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster" ETCD_ADVERTISE_CLIENT_URLS="http://0.0.0.0:2379" ETCD_ENABLE_V2="true" ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"' >> /etc/default/etcd
21. Se connecter au serveur postgres3, puis lancer la commande echo pour ajouter les varables de configuration etcd dans le fichier /etc/default/etcd.
root@data-resilience:/app/postgres# docker exec -ti postgres3 sh #echo 'ETCD_NAME=postgres3 ETCD_DATA_DIR="/var/lib/etcd/postgres3" ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.18.56.43:2380" ETCD_INITIAL_CLUSTER="postgres1=http://172.18.56.41:2380,postgres2=http://172.18.56.42:2380,postgres3=http://172.18.56.43:2380" ETCD_INITIAL_CLUSTER_STATE="new" ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster" ETCD_ADVERTISE_CLIENT_URLS="http://0.0.0.0:2379" ETCD_ENABLE_V2="true" ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"' >> /etc/default/etcd
22. Démarrer le service etcd sur les 3 serveurs.
root@data-resilience:/app/postgres# docker exec -ti postgres1 sh -c "service etcd start" * Starting etcd... [ OK ] root@data-resilience:/app/postgres# docker exec -ti postgres2 sh -c "service etcd start" * Starting etcd... [ OK ] root@data-resilience:/app/postgres# docker exec -ti postgres3 sh -c "service etcd start" * Starting etcd... [ OK ]
23. Vérifier l'état du cluster.
root@data-resilience:/app/postgres# docker exec -ti postgres1 etcdctl cluster-health member 1d9b124e367f093a is healthy: got healthy result from http://0.0.0.0:2379 member dccc2d987992f32f is healthy: got healthy result from http://0.0.0.0:2379 member e08d29b27edb2e19 is healthy: got healthy result from http://0.0.0.0:2379 cluster is healthy [ OK ]
24. List les membres du cluster.
root@data-resilience:/app/postgres# docker exec -ti postgres1 etcdctl member list 1d9b124e367f093a: name=postgres3 peerURLs=http://172.18.56.43:2380 clientURLs=http://0.0.0.0:2379 isLeader=false dccc2d987992f32f: name=postgres1 peerURLs=http://172.18.56.41:2380 clientURLs=http://0.0.0.0:2379 isLeader=false e08d29b27edb2e19: name=postgres2 peerURLs=http://172.18.56.42:2380 clientURLs=http://0.0.0.0:2379 isLeader=true root@data-resilience:/app/postgres# docker exec -it postgres1 bash -c 'source /etc/environment ; etcdctl endpoint status --write-out=table --endpoints=$ENDPOINTS' +-------------------+------------------+---------+---------+-----------+-----------+------------+ | ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | RAFT TERM | RAFT INDEX | +-------------------+------------------+---------+---------+-----------+-----------+------------+ | 172.18.56.41:2379 | dccc2d987992f32f | 3.2.26 | 25 kB | false | 4497 | 34 | | 172.18.56.42:2379 | e08d29b27edb2e19 | 3.2.26 | 25 kB | true | 4497 | 34 | | 172.18.56.43:2379 | 1d9b124e367f093a | 3.2.26 | 25 kB | false | 4497 | 34 | +-------------------+------------------+---------+---------+-----------+-----------+------------+ [ OK ]
Configuration du service patroni
25. Ajouter les lignes suivantes dans le fichier /etc/patroni/postgres.yml du serveur postgres1:
scope: pg_cluster
namespace: /service/
name: postgres1
restapi:
listen: postgres1:8008
connect_address: postgres1:8008
etcd:
hosts: postgres1:2379,postgres2:2379,postgres3: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:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: postgres1:5432
connect_address: postgres1:5432
proxy_address: postgres1:6432
data_dir: /var/lib/postgresql/12/main
bin_dir: /usr/lib/postgresql/12/bin
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: replicator
superuser:
username: postgres
password: postgres
rewind:
username: rewind
password: rewind
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
26. Ajouter les lignes suivantes dans le fichier /etc/patroni/postgres.yml du serveur postgres2
scope: pg_cluster
namespace: /service/
name: postgres2
restapi:
listen: postgres2:8008
connect_address: postgres2:8008
etcd:
hosts: postgres1:2379,postgres2:2379,postgres3: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:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: postgres2:5432
connect_address: postgres2:5432
proxy_address: postgres2:6432
data_dir: /var/lib/postgresql/12/main
bin_dir: /usr/lib/postgresql/12/bin
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: replicator
superuser:
username: postgres
password: postgres
rewind:
username: rewind
password: rewind
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
27. Ajouter les lignes suivantes dans le fichier /etc/patroni/postgres.yml du serveur postgres3
scope: pg_cluster
namespace: /service/
name: postgres3
restapi:
listen: postgres3:8008
connect_address: postgres3:8008
etcd:
hosts: postgres1:2379,postgres2:2379,postgres3: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:
- host replication replicator 127.0.0.1/32 md5
- host replication replicator 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: postgres3:5432
connect_address: postgres3:5432
proxy_address: postgres3:6432
data_dir: /var/lib/postgresql/12/main
bin_dir: /usr/lib/postgresql/12/bin
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: replicator
superuser:
username: postgres
password: postgres
rewind:
username: rewind
password: rewind
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
28. Démarrer le service patroni sur le serveur postgres1 en premier, puis sur les autres nœuds.
root@data-resilience:/app/postgres# docker exec -ti postgres1 sh -c "service patroni start" * Starting Patroni
29. Vérifier l'état du service patroni.
root@data-resilience:/app/postgres# docker exec -ti postgres1 sh -c "service patroni status" * Patroni is running
30. Consulter le fichier de log /var/log/patroni.log pour vérifier qu'il n'y a pas d'erreurs et que le serveur postgres1 est primaire.
root@data-resilience:/app/postgres# docker exec -ti postgres1 sh -c "tail -f /var/log/patroni.log" 2023-01-18 22:13:33,042 INFO: no action. I am (postgres1), the leader with the lock 2023-01-18 22:13:43,045 INFO: no action. I am (postgres1), the leader with the lock 2023-01-18 22:13:53,056 INFO: no action. I am (postgres1), the leader with the lock 2023-01-18 22:14:03,040 INFO: no action. I am (postgres1), the leader with the lock 2023-01-18 22:14:13,060 INFO: no action. I am (postgres1), the leader with the lock
31. Démarrer le service patroni sur les autres serveurs postgres2 et postgres3.
root@data-resilience:/app/postgres# docker exec -ti postgres2 sh -c "service patroni start" * Creating logfile for Patroni... * Starting Patroni root@data-resilience:/app/postgres# docker exec -ti postgres3 sh -c "service patroni start" * Creating logfile for Patroni... * Starting Patroni
32. Consulter le fichier de log /var/log/patroni.log sur postgres2 pour vérifier qu'il n'y a pas d'erreurs et que le serveur est secondaire.
root@data-resilience:/app/postgres# docker exec -ti postgres2 sh -c "tail -f /var/log/patroni.log" 2023-01-18 22:16:43,054 INFO: no action. I am (postgres2), a secondary, and following a leader (postgres1) 2023-01-18 22:16:47.346 CET [107] LOG: started streaming WAL from primary at 0/3000000 on timeline 1 2023-01-18 22:16:53,062 INFO: no action. I am (postgres2), a secondary, and following a leader (postgres1) 2023-01-18 22:17:03,094 INFO: no action. I am (postgres2), a secondary, and following a leader (postgres1) 2023-01-18 22:17:13,154 INFO: no action. I am (postgres2), a secondary, and following a leader (postgres1)
33. Consulter le fichier de log /var/log/patroni.log sur postgres3 pour vérifier qu'il n'y a pas d'erreurs et que le serveur est secondaire.
root@data-resilience:/app/postgres# docker exec -ti postgres3 sh -c "tail -f /var/log/patroni.log" 2023-01-18 22:17:18,334 INFO: establishing a new patroni connection to the postgres cluster 2023-01-18 22:17:18,352 INFO: no action. I am (postgres3), a secondary, and following a leader (postgres1) 2023-01-18 22:17:23,078 INFO: no action. I am (postgres3), a secondary, and following a leader (postgres1) 2023-01-18 22:17:33,055 INFO: no action. I am (postgres3), a secondary, and following a leader (postgres1)
34. Exécuter la commande suivante pour lister les membres du cluster patroni:
root@data-resilience:/app/postgres# docker exec -it postgres1 bash -c 'source /etc/environment ; patronictl list' + Cluster: pg_cluster (7190103337723752656) +----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+---------+----+-----------+ | postgres1 | postgres1 | Leader | running | 1 | | | postgres2 | postgres2 | Replica | running | 1 | 0 | | postgres3 | postgres3 | Replica | running | 1 | 0 | +-----------+-----------+---------+---------+----+-----------+
Configuration pgbouncer
Pgbouncer est un logiciel open-source qui agit comme un proxy de base de données pour PostgreSQL. Il permet de réduire le nombre de connexions physiques à la base de données en utilisant un pool de connexions. Cela permet d'améliorer les performances et de réduire la charge sur la base de données en utilisant moins de ressources système. Il est également utilisé pour la sécurité et aussi il acte comme répartiteur de charge (load balancer).
35. Editer le fichier /etc/pgbouncer/pgbouncer.ini du serveur postgres1 puis ajouter la ligne suivante à la fin de la section [databases]:
* = host=postgres1 port=5432 dbname=postgres
36. Editer le fichier /etc/pgbouncer/pgbouncer.ini du serveur postgres2 puis ajouter la ligne suivante à la fin de la section [databases]:
* = host=postgres2 port=5432 dbname=postgres
37. Editer le fichier /etc/pgbouncer/pgbouncer.ini du serveur postgres3 puis ajouter la ligne suivante à la fin de la section [databases]:
* = host=postgres3 port=5432 dbname=postgres
38. Sur les 3 serveurs, éditer le fichier /etc/pgbouncer/pgbouncer.ini puis modifier la ligne listen_addr = localhost par listen_addr = *.
#listen_addr = localhost listen_addr = *
39. Sur les 3 serveurs, éditer le fichier /etc/pgbouncer/pgbouncer.ini puis ajouter les lignes suivantes dans section [pgbouncer] après "auth_file = /etc/pgbouncer/userlist.txt".
auth_user = pgbouncer auth_query = SELECT p_user, p_password FROM public.lookup($1)
40. Se connecter au serveur postgreSQL primaire (postgres1).
root@data-resilience:/app/postgres# docker exec -ti postgres1 bash -c "psql -h postgres1 -p 5432 -U postgres" Password for user postgres: psql (12.13 (Ubuntu 12.13-1.pgdg20.04+1)) Type "help" for help. postgres=#
41. Créer le rôle pgbouncer en exécutant la commande suivante:
postgres=# CREATE ROLE pgbouncer with LOGIN encrypted password 'pgbouncer'; CREATE ROLE
42. Créer la fonction lookup pour récupérer le mot de passe du rôle pgbouncer depuis la base de données.
postgres=# CREATE FUNCTION public.lookup ( postgres(# INOUT p_user name, postgres(# OUT p_password text postgres(# ) RETURNS record postgres-# LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS postgres-# $$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$; CREATE FUNCTION
43. Récupérer le mot de passe crypté du rôle pgbouncer avec la requête suivante:
postgres=# select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
------------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | |
replicator | 16384 | f | f | t | f | md55577127b7ffb431f05a1dcd318438d11 | |
rewind | 16385 | f | f | f | f | md5856a52b2f53683332cc7ec84489ac63f | |
admin | 16386 | t | f | f | f | md5f6fdffe48c908deb0f4c3bd36c032e72 | |
pgbouncer | 16387 | f | f | f | f | md5be5544d3807b54dd0637f2439ecb03b9 | |
(5 rows)
44. Ajouter le mot de passe crypté dans le fichier /etc/pgbouncer/userlist.txt.
root@data-resilience:/app/postgres# docker exec -ti postgres1 bash -c "cat /etc/pgbouncer/userlist.txt" "pgbouncer" "md5be5544d3807b54dd0637f2439ecb03b9"
45. Démarrer le service pgbouncer sur les 3 serveurs.
root@data-resilience:/app/postgres# docker exec -it postgres1 bash -c "service pgbouncer start" * Starting PgBouncer pgbouncer root@data-resilience:/app/postgres# docker exec -it postgres2 bash -c "service pgbouncer start" * Starting PgBouncer pgbouncer root@data-resilience:/app/postgres# docker exec -it postgres3 bash -c "service pgbouncer start" * Starting PgBouncer pgbouncer
46. Tester l'authentification avec pgbouncer en se connectant à la base de données sur le port 6432. En effet, pgbouncer utilise par défaut le port 6432.
root@data-resilience:/app/postgres# docker exec -ti postgres1 bash -c "psql -h postgres1 -p 6432 -U postgres" Password for user postgres: psql (12.13 (Ubuntu 12.13-1.pgdg20.04+1)) Type "help" for help. postgres=# \q
Installation HAProxy
47. Ajouter le service haproxy dans le ficher docker-compose.yml comme indiqué ci-dessous.
haproxy1:
tty: true
hostname: haproxy1
container_name: haproxy1
image: ubuntu:20.04
networks:
dbNetwork:
ipv4_address: 172.18.56.50
ports:
- '5000:5000'
- '5001:5001'
- '7000:7000'
48. Lancer le conteneur haproxy1.
root@data-resilience:/app/postgres# docker-compose up -d haproxy1 Creating haproxy1 ... done
49. Exécuter les commandes suivantes pour installer HAProxy:
apt update apt install -y haproxy apt install -y vim
50. Ajouter les lignes suivantes dans le fichier /etc/haproxy/haproxy.cfg pour configurer le HAProxy. Nous avons inclus tous les trois nœuds de base de données dans les sections primaires et de réplication: c'est parce que chaque nœud de base de données est un candidat potentiel pour être soit primaire ou réplique.
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
stats timeout 30s
user haproxy
group haproxy
maxconn 100
daemon
defaults
mode tcp
log global
option tcplog
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 100
listen stats
mode http
bind *:7000
stats enable
stats uri /
listen primary
bind *:5000
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server postgres1 172.18.56.41:6432 maxconn 100 check port 8008
server postgres2 172.18.56.42:6432 maxconn 100 check port 8008
server postgres3 172.18.56.43:6432 maxconn 100 check port 8008
listen replicas
bind *:5001
balance roundrobin
option httpchk OPTIONS /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server postgres1 172.18.56.41:6432 maxconn 100 check port 8008
server postgres2 172.18.56.42:6432 maxconn 100 check port 8008
server postgres3 172.18.56.43:6432 maxconn 100 check port 8008
51. Démarrer le service HAProxy avec la commande suivante:
root@data-resilience:/app/postgres# docker exec -ti haproxy1 sh -c "service haproxy start" * Starting haproxy haproxy
52. Vérifier le statut de HAProxy.
root@data-resilience:/app/postgres# docker exec -ti haproxy1 sh -c "service haproxy status" haproxy is running.
53. Vérifier que le HAProxy est bien configuré en se rendant sur l'URL http://172.18.56.50:7000/.
Installation du client postgreSQL
Nous allons utiliser un client postgreSQL pour effectuer nos tests sur le cluster.
54. Exécuter la commande suivante pour installer le client postgreSQL sur votre host ubuntu.
apt install -y postgresql-client python3-psycopg2
55. sauvegarder le mot de passe de postgres dans le fichier ~/.pgpass. Pour chaque mot de passe, il faut ajouter une ligne pour la connexion sur le port d'écriture 5000 et une autre ligne pour se connecter sur le port de lecture 5001 comme indiqué ci-dessous.
root@data-resilience:/app/postgres# echo "localhost:5000:*:postgres:postgres" >> ~/.pgpass root@data-resilience:/app/postgres# echo "localhost:5001:*:postgres:postgres" >> ~/.pgpass root@data-resilience:/app/postgres# chmod 0600 ~/.pgpass root@data-resilience:/app/postgres# cat ~/.pgpass localhost:5000:*:postgres:postgres localhost:5001:*:postgres:postgres
56. Tester les requêtes de lecture.
root@data-resilience:/app/postgres# psql -h localhost -p 5001 -U postgres -t -c "select inet_server_addr()" 172.18.56.42 root@data-resilience:/app/postgres# psql -h localhost -p 5001 -U postgres -t -c "select inet_server_addr()" 172.18.56.43 root@data-resilience:/app/postgres# psql -h localhost -p 5001 -U postgres -t -c "select inet_server_addr()" 172.18.56.42 root@data-resilience:/app/postgres# psql -h localhost -p 5001 -U postgres -t -c "select inet_server_addr()" 172.18.56.43 root@data-resilience:/app/postgres# psql -h localhost -p 5001 -U postgres -t -c "select inet_server_addr()" 172.18.56.42
57. Tester les requêtes d'écriture.
root@data-resilience:/app/postgres# psql -h localhost -p 5000 -U postgres -t -c "select inet_server_addr()" 172.18.56.41 root@data-resilience:/app/postgres# psql -h localhost -p 5000 -U postgres -t -c "select inet_server_addr()" 172.18.56.41 root@data-resilience:/app/postgres# psql -h localhost -p 5000 -U postgres -t -c "select inet_server_addr()" 172.18.56.41
Test PostgreSQL High Availability (HA) Cluster Replication
Nous allons créer une base de donner pour effectuer nos tests.
58. Créer le répertoire /opt/data pour les datafiles de la base de données sur les 3 serveurs.
root@data-resilience:~# docker exec -it postgres1 bash -c "mkdir -p /opt/data" root@data-resilience:~# docker exec -it postgres1 bash -c "chown -R postgres:postgres /opt/data" root@data-resilience:~# docker exec -it postgres2 bash -c "mkdir -p /opt/data" root@data-resilience:~# docker exec -it postgres2 bash -c "chown -R postgres:postgres /opt/data" root@data-resilience:~# docker exec -it postgres3 bash -c "mkdir -p /opt/data" root@data-resilience:~# docker exec -it postgres3 bash -c "chown -R postgres:postgres /opt/data"
59. Créer le tablespace tbs_data.
root@data-resilience:~# psql -h localhost -p 5000 -U postgres -c "CREATE TABLESPACE tbs_data OWNER postgres LOCATION '/opt/data'" CREATE TABLESPACE
60. Créer la base dataresilience.
root@data-resilience:~# psql -h localhost -p 5000 -U postgres -c "create database dataresilience TABLESPACE tbs_data" CREATE DATABASE
61. Créer le schémat test.
root@data-resilience:~# psql -h localhost -p 5000 -U postgres -d dataresilience -c "create schema test" CREATE SCHEMA
62. Créer la table log suivante. A noter qu'ici, on s'interesse principalement à la colonne host_addr qui sera utilisée pour indiquer sur que noeud de cluster, la ligne a été ajoutée.
root@data-resilience:~# psql -h localhost -p 5000 -U postgres -d dataresilience -c "create table test.log(date timestamp, host_addr character varying(15), message character varying(30))" CREATE TABLE
63. Créer un utilisateur avec des droits sur la base dataresilience.
root@data-resilience:~# psql -h localhost -p 5000 -U postgres -d dataresilience -c "create user rachid login password 'rachid'" CREATE ROLE root@data-resilience:~# psql -h localhost -p 5000 -U postgres -d dataresilience -c "grant usage on schema test to rachid;" GRANT root@data-resilience:~# psql -h localhost -p 5000 -U postgres -d dataresilience -c "grant insert, update, delete, select on test.log to rachid;" GRANT
64. Ajouter le compte précédement créé dans le fichier ~/.pgpass.
root@data-resilience:~# echo "localhost:5000:*:rachid:rachid" >> ~/.pgpass root@data-resilience:~# echo "localhost:5001:*:rachid:rachid" >> ~/.pgpass
65. Notre premier test consiste à insérer une ligne dans la table log puis de lancer une requête SQL pour afficher son contenu. Dans ce premier test, tous les 3 noeuds sont up.
root@data-resilience:~# psql -h localhost -p 5000 -U rachid -d dataresilience -c "INSERT INTO test.log (date, host_addr, message) VALUES (current_date, inet_server_addr(), 'tous les serveurs sont up')"
INSERT 0 1
root@data-resilience:~# psql -h localhost -p 5001 -U rachid -d dataresilience -c "select date, host_addr as insert_from_host, message, inet_server_addr select_from_host from test.log, inet_server_addr();"
date | insert_from_host | message | select_from_host
---------------------+------------------+---------------------------+------------------
2023-01-25 00:00:00 | 172.18.56.41/32 | tous les serveurs sont up | 172.18.56.43
(1 row)
Le constat est clair, la ligne insérée est envoyée au serveur principal, mais la requête de lecture récupère les données à partir du serveur de réplication, dans ce cas, le serveur Postgres3.
66. Nous allons ici insérer une nouvelle ligne puis afficher une nouvelle fois le contenu de la table log.
root@data-resilience:~# psql -h localhost -p 5000 -U rachid -d dataresilience -c "INSERT INTO test.log (date, host_addr, message) VALUES (current_date, inet_server_addr(), 'tous les serveurs sont up')"
INSERT 0 1
root@data-resilience:~# psql -h localhost -p 5001 -U rachid -d dataresilience -c "select date, host_addr as insert_from_host, message, inet_server_addr select_from_host from test.log, inet_server_addr();"
date | insert_from_host | message | select_from_host
---------------------+------------------+---------------------------+------------------
2023-01-25 00:00:00 | 172.18.56.41/32 | tous les serveurs sont up | 172.18.56.42
2023-01-25 00:00:00 | 172.18.56.41/32 | tous les serveurs sont up | 172.18.56.42
(2 rows)
Comme prévu, l'écriture est envoyée au serveur primaire postgres1. Notre requête de sélection récupère les données cette fois-ci depuis l'autre serveur de réplication postgres2. En exécutant plusieurs fois la requête de lecture, on constate que le load balancing fonctionne très bien.
67. Pour notre dernier test, nous allons simuler la perte du nœud primaire postgres1 en arrêtant simplement le service etcd.
root@data-resilience:/app/postgres# docker exec -ti postgres1 sh -c "service etcd stop" * Stopping etcd... [ OK ] root@data-resilience:/app/postgres# docker exec -ti postgres1 sh -c "service etcd status" * etcd is not running root@data-resilience:/app/postgres# docker exec -it postgres2 bash -c 'source /etc/environment ; patronictl list' + Cluster: pg_cluster (7191529948583444670) +----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+---------+----+-----------+ | postgres2 | postgres2 | Leader | running | 5 | | | postgres3 | postgres3 | Replica | running | 5 | 0 | +-----------+-----------+---------+---------+----+-----------+
Le cluster a élu un nouveau leader qui est le serveur postgres2.
68. Comme avant, on insère une nouvelle ligne puis on affiche le contenu de la table log.
root@data-resilience:/app/postgres# psql -h localhost -p 5000 -U rachid -d dataresilience -c "INSERT INTO test.log (date, host_addr, message) VALUES (current_date, inet_server_addr(), 'tous les serveurs sont up')"
INSERT 0 1
root@data-resilience:/app/postgres# psql -h localhost -p 5001 -U rachid -d dataresilience -c "select date, host_addr as insert_from_host, message, inet_server_addr select_from_host from test.log, inet_server_addr();"
date | insert_from_host | message | select_from_host
---------------------+------------------+---------------------------+------------------
2023-01-25 00:00:00 | 172.18.56.41/32 | tous les serveurs sont up | 172.18.56.43
2023-01-25 00:00:00 | 172.18.56.41/32 | tous les serveurs sont up | 172.18.56.43
2023-01-25 00:00:00 | 172.18.56.42/32 | tous les serveurs sont up | 172.18.56.43
(3 rows)
Cette fois, l'écriture est passée par le serveur postgres2 qui est devenu primaire suite à la défaillance du noeud postgres1. La haute disponibilité fonctionne parfaitement!