Skip to main content

Déploiement d'un cluster postgresql hautement disponible avec Patroni

· 18 min read

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​

mkdir -p /app/postgres
cd /app/postgres/

2. Créer un Dockerfile avec le contenu suivant​

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​

docker build -t postgres12-img .

4. Vérifier que l'image a bien été créée​

docker images

5. Lancer un conteneur à partir de notre image Docker​

docker run -tid --name postgres12-server postgres12-img

6. Se connecter au conteneur postgres12-server​

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

8. Installer postgreSQL 12​

apt -y install postgresql-12
ln -s /usr/lib/postgresql/12/bin/* /usr/sbin/
rm -rf /var/lib/postgresql/12/main/*

9. Installer etcd​

apt install -y etcd

10. Installer patroni​

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. Ajouter les variables d'environnement de Patroni​

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_SCOPE="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. Configurer la résolution des noms des machines​

cat >> /etc/hosts << 'EOF'
172.18.56.41    postgres1
172.18.56.42    postgres2
172.18.56.43    postgres3
EOF

14. Créer une nouvelle image Docker​

docker commit postgres12-server postgres12-img

15. Supprimer le conteneur postgres12-server​

docker rm -f postgres12-server

16. Créer un fichier 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​

docker-compose up -d

18. Vérifier que les 3 nœuds sont en cours d'exécution​

docker-compose ps

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. Configuration etcd pour postgres1​

docker exec -ti postgres1 sh -c "cat > /etc/default/etcd << 'EOF'
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\"
EOF"

20. Configuration etcd pour postgres2​

docker exec -ti postgres2 sh -c "cat > /etc/default/etcd << 'EOF'
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\"
EOF"

21. Configuration etcd pour postgres3​

docker exec -ti postgres3 sh -c "cat > /etc/default/etcd << 'EOF'
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\"
EOF"

22. Démarrer le service etcd sur les 3 serveurs​

docker exec -ti postgres1 sh -c "service etcd start"
docker exec -ti postgres2 sh -c "service etcd start"
docker exec -ti postgres3 sh -c "service etcd start"

23. Vérifier l'état du cluster​

docker exec -ti postgres1 etcdctl cluster-health

24. Lister les membres du cluster​

docker exec -ti postgres1 etcdctl member list
docker exec -it postgres1 bash -c 'source /etc/environment ; etcdctl endpoint status --write-out=table --endpoints=$ENDPOINTS'

Configuration du service patroni​

25. Configuration patroni pour postgres1​

docker exec -ti postgres1 sh -c "cat > /etc/patroni/postgres.yml << 'EOF'
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
EOF"

26. Configuration patroni pour postgres2​

docker exec -ti postgres2 sh -c "cat > /etc/patroni/postgres.yml << 'EOF'
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
EOF"

27. Configuration patroni pour postgres3​

docker exec -ti postgres3 sh -c "cat > /etc/patroni/postgres.yml << 'EOF'
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
EOF"

28. Démarrer le service patroni sur postgres1​

docker exec -ti postgres1 sh -c "service patroni start"

29. Vérifier l'état du service patroni​

docker exec -ti postgres1 sh -c "service patroni status"

30. Consulter les logs patroni pour postgres1​

docker exec -ti postgres1 sh -c "tail -f /var/log/patroni.log"

31. Démarrer patroni sur postgres2 et postgres3​

docker exec -ti postgres2 sh -c "service patroni start"
docker exec -ti postgres3 sh -c "service patroni start"

32. Consulter les logs patroni pour postgres2​

docker exec -ti postgres2 sh -c "tail -f /var/log/patroni.log"

33. Consulter les logs patroni pour postgres3​

docker exec -ti postgres3 sh -c "tail -f /var/log/patroni.log"

34. Lister les membres du cluster patroni​

docker exec -it postgres1 bash -c 'source /etc/environment ; patronictl list'

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 agit comme répartiteur de charge (load balancer).

35. Configuration pgbouncer pour postgres1​

docker exec -ti postgres1 sh -c "echo '* = host=postgres1 port=5432 dbname=postgres' >> /etc/pgbouncer/pgbouncer.ini"

36. Configuration pgbouncer pour postgres2​

docker exec -ti postgres2 sh -c "echo '* = host=postgres2 port=5432 dbname=postgres' >> /etc/pgbouncer/pgbouncer.ini"

37. Configuration pgbouncer pour postgres3​

docker exec -ti postgres3 sh -c "echo '* = host=postgres3 port=5432 dbname=postgres' >> /etc/pgbouncer/pgbouncer.ini"

38. Modifier listen_addr sur les 3 serveurs​

# Sur les 3 serveurs, modifier listen_addr = localhost par listen_addr = *
docker exec -ti postgres1 sh -c "sed -i 's/#listen_addr = localhost/listen_addr = */' /etc/pgbouncer/pgbouncer.ini"
docker exec -ti postgres2 sh -c "sed -i 's/#listen_addr = localhost/listen_addr = */' /etc/pgbouncer/pgbouncer.ini"
docker exec -ti postgres3 sh -c "sed -i 's/#listen_addr = localhost/listen_addr = */' /etc/pgbouncer/pgbouncer.ini"

