PostgreSQL¶

Rôle¶

Backend de base de données relationnelle à haute disponibilité de l'infrastructure Indio. Héberge notamment la base zabbix, backend du cluster de supervision (voir Zabbix). Le cluster combine réplication physique via repmgr et bascule automatique de l'accès applicatif via une VIP keepalived — c'est le seul mécanisme de haute disponibilité de l'infra basé sur une IP flottante côté base de données, à contraster avec le cluster Zabbix Server, qui utilise une HA native sans VIP.

Architecture¶

• 2 nœuds Rocky Linux 9.6 (clones du template tmpl-rocky96-hardened) sur le segment seg-data (10.100.2.192/27) :
  • INDDATA001 (10.100.2.193) — primaire
  • INDDATA002 (10.100.2.194) — standby (réplication physique streaming)
• PostgreSQL 18 (postgresql-18, dépôt PGDG), extension repmgr (shared_preload_libraries=repmgr) pour l'orchestration de la réplication et le failover automatique (failover='automatic' dans repmgr.conf, avec promote_command/follow_command).
• VIP keepalived 10.100.2.195/27 (VRRP router id 51) : elle ne suit pas une priorité statique par nœud, mais le primaire réellement actif. Le script pg-is-primary.sh (exécuté toutes les 2 secondes par le vrrp_script chk_pg_primary, poids 50) interroge PostgreSQL via SELECT NOT pg_is_in_recovery() pour déterminer quel nœud doit porter la VIP. C'est cette VIP que les clients applicatifs (ex. Zabbix Server) utilisent comme point d'entrée unique.
• Accès réseau : port 5432/tcp ouvert (firewalld, zone drop) ; protocole VRRP autorisé en règle riche firewalld pour keepalived. pg_hba.conf autorise explicitement la réplication entre les 2 nœuds, la base repmgr, ainsi que les clients Zabbix (10.100.2.226/10.100.2.227, soit INDSUPV001/002) vers la base zabbix — aucun autre client applicatif n'est autorisé à ce jour (voir Points d'attention).
• Au niveau réseau NSX, aucune règle DFW dédiée n'est nécessaire pour l'accès depuis seg-supervision : la zone Admin (T1-Admin) reste en allow par défaut (voir NSX), contrairement aux zones DMZ/SOC.

Structure du dépôt¶

flowchart TB
    subgraph SEGDATA["seg-data — 10.100.2.192/27"]
        D1["INDDATA001<br/>10.100.2.193<br/>PostgreSQL 18 + repmgrd"]
        D2["INDDATA002<br/>10.100.2.194<br/>PostgreSQL 18 + repmgrd"]
    end
    D1 -- "réplication physique streaming" --> D2
    K1["keepalived<br/>chk_pg_primary (2s)"] -. "SELECT NOT pg_is_in_recovery()" .-> D1
    K2["keepalived<br/>chk_pg_primary (2s)"] -. "SELECT NOT pg_is_in_recovery()" .-> D2
    K1 & K2 ==> VIP(("VIP 10.100.2.195/27<br/>VRRP id 51"))
    VIP --> ZBX["Zabbix Server HA<br/>INDSUPV001 / INDSUPV002<br/>base zabbix"]

Séquence d'un failover réel (validée le 2026-07-07 dans les deux sens, 001→002 puis 002→001) :

sequenceDiagram
    participant P as INDDATA001 (primaire)
    participant S as INDDATA002 (standby)
    participant K1 as keepalived (001)
    participant K2 as keepalived (002)
    participant C as Client (ex. Zabbix)

    Note over P,S: Fonctionnement normal — réplication streaming
    P->>S: WAL streaming
    K1->>P: chk_pg_primary OK (toutes les 2s)
    K2->>S: chk_pg_primary KO (standby)
    Note over K1: VIP .195 portée par INDDATA001

    Note over P: Panne (postgresql-18 + repmgr-18 arrêtés)
    Note over S: repmgrd détecte l'absence du primaire (~50s observés)
    S->>S: repmgr standby promote (automatic)
    K2->>S: chk_pg_primary devient OK
    K1->>P: chk_pg_primary devient KO
    Note over K2: VIP .195 bascule vers INDDATA002 (~10-15s après promotion)
    C->>S: reconnexion applicative via la VIP
    Note over P: Réintégration manuelle requise (repmgr standby clone --force)

Provisioning Terraform¶