39. Ajouter les paramètres d'authentification sur les 3 serveurs​

docker exec -ti postgres1 sh -c "sed -i '/auth_file = \/etc\/pgbouncer\/userlist.txt/a auth_user = pgbouncer\nauth_query = SELECT p_user, p_password FROM public.lookup(\$1)' /etc/pgbouncer/pgbouncer.ini"
docker exec -ti postgres2 sh -c "sed -i '/auth_file = \/etc\/pgbouncer\/userlist.txt/a auth_user = pgbouncer\nauth_query = SELECT p_user, p_password FROM public.lookup(\$1)' /etc/pgbouncer/pgbouncer.ini"
docker exec -ti postgres3 sh -c "sed -i '/auth_file = \/etc\/pgbouncer\/userlist.txt/a auth_user = pgbouncer\nauth_query = SELECT p_user, p_password FROM public.lookup(\$1)' /etc/pgbouncer/pgbouncer.ini"

40. Se connecter au serveur PostgreSQL primaire (postgres1)​

docker exec -ti postgres1 bash -c "psql -h postgres1 -p 5432 -U postgres"

41. Créer le rôle pgbouncer​

CREATE ROLE pgbouncer with LOGIN encrypted password 'pgbouncer';

42. Créer la fonction lookup​

CREATE FUNCTION public.lookup (
INOUT p_user     name,
OUT   p_password text
) RETURNS record
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;

43. Récupérer le mot de passe crypté du rôle pgbouncer​

select * from pg_shadow;

44. Configurer userlist.txt​

docker exec -ti postgres1 bash -c "echo '\"pgbouncer\" \"md5be5544d3807b54dd0637f2439ecb03b9\"' > /etc/pgbouncer/userlist.txt"
docker exec -ti postgres2 bash -c "echo '\"pgbouncer\" \"md5be5544d3807b54dd0637f2439ecb03b9\"' > /etc/pgbouncer/userlist.txt"
docker exec -ti postgres3 bash -c "echo '\"pgbouncer\" \"md5be5544d3807b54dd0637f2439ecb03b9\"' > /etc/pgbouncer/userlist.txt"

45. Démarrer pgbouncer sur les 3 serveurs​

docker exec -it postgres1 bash -c "service pgbouncer start"
docker exec -it postgres2 bash -c "service pgbouncer start"
docker exec -it postgres3 bash -c "service pgbouncer start"

46. Tester l'authentification avec pgbouncer​

docker exec -ti postgres1 bash -c "psql -h postgres1 -p 6432 -U postgres"

Installation HAProxy​

47. Ajouter haproxy dans docker-compose.yml​

  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​

docker-compose up -d haproxy1

49. Installer HAProxy​

docker exec -ti haproxy1 bash -c "apt update && apt install -y haproxy vim"

50. Configuration HAProxy​

docker exec -ti haproxy1 sh -c "cat > /etc/haproxy/haproxy.cfg << 'EOF'
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
EOF"

51. Démarrer HAProxy​

docker exec -ti haproxy1 sh -c "service haproxy start"

52. Vérifier le statut de HAProxy​

docker exec -ti haproxy1 sh -c "service haproxy status"

53. Interface web HAProxy​

Vous pouvez vérifier que le HAProxy est bien configuré en accédant à l'URL http://172.18.56.50:7000/

Installation du client PostgreSQL​

54. Installer le client PostgreSQL sur l'hôte​

apt install -y postgresql-client python3-psycopg2

55. Configurer les mots de passe​

echo "localhost:5000:*:postgres:postgres" >> ~/.pgpass
echo "localhost:5001:*:postgres:postgres" >> ~/.pgpass
chmod 0600 ~/.pgpass