• /root/postgres/main.tf clone les VM depuis data.vsphere_virtual_machine.template via for_each sur la variable nodes.
• IP attribuées dynamiquement depuis NetBox (data source netbox_ip_addresses, filtre sur dns_name en minuscules) — pas d'adresse en dur dans le code.
• lifecycle.ignore_changes couvre annotation, clone[0].template_uuid, clone[0].customize, disk[0].io_share_count — évite une recréation de VM sur dérive cosmétique post-clonage.
• Variables principales (variables.tf) : vsphere_network = "seg-data", vm_gateway = "10.100.2.222", vm_netmask = 27, vm_cpu = 2, vm_ram = 4096, nodes = [INDDATA001, INDDATA002].
• Providers : hashicorp/vsphere (>= 2.6.0), e-breuninger/netbox (>= 5.0.0, < 6.0.0).
• Identifiants vCenter/NetBox attendus dans terraform.tfvars (non versionné, .gitignore) ; modèle disponible dans terraform.tfvars.example.

Configuration Ansible¶

Playbook /root/postgres/ansible/site.yml, 3 rôles appliqués dans l'ordre, chacun tagué (primary_setup/standby_setup/keepalived_setup) pour permettre de cibler une phase précise sans rejouer tout le playbook :

1. postgres_primary (hôte INDDATA001) : initdb (si PG_VERSION absent), listen_addresses='*', shared_preload_libraries='repmgr' (+ restart du service — cette directive n'est pas rechargeable à chaud), bloc géré pg_hba.conf (réplication + base repmgr entre les 2 IP, puis un second bloc dédié pour les clients Zabbix), ouverture du port 5432, création du rôle repmgr (superuser, mot de passe vaulté) et de la base repmgr, sudoers NOPASSWD dédié (/etc/sudoers.d/repmgr-postgres, requis par repmgr pour démarrer/arrêter/recharger PostgreSQL lors d'un switchover), déploiement de repmgr.conf (node_id=1), repmgr primary register --force, démarrage de repmgrd.
2. postgres_standby (hôte INDDATA002) : sudoers identique, déploiement de repmgr.conf (node_id=2), puis — uniquement si le cluster local n'est pas déjà initialisé — repmgr standby clone --force depuis le primaire (initialise entièrement data_directory par streaming, hérite du pg_hba.conf du primaire, y compris son bloc zabbix), bloc pg_hba.conf zabbix redéployé par idempotence, démarrage de PostgreSQL en recovery, repmgr standby register --force, démarrage de repmgrd.
3. postgres_keepalived (groupe postgres, les 2 nœuds, rôle symétrique) : installation de keepalived, activation du booléen SELinux keepalived_connect_any (module ansible.posix.seboolean, persistant), déploiement du script pg-is-primary.sh et de keepalived.conf (VIP 10.100.2.195/27, vrrp_script chk_pg_primary toutes les 2s, poids 50, fall 2/rise 2), autorisation du protocole VRRP dans firewalld (règle riche).

repmgr.conf.j2 (identique structurellement sur les 2 nœuds, seuls node_id/node_name/node_ip varient) définit use_replication_slots=yes, failover='automatic', promote_command='repmgr standby promote ...', follow_command='repmgr standby follow ... --upstream-node-id=%n', et les 4 service_*_command (sudo systemctl {start,stop,restart,reload} postgresql-18) qui s'appuient sur le sudoers déployé par les rôles primary/standby.

Le mot de passe du rôle repmgr ({{ repmgr_password }}) et la passphrase VRRP ({{ pg_vrrp_auth_pass }}) sont stockés chiffrés par Ansible Vault dans group_vars/postgres.yml — aucune valeur en clair dans le dépôt (voir gestion des secrets).

Procédure manuelle¶

systemctl stop repmgr-18 postgresql-18
rm -rf /var/lib/pgsql/18/data
sudo -u postgres mkdir -p /var/lib/pgsql/18/data && chmod 700 /var/lib/pgsql/18/data

# <repmgr_password> : valeur récupérée depuis Ansible Vault (group_vars/postgres.yml)
sudo -u postgres bash -c "PGPASSWORD='<repmgr_password>' repmgr -h <ip_nouveau_primaire> \
    -U repmgr -d repmgr -f /etc/repmgr/18/repmgr.conf standby clone --force"

systemctl start postgresql-18
sudo -u postgres repmgr -f /etc/repmgr/18/repmgr.conf standby register --force
systemctl start repmgr-18

Procédure de déploiement¶

1. terraform apply (dans /root/postgres) : clone les 2 VM et attribue leurs IP via NetBox.
2. ansible-playbook site.yml (dans /root/postgres/ansible) : applique dans l'ordre postgres_primary → postgres_standby → postgres_keepalived — l'ordre est important, le standby devant cloner un primaire déjà initialisé.

Contrôle de santé / Vérification¶

• sudo -u postgres repmgr -f /etc/repmgr/18/repmgr.conf cluster show (sur l'un ou l'autre nœud) : affiche le rôle réel (primary/standby) et l'état (running) de chaque nœud — seule source de vérité fiable sur qui est réellement primaire.
• SELECT client_addr, state, sync_state FROM pg_stat_replication; sur le primaire : confirme le streaming actif vers le standby.
• ip -4 addr show | grep 10.100.2.195 sur chaque nœud : identifie lequel porte actuellement la VIP.
• systemctl status postgresql-18 repmgr-18 keepalived : les 3 services doivent être active (running) sur les 2 nœuds (keepalived tourne partout, PostgreSQL/repmgrd aussi — c'est leur rôle interne, primaire ou standby, qui diffère).
• Test de connexion applicative via la VIP : psql -h 10.100.2.195 -U zabbix -d zabbix depuis un hôte autorisé (ex. INDSUPV001/002) doit aboutir quel que soit le nœud réellement primaire.

Points d'attention¶

• SELinux keepalived_connect_any : sans ce booléen (activé explicitement par le rôle postgres_keepalived), keepalived ne peut pas établir la connexion TCP sortante que fait son vrrp_script vers le port PostgreSQL — le symptôme côté script est trompeur (psql renvoie un simple refus, facilement confondu avec un problème pg_hba.conf) — le script de détection du primaire échouerait silencieusement et la VIP ne basculerait jamais correctement.
• VIP dynamique, pas de priorité statique : les 2 nœuds partagent la même priorité keepalived de base (100) ; c'est le vrrp_script chk_pg_primary (poids 50) qui fait réellement basculer la VIP selon l'état réel de réplication PostgreSQL.
• nopreempt serait un contresens ici : pour une VIP qui doit suivre dynamiquement « qui est primaire » (priorité pilotée par script), la préemption doit rester active (comportement par défaut, non modifié) — sinon un nœud redevenu primaire (priorité plus haute) ne reprendrait jamais la VIP tant que l'ancien primaire (devenu standby, toujours vivant) continue d'émettre ses advertisements VRRP. nopreempt n'a de sens que pour des rôles figés par admin (ex. HAProxy/Squid), pas pour une VIP qui suit un état applicatif changeant.
• Authentification du script de détection : pg-is-primary.sh utilise une connexion TCP/mot de passe (rôle repmgr) plutôt qu'un changement d'utilisateur système, car la directive user d'un vrrp_script keepalived a un bug connu sur la version installée (2.2.8) qui empêche sa ré-exécution après le premier run.
• Mot de passe VRRP limité à 8 caractères : limite du protocole VRRPv2 (auth_type PASS) — keepalived tronque silencieusement (log Truncating auth_pass to 8 characters) sans échouer ; à générer sur 8 caractères maximum dès le départ pour ce secret précis.
• pg_hba.conf n'autorise que les 2 nœuds entre eux et les clients Zabbix : aucun autre client applicatif ne peut se connecter tant qu'une règle dédiée n'est pas ajoutée dans le bloc géré Ansible (postgres_primary/tasks/main.yml) — une règle ajoutée à la main sur un seul nœud pour tester ne survit pas à un failover/reclone (le standby clone écrase pg_hba.conf avec la version du primaire au moment du clone). Décision actuelle : ne rien ouvrir de plus tant qu'aucun besoin applicatif réel ne se présente ; ne pas exposer le rôle repmgr (superuser) à un futur client applicatif — prévoir un rôle dédié non-superuser.
• Fenêtre d'indisponibilité mesurée (test réel du 2026-07-07) : ~50s entre l'arrêt du primaire et la promotion automatique du standby, VIP bascule ~10-15s après. Non modifiés : reconnect_attempts/reconnect_interval par défaut de repmgr.
• Dépendance croisée : la base zabbix de ce cluster est le backend du cluster Zabbix HA — toute intervention sur pg_hba.conf ou sur la VIP impacte directement la supervision.
• Secrets : repmgr_password et pg_vrrp_auth_pass chiffrés via Ansible Vault dans group_vars/postgres.yml — voir gestion des secrets.