56. Tester les requêtes de lecture​

psql -h localhost -p 5001 -U postgres -t -c "select inet_server_addr()"

57. Tester les requêtes d'écriture​

psql -h localhost -p 5000 -U postgres -t -c "select inet_server_addr()"

Test PostgreSQL High Availability (HA) Cluster Replication​

58. Créer les répertoires de données​

docker exec -it postgres1 bash -c "mkdir -p /opt/data && chown -R postgres:postgres /opt/data"
docker exec -it postgres2 bash -c "mkdir -p /opt/data && chown -R postgres:postgres /opt/data"
docker exec -it postgres3 bash -c "mkdir -p /opt/data && chown -R postgres:postgres /opt/data"

59. Créer le tablespace​

psql -h localhost -p 5000 -U postgres -c "CREATE TABLESPACE tbs_data OWNER postgres LOCATION '/opt/data'"

60. Créer la base de données​

psql -h localhost -p 5000 -U postgres -c "create database dataresilience TABLESPACE tbs_data"

61. Créer le schéma test​

psql -h localhost -p 5000 -U postgres -d dataresilience -c "create schema test"

62. Créer la table log​

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))"

63. Créer un utilisateur​

psql -h localhost -p 5000 -U postgres -d dataresilience -c "create user rachid login password 'rachid'"
psql -h localhost -p 5000 -U postgres -d dataresilience -c "grant usage on schema test to rachid;"
psql -h localhost -p 5000 -U postgres -d dataresilience -c "grant insert, update, delete, select on test.log to rachid;"

64. Ajouter le compte dans .pgpass​

echo "localhost:5000:*:rachid:rachid" >> ~/.pgpass
echo "localhost:5001:*:rachid:rachid" >> ~/.pgpass

65. Premier test - tous les serveurs up​

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')"
psql -h localhost -p 5001 -U rachid -d dataresilience -c "select date, host_addr as insert_from_host, message, inet_server_addr() as select_from_host from test.log, inet_server_addr();"

66. Deuxième test​

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')"
psql -h localhost -p 5001 -U rachid -d dataresilience -c "select date, host_addr as insert_from_host, message, inet_server_addr() as select_from_host from test.log, inet_server_addr();"

67. Test de failover - arrêt du nœud primaire​

docker exec -ti postgres1 sh -c "service etcd stop"
docker exec -ti postgres1 sh -c "service etcd status"
docker exec -it postgres2 bash -c 'source /etc/environment ; patronictl list'

68. Test après failover​

psql -h localhost -p 5000 -U rachid -d dataresilience -c "INSERT INTO test.log (date, host_addr, message) VALUES (current_date, inet_server_addr(), 'test après failover')"
psql -h localhost -p 5001 -U rachid -d dataresilience -c "select date, host_addr as insert_from_host, message, inet_server_addr() as select_from_host from test.log, inet_server_addr();"

Surveillance et maintenance​

Commandes utiles pour le monitoring​

État du cluster Patroni​

docker exec -it postgres1 bash -c 'source /etc/environment ; patronictl list'

État d'etcd​

docker exec -it postgres1 bash -c 'source /etc/environment ; etcdctl endpoint status --write-out=table --endpoints=$ENDPOINTS'

Logs des services​

# Logs Patroni
docker exec -ti postgres1 tail -f /var/log/patroni.log

# Logs PostgreSQL
docker exec -ti postgres1 tail -f /var/log/postgresql/postgresql-12-main.log

Interface HAProxy​

Accédez à http://localhost:7000/ pour monitorer l'état des serveurs backend.

Opérations de maintenance​

Basculement manuel​

# Basculer vers un nœud spécifique
docker exec -it postgres1 bash -c 'source /etc/environment ; patronictl switchover pg_cluster --master postgres1 --candidate postgres2'

Redémarrage d'un nœud​

# Redémarrer un service sur un nœud
docker exec -ti postgres2 sh -c "service patroni restart"

Vérification de la réplication​

# Vérifier le lag de réplication
docker exec -it postgres1 bash -c 'source /etc/environment ; patronictl list'

Gestion des services​

# Arrêter tous les services
docker-compose down

# Redémarrer un conteneur spécifique
docker-compose restart postgres1

# Voir les logs d'un conteneur
docker-compose logs -f postgres1

Résolution de problèmes​

Problèmes courants​

Le cluster ne démarre pas​

  1. Vérifier que etcd est démarré sur tous les nœuds
  2. Vérifier la connectivité réseau entre les nœuds
  3. Consulter les logs de patroni
docker exec -ti postgres1 tail -100 /var/log/patroni.log

Problème de réplication​

# Vérifier le statut de la réplication
docker exec -ti postgres1 psql -U postgres -c "SELECT * FROM pg_stat_replication;"

Problème avec pgbouncer​

# Vérifier la configuration pgbouncer
docker exec -ti postgres1 cat /etc/pgbouncer/pgbouncer.ini

# Redémarrer pgbouncer
docker exec -ti postgres1 service pgbouncer restart

Problème avec HAProxy​

# Vérifier la configuration HAProxy
docker exec -ti haproxy1 cat /etc/haproxy/haproxy.cfg

# Tester la connectivité vers les backends
docker exec -ti haproxy1 curl -I http://172.18.56.41:8008/master

Sauvegarde et restauration​

Sauvegarde avec pg_dump​

# Sauvegarde complète de la base dataresilience
docker exec -ti postgres1 pg_dump -h postgres1 -U postgres -d dataresilience > backup_dataresilience.sql

Sauvegarde continue avec WAL-E (optionnel)​

Pour une solution de sauvegarde plus robuste, vous pouvez intégrer WAL-E ou pgBackRest.

Optimisations et bonnes pratiques​

Configuration PostgreSQL​

Paramètres recommandés pour la réplication​

-- Augmenter le nombre de connexions WAL sender
ALTER SYSTEM SET max_wal_senders = 10;

-- Configurer les archives WAL
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'cp %p /var/lib/postgresql/archives/%f';

-- Optimiser la réplication synchrone si nécessaire
ALTER SYSTEM SET synchronous_standby_names = 'postgres2,postgres3';

Monitoring des performances​

-- Vérifier les statistiques de réplication
SELECT * FROM pg_stat_replication;

-- Monitorer les conflits de réplication
SELECT * FROM pg_stat_database_conflicts;

Configuration Patroni avancée​

Configuration des callbacks​

Vous pouvez ajouter des scripts de callback dans la configuration Patroni :

postgresql:
  callbacks:
    on_start: /scripts/on_start.sh
    on_stop: /scripts/on_stop.sh
    on_role_change: /scripts/on_role_change.sh

Configuration des tags avancés​

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
    priority: 100  # Priorité pour l'élection du leader

Sécurité​

Chiffrement des communications​

Pour un environnement de production, activez SSL :

postgresql:
  parameters:
    ssl: 'on'
    ssl_cert_file: '/etc/ssl/certs/server.crt'
    ssl_key_file: '/etc/ssl/private/server.key'

Authentification renforcée​

postgresql:
  pg_hba:
  - hostssl replication replicator 0.0.0.0/0 md5
  - hostssl all all 0.0.0.0/0 md5

Conclusion​

Ce guide vous a permis de déployer un cluster PostgreSQL hautement disponible avec les composants suivants :

  • 3 nœuds PostgreSQL avec réplication automatique
  • Patroni pour la gestion automatique du failover
  • etcd pour la coordination du cluster
  • pgbouncer pour l'optimisation des connexions
  • HAProxy pour la répartition de charge et la haute disponibilité

Avantages de cette architecture​

  1. Haute disponibilité : Failover automatique en cas de panne
  2. Répartition de charge : Les lectures sont distribuées sur les répliques
  3. Monitoring intégré : Interface web HAProxy et outils de monitoring
  4. Scalabilité : Possibilité d'ajouter facilement de nouveaux nœuds
  5. Maintenance sans interruption : Basculement manuel pour les mises à jour

Points de vigilance​

  • Réseau : Assurez-vous d'une connectivité stable entre les nœuds
  • Stockage : Utilisez un stockage performant et fiable
  • Monitoring : Surveillez régulièrement les métriques de performance
  • Sauvegardes : Implémentez une stratégie de sauvegarde robuste
  • Sécurité : Chiffrez les communications et sécurisez les accès

Cette architecture est prête pour un environnement de production et peut être adaptée selon vos besoins spécifiques en termes de performance, sécurité et disponibilité.

Ressources supplémentaires​

Cet article fait partie d'une série sur la gestion des bases de données haute disponibilité. N'hésitez pas à nous contacter pour des conseils personnalisés sur votre architecture.