{"id":6867,"date":"2026-05-27T14:52:10","date_gmt":"2026-05-27T12:52:10","guid":{"rendered":"https:\/\/rootfan.com\/?p=6867"},"modified":"2026-05-27T14:54:02","modified_gmt":"2026-05-27T12:54:02","slug":"postgresql-repmgr-avec-keepalived","status":"publish","type":"post","link":"https:\/\/rootfan.com\/fr\/postgresql-repmgr-with-keepalived\/","title":{"rendered":"PostgreSQL repmgr avec Keepalived Ajout d'une VIP flottante"},"content":{"rendered":"<p class=\"wp-block-paragraph\" id=\"tl-dr\">TL;DR : Un cluster repmgr g\u00e8re le basculement automatique, mais les applications doivent toujours savoir quel n\u0153ud est le primaire actuel.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived r\u00e9sout ce probl\u00e8me avec une adresse IP virtuelle (VIP) flottante qui se d\u00e9place automatiquement vers le n\u0153ud qui d\u00e9tient le r\u00f4le principal.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ce guide ajoute un VIP \u00e0 un cluster PostgreSQL 18 + repmgr existant sur Ubuntu 24.04 \u00e0 l'aide de Keepalived 2.x.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Chaque \u00e9tape a \u00e9t\u00e9 ex\u00e9cut\u00e9e en direct sur un cluster r\u00e9el et la sortie a \u00e9t\u00e9 v\u00e9rifi\u00e9e.<\/p>\n\n\n\n<!--more-->\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Votre cluster repmgr bascule en 60 secondes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Votre application pointe toujours vers l'IP de l'ancien primaire.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Une adresse VIP flottante r\u00e9sout ce probl\u00e8me : une adresse stable qui se connecte toujours au primaire actuel, quel que soit le n\u0153ud physique concern\u00e9.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived impl\u00e9mente ceci en utilisant VRRP \u2014 un protocole standard con\u00e7u pr\u00e9cis\u00e9ment \u00e0 cet effet.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ce guide ajoute un VIP \u00e0 un cluster PostgreSQL + repmgr fonctionnel \u00e0 deux n\u0153uds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Si vous n'avez pas encore ce cluster, suivez le <a href=\"https:\/\/rootfan.com\/fr\/configuration-de-repmgr-pour-postgresql\/\">Guide d'installation de repmgr<\/a> d'abord, puis reviens ici.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table des mati\u00e8res<\/h2><nav><ul><li><a href=\"#how-it-works\">Comment \u00e7a marche<\/a><\/li><li><a href=\"#the-environment\">L'environnement<\/a><\/li><li><a href=\"#step-1-install-keepalived-on-both-servers\">\u00c9tape 1 \u2014 Installer Keepalived sur les deux serveurs<\/a><\/li><li><a href=\"#step-2-create-the-health-check-script-on-both-servers\">\u00c9tape 2 \u2014 Cr\u00e9er le script de v\u00e9rification de l'\u00e9tat sur les deux serveurs<\/a><\/li><li><a href=\"#step-3-configure-keepalived-on-both-servers\">\u00c9tape 3 \u2014 Configurer Keepalived sur les deux serveurs<\/a><ul><li><a href=\"#server-1-configuration-priority-100\">configuration server1 (priorit\u00e9 100)<\/a><\/li><li><a href=\"#server-2-configuration-priority-90\">configuration du serveur2 (priorit\u00e9 90)<\/a><\/li><\/ul><\/li><li><a href=\"#step-4-start-keepalived-and-verify-the-vip\">\u00c9tape 4 \u2014 D\u00e9marrer Keepalived et v\u00e9rifier la VIP<\/a><\/li><li><a href=\"#step-5-test-vip-failover\">\u00c9tape 5 \u2014 Tester le basculement VIP<\/a><\/li><li><a href=\"#step-6-test-vip-switchover\">\u00c9tape 6 \u2014 Test de basculement VIP<\/a><\/li><li><a href=\"#frequently-asked-questions\">Foire aux questions<\/a><ul><li><a href=\"#faq-question-1779310385824\">Pourquoi le script de v\u00e9rification de l'\u00e9tat utilise-t-il psql directement au lieu de sudo -u postgres psql ?<\/a><\/li><li><a href=\"#faq-question-1779310386824\">Pourquoi les deux n\u0153uds utilisent-ils l'\u00e9tat BACKUP au lieu d'un utilisant l'\u00e9tat MASTER ?<\/a><\/li><li><a href=\"#faq-question-1779310387824\">Combien de temps faut-il au VIP pour se d\u00e9placer apr\u00e8s un basculement ?<\/a><\/li><li><a href=\"#faq-question-1779310388824\">Le VIP bouge-t-il lors d'une bascule planifi\u00e9e ?<\/a><\/li><li><a href=\"#faq-question-1779310389824\">VRPP est le protocole de redondance des routeurs. Il est utilis\u00e9 pour assurer la redondance au niveau de la passerelle par d\u00e9faut pour les h\u00f4tes d'un sous-r\u00e9seau. L'objectif est d'emp\u00eacher un point de d\u00e9faillance unique dans un r\u00e9seau en cas de d\u00e9faillance du routeur.<\/a><\/li><\/ul><\/li><li><a href=\"#in-summary\">En r\u00e9sum\u00e9<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"how-it-works\" class=\"wp-block-heading\">Comment \u00e7a marche<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived ex\u00e9cute un script de v\u00e9rification de l'\u00e9tat sur chaque n\u0153ud toutes les 2 secondes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Le script se connecte \u00e0 l'instance PostgreSQL locale via une socket Unix et interroge <code>pg_is_in_recovery()<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Si le n\u0153ud est le principal (la fonction retourne <code>f<\/code>), le script se termine avec 0 \u2014 Keepalived conserve ou revendique la VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Si le n\u0153ud est en mode veille ou si PostgreSQL est inaccessible (la fonction renvoie <code>t<\/code> ou la connexion \u00e9choue), le script se termine avec 1 \u2014 Keepalived lib\u00e8re la VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Le n\u0153ud avec la priorit\u00e9 effective la plus \u00e9lev\u00e9e d\u00e9tient la VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Server1 a une priorit\u00e9 de base de 100, server2 a une priorit\u00e9 de base de 90.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Le script de v\u00e9rification de l'\u00e9tat de sant\u00e9 est configur\u00e9 avec un poids de -50.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lorsqu'un script d'un n\u0153ud \u00e9choue, sa priorit\u00e9 effective diminue de 50 : server1 passe de 100 \u00e0 50, server2 de 90 \u00e0 40.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Le n\u0153ud primaire gagne toujours \u2014 son script r\u00e9ussit et sa priorit\u00e9 effective reste \u00e0 sa valeur de base.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"the-environment\" class=\"wp-block-heading\">L'environnement<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Pr\u00e9requis :<\/strong> Un cluster PostgreSQL 18 + repmgr fonctionnel \u00e0 deux n\u0153uds.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>H\u00f4te<\/th><th>CI<\/th><th>R\u00f4le<\/th><\/tr><\/thead><tbody><tr><td>serveur1 (Ubuntu 24.04)<\/td><td>192.168.0.181<\/td><td>N\u0153ud PostgreSQL<\/td><\/tr><tr><td>serveur2 (Ubuntu 24.04)<\/td><td>192.168.0.182<\/td><td>N\u0153ud PostgreSQL<\/td><\/tr><tr><td>VIP<\/td><td>192.168.0.180<\/td><td>Flottant vers le principal actuel<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"step-1-install-keepalived-on-both-servers\" class=\"wp-block-heading\">\u00c9tape 1 \u2014 Installer Keepalived sur les deux serveurs<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur1 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo apt update\nsudo apt install -y keepalived\n\nkeepalived --version\n# Expected: Keepalived v2.x.x\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur2 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nsudo apt update\nsudo apt install -y keepalived\n\nkeepalived --version\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"step-2-create-the-health-check-script-on-both-servers\" class=\"wp-block-heading\">\u00c9tape 2 \u2014 Cr\u00e9er le script de v\u00e9rification de l'\u00e9tat sur les deux serveurs<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Le script se connecte \u00e0 l'instance PostgreSQL locale via une socket Unix et v\u00e9rifie si le n\u0153ud est le primaire.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Il retourne 0 sur le ma\u00eetre, 1 sur un secondaire ou si PostgreSQL est injoignable.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Un pi\u00e8ge est \u00e0 consid\u00e9rer ici : Keepalived ex\u00e9cute ce script en tant que <code>PostgreSQL<\/code> Utilisateur de l'OS, configur\u00e9 via <code>script_utilisateur postgres postgres<\/code> en <code>global_defs<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Parce que le script s'ex\u00e9cute d\u00e9j\u00e0 en tant que postgres, appelez <code>psql<\/code> directement \u2014 n'utilisez pas <code>ex\u00e9cutez luser<\/code> ou <code>sudo -u postgres<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>ex\u00e9cutez luser<\/code> n\u00e9cessite l'acc\u00e8s root et \u00e9chouera silencieusement lorsqu'il sera appel\u00e9 par un utilisateur non root, ce qui entra\u00eenera toujours la sortie du script avec 1 sur les deux n\u0153uds et le comportement incorrect de la VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur1 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo tee \/usr\/local\/bin\/check_postgres_primary.sh &gt; \/dev\/null &lt;&lt; &#039;EOF&#039;\n#!\/bin\/bash\nresult=$(psql -t -c &quot;SELECT pg_is_in_recovery();&quot; 2&gt;\/dev\/null | tr -d &#039;&#x5B;:space:]&#039;)\n&#x5B; &quot;$result&quot; = &quot;f&quot; ]\nEOF\n\n# The script must be executable \u2014 Keepalived will not run it otherwise\nsudo chmod +x \/usr\/local\/bin\/check_postgres_primary.sh\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Testez le script manuellement en tant qu'utilisateur postgres :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo -u postgres \/usr\/local\/bin\/check_postgres_primary.sh\necho $?\n# Expected: 0 if server1 is currently the primary, 1 if it is a standby\n# Always test as postgres \u2014 running as root will give a different result\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur2 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nsudo tee \/usr\/local\/bin\/check_postgres_primary.sh &gt; \/dev\/null &lt;&lt; &#039;EOF&#039;\n#!\/bin\/bash\nresult=$(psql -t -c &quot;SELECT pg_is_in_recovery();&quot; 2&gt;\/dev\/null | tr -d &#039;&#x5B;:space:]&#039;)\n&#x5B; &quot;$result&quot; = &quot;f&quot; ]\nEOF\n\nsudo chmod +x \/usr\/local\/bin\/check_postgres_primary.sh\n\nsudo -u postgres \/usr\/local\/bin\/check_postgres_primary.sh\necho $?\n# Expected: 1 \u2014 server2 is currently a standby\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"step-3-configure-keepalived-on-both-servers\" class=\"wp-block-heading\">\u00c9tape 3 \u2014 Configurer Keepalived sur les deux serveurs<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sauvegardez la configuration par d\u00e9faut sur les deux serveurs avant d'\u00e9craser :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nsudo cp \/etc\/keepalived\/keepalived.conf \/etc\/keepalived\/keepalived.conf.20260519 2&gt;\/dev\/null || true\n<\/pre><\/div>\n\n\n<h3 id=\"server-1-configuration-priority-100\" class=\"wp-block-heading\">configuration server1 (priorit\u00e9 100)<\/h3>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo tee \/etc\/keepalived\/keepalived.conf &gt; \/dev\/null &lt;&lt; &#039;EOF&#039;\nglobal_defs {\n    # Run health check scripts as the postgres OS user\n    # This allows the script to connect via Unix socket using peer authentication\n    script_user postgres postgres\n    enable_script_security\n}\n\nvrrp_script check_postgres {\n    script &quot;\/usr\/local\/bin\/check_postgres_primary.sh&quot;\n    # Run the check every 2 seconds\n    interval 2\n    # If the script fails, subtract 50 from this node&#039;s effective priority\n    # server1 base priority is 100 \u2014 on failure it drops to 50, losing to server2 (base 90)\n    weight -50\n    # Number of consecutive failures before declaring the script failed\n    fall 2\n    # Number of consecutive successes before declaring the script recovered\n    rise 2\n}\n\nvrrp_instance VI_POSTGRES {\n    state BACKUP\n    interface enp0s3\n    virtual_router_id 51\n    # Base priority \u2014 must differ between nodes; server1 is preferred primary candidate\n    priority 100\n    advert_int 1\n\n    authentication {\n        auth_type PASS\n        auth_pass pg_vip_2026\n    }\n\n    virtual_ipaddress {\n        192.168.0.180\/24\n    }\n\n    track_script {\n        check_postgres\n    }\n}\nEOF\n<\/pre><\/div>\n\n\n<h3 id=\"server-2-configuration-priority-90\" class=\"wp-block-heading\">configuration du serveur2 (priorit\u00e9 90)<\/h3>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nsudo tee \/etc\/keepalived\/keepalived.conf &gt; \/dev\/null &lt;&lt; &#039;EOF&#039;\nglobal_defs {\n    script_user postgres postgres\n    enable_script_security\n}\n\nvrrp_script check_postgres {\n    script &quot;\/usr\/local\/bin\/check_postgres_primary.sh&quot;\n    interval 2\n    # server2 base priority is 90 \u2014 on failure it drops to 40\n    weight -50\n    fall 2\n    rise 2\n}\n\nvrrp_instance VI_POSTGRES {\n    state BACKUP\n    interface enp0s3\n    virtual_router_id 51\n    # Lower base priority than server1 \u2014 server1 holds the VIP when both are healthy\n    priority 90\n    advert_int 1\n\n    authentication {\n        auth_type PASS\n        auth_pass pg_vip_2026\n    }\n\n    virtual_ipaddress {\n        192.168.0.180\/24\n    }\n\n    track_script {\n        check_postgres\n    }\n}\nEOF\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Les deux n\u0153uds utilisent <code>\u00e9tat SAUVEGARDE<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived \u00e9lit le ma\u00eetre dynamiquement en fonction de la priorit\u00e9 effective \u2014 il n'est pas n\u00e9cessaire d'en d\u00e9finir un dans un n\u0153ud <code>\u00e9tat MA\u00ceTRE<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"step-4-start-keepalived-and-verify-the-vip\" class=\"wp-block-heading\">\u00c9tape 4 \u2014 D\u00e9marrer Keepalived et v\u00e9rifier la VIP<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur1 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo systemctl enable keepalived\nsudo systemctl start keepalived\nsudo systemctl status keepalived\n# Expected: active (running)\n# If failed: sudo journalctl -u keepalived -n 30\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur2 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nsudo systemctl enable keepalived\nsudo systemctl start keepalived\nsudo systemctl status keepalived\n# Expected: active (running)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Laissez 5 \u00e0 10 secondes apr\u00e8s le d\u00e9marrage pour que les v\u00e9rifications d'int\u00e9grit\u00e9 se stabilisent, puis v\u00e9rifiez que le VIP est sur le principal actuel.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur1 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nip addr show enp0s3 | grep 192.168.0.180\n# Expected: inet 192.168.0.180\/24 \u2014 VIP is present if server1 is the current primary\n# If not present and server1 IS the primary: wait 10 seconds and retry\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Sur le serveur2 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nip addr show enp0s3 | grep 192.168.0.180\n# Expected: no output \u2014 server2 is standby and does not hold the VIP\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Ajouter le VIP \u00e0 <code>.pgpass<\/code> sur les deux serveurs afin que repmgr puisse s'y connecter sans invite de mot de passe :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo -u postgres bash -c &#039;echo &quot;192.168.0.180:5432:repmgr:repmgr:repmgr&quot; &gt;&gt; \/var\/lib\/postgresql\/.pgpass&#039;\nsudo -u postgres bash -c &#039;echo &quot;192.168.0.180:5432:replication:repmgr:repmgr&quot; &gt;&gt; \/var\/lib\/postgresql\/.pgpass&#039;\n<\/pre><\/div>\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nsudo -u postgres bash -c &#039;echo &quot;192.168.0.180:5432:repmgr:repmgr:repmgr&quot; &gt;&gt; \/var\/lib\/postgresql\/.pgpass&#039;\nsudo -u postgres bash -c &#039;echo &quot;192.168.0.180:5432:replication:repmgr:repmgr&quot; &gt;&gt; \/var\/lib\/postgresql\/.pgpass&#039;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">V\u00e9rifiez que le VIP est joignable et se connecte au primaire :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1 or server2\nping -c 3 192.168.0.180\n# Expected: replies from 192.168.0.180\n\n# Connect to PostgreSQL via VIP \u2014 must run as postgres OS user to use .pgpass\nsudo -u postgres psql -h 192.168.0.180 -U repmgr -d repmgr -c &quot;SELECT pg_is_in_recovery(), inet_server_addr();&quot;\n# Expected: f (false) | 192.168.0.180 \u2014 connected to the primary through the VIP\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"step-5-test-vip-failover\" class=\"wp-block-heading\">\u00c9tape 5 \u2014 Tester le basculement VIP<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Notez quel n\u0153ud d\u00e9tient actuellement le VIP :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nip addr show enp0s3 | grep 192.168.0.180\n# Record which node holds the VIP before triggering the failover\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Arr\u00eater PostgreSQL sur le primaire pour d\u00e9clencher le basculement automatique :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nsudo systemctl stop postgresql\n# repmgrd on server2 will detect this and promote server2 after ~60 seconds\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Regardez Keepalived sur le serveur2 prendre en charge la VIP :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nsudo journalctl -u keepalived -f\n# Expected sequence:\n#   Script check_postgres_primary.sh succeeded \u2014 server2 now primary after promotion\n#   VRRP_Instance(VI_POSTGRES) Entering MASTER STATE\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">V\u00e9rifiez que le VIP a boug\u00e9 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server2\nip addr show enp0s3 | grep 192.168.0.180\n# Expected: inet 192.168.0.180\/24 \u2014 VIP is now on server2\n\npsql -h 192.168.0.180 -U repmgr -d repmgr -c &quot;SELECT pg_is_in_recovery(), inet_server_addr();&quot;\n# Expected: f | 192.168.0.182 \u2014 VIP now connects to server2, which is the new primary\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"step-6-test-vip-switchover\" class=\"wp-block-heading\">\u00c9tape 6 \u2014 Test de basculement VIP<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Un basculement propre d\u00e9place \u00e9galement le VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">L'ancien primaire devient un secondaire \u2014 son script de v\u00e9rification d'\u00e9tat \u00e9choue \u2014 le VIP bascule vers le nouveau primaire.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Avant d'ex\u00e9cuter un basculement, r\u00e9int\u00e9grez le n\u0153ud d\u00e9faillant du test pr\u00e9c\u00e9dent en tant que serveur de secours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sur la r\u00e9plication (le n\u0153ud qui deviendra le nouveau primaire) :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1 (assuming server1 is currently standby)\nsudo -u postgres repmgr standby switchover\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Lors du basculement, repmgr arr\u00eate l'ancien primaire via SSH mais ne le red\u00e9marre pas automatiquement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">D\u00e9marrez PostgreSQL manuellement sur le n\u0153ud d\u00e9class\u00e9 lorsque le message de basculement indique \u201c en attente de la connexion du n\u0153ud X \u201d :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On the demoted node (the old primary)\nsudo systemctl start postgresql\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">V\u00e9rifiez que le VIP a bien \u00e9t\u00e9 transf\u00e9r\u00e9 vers le serveur 1 :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# On server1\nip addr show enp0s3 | grep 192.168.0.180\n# Expected: inet 192.168.0.180\/24 \u2014 VIP is back on server1\n\npsql -h 192.168.0.180 -U repmgr -d repmgr -c &quot;SELECT pg_is_in_recovery(), inet_server_addr();&quot;\n# Expected: f | 192.168.0.181 \u2014 VIP connects to server1, now the primary\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"frequently-asked-questions\" class=\"wp-block-heading\">Foire aux questions<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<div class=\"rank-math-list\">\n<div id=\"faq-question-1779310385824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Pourquoi le script de v\u00e9rification de l'\u00e9tat utilise-t-il psql directement au lieu de sudo -u postgres psql ?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Keepalived ex\u00e9cute le script en tant qu'utilisateur postgres du syst\u00e8me d'exploitation via <code>script_utilisateur postgres postgres<\/code> en <code>global_defs<\/code>.<br \/>Le script s&#x27;ex\u00e9cute d\u00e9j\u00e0 sous l&#x27;identit\u00e9 de postgres, donc appeler <code>psql<\/code> se connecte directement via un socket Unix en utilisant l&#x27;authentification par pair.<br \/>Utilisation <code>ex\u00e9cutez luser<\/code> ou <code>sudo -u postgres<\/code> \u00e0 l'int\u00e9rieur du script \u00e9chouera silencieusement \u2014 les deux n\u00e9cessitent les privil\u00e8ges root, et le script ne s'ex\u00e9cute pas en tant que root.<br \/>Le r\u00e9sultat est que les deux n\u0153uds se terminent toujours avec 1, et aucun ne d\u00e9tient correctement le VIP.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310386824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Pourquoi les deux n\u0153uds utilisent-ils l'\u00e9tat BACKUP au lieu d'un utilisant l'\u00e9tat MASTER ?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Avec une v\u00e9rification de sant\u00e9 bas\u00e9e sur le poids, le ma\u00eetre VRRP est d\u00e9termin\u00e9 dynamiquement par la priorit\u00e9 effective, et non par la priorit\u00e9 statique. <code>\u00e9tat<\/code> directive.<br \/>Si un n\u0153ud est configur\u00e9 pour <code>\u00e9tat MA\u00ceTRE<\/code>, il revendiquera le VIP au d\u00e9marrage, quel que soit le r\u00e9sultat de la v\u00e9rification de l'\u00e9tat, provoquant une condition de concurrence pendant les premi\u00e8res secondes.<br \/>Utilisation <code>\u00e9tat SAUVEGARDE<\/code> sur les deux n\u0153uds permet au script de v\u00e9rification de l&#x27;\u00e9tat de sant\u00e9 de d\u00e9terminer d\u00e8s le d\u00e9part quel n\u0153ud doit h\u00e9berger l&#x27;adresse VIP.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310387824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Combien de temps faut-il au VIP pour se d\u00e9placer apr\u00e8s un basculement ?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Le VIP se d\u00e9place une fois que deux conditions sont remplies : repmgrd a promu le standby en primaire (environ 60 secondes avec les param\u00e8tres par d\u00e9faut), et la v\u00e9rification de sant\u00e9 de Keepalived a confirm\u00e9 la promotion (jusqu'\u00e0 <code>l'automne 2<\/code> \u00d7 <code>intervalle 2<\/code> = 4 secondes).<br \/>Le temps total entre la d\u00e9faillance primaire et le d\u00e9placement du VIP est d'environ 60 \u00e0 70 secondes avec la configuration d\u00e9crite dans ce guide.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310388824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Le VIP bouge-t-il lors d'une bascule planifi\u00e9e ?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Oui.<br \/>Quand <code>Basculement vers le mode veille de repmgr<\/code> d\u00e9grade l&#x27;ancien n\u0153ud principal ; le script de v\u00e9rification de l&#x27;\u00e9tat de ce n\u0153ud commence alors \u00e0 renvoyer la valeur 1 (car le n\u0153ud est d\u00e9sormais en mode de secours).<br \/>Keepalived d\u00e9tecte le changement dans <code>l'automne 2<\/code> \u00d7 <code>intervalle 2<\/code> = 4 secondes et transf\u00e8re le VIP vers le nouveau serveur principal.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310389824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>VRPP est le protocole de redondance des routeurs. Il est utilis\u00e9 pour assurer la redondance au niveau de la passerelle par d\u00e9faut pour les h\u00f4tes d'un sous-r\u00e9seau. L'objectif est d'emp\u00eacher un point de d\u00e9faillance unique dans un r\u00e9seau en cas de d\u00e9faillance du routeur.<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>VRRP (Virtual Router Redundancy Protocol) est un protocole r\u00e9seau standard (RFC 5798) con\u00e7u pour permettre l'attribution automatique de routeurs IP aux h\u00f4tes participants.<br \/>Keepalived met en \u0153uvre VRRP pour g\u00e9rer le VIP flottant \u2014 plusieurs n\u0153uds participent \u00e0 un groupe VRRP et \u00e9lisent un ma\u00eetre en fonction de la priorit\u00e9.<br \/>Le ma\u00eetre d\u00e9tient l&#x27;adresse VIP ; si le ma\u00eetre tombe en panne ou si sa priorit\u00e9 passe en dessous de celle d&#x27;un autre n\u0153ud, un nouveau ma\u00eetre est \u00e9lu et l&#x27;adresse VIP est transf\u00e9r\u00e9e.<\/p>\n\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"in-summary\" class=\"wp-block-heading\">En r\u00e9sum\u00e9<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived permet d&#x27;ajouter un n\u0153ud VIP flottant \u00e0 un cluster PostgreSQL + repmgr existant avec une configuration minimale.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Le script de v\u00e9rification de l'\u00e9tat de sant\u00e9 est le composant critique : il doit s'ex\u00e9cuter en tant qu'utilisateur syst\u00e8me postgres et appeler <code>psql<\/code> directement \u2014 et non par l&#x27;interm\u00e9diaire de <code>ex\u00e9cutez luser<\/code> ou <code>sudo<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Avec <code>l'automne 2<\/code> et <code>intervalle 2<\/code>, le VIP se met en place dans les 4 secondes suivant la d\u00e9tection du changement de r\u00f4le par Keepalived, qui se produit automatiquement apr\u00e8s que repmgrd a promu le serveur de secours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Si vous concevez une architecture de haute disponibilit\u00e9 PostgreSQL et que vous souhaitez un deuxi\u00e8me avis avant de passer en production, <a href=\"https:\/\/rootfan.com\/fr\/services\/\">prendre contact \u2192<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>TL;DR : Un cluster repmgr g\u00e8re le basculement automatique \u2014 mais les applications doivent toujours savoir quel n\u0153ud est le primaire actuel. Keepalived r\u00e9sout ce probl\u00e8me avec une IP virtuelle (VIP) flottante qui se d\u00e9place automatiquement vers le n\u0153ud qui d\u00e9tient le r\u00f4le de primaire. Ce guide ajoute une VIP \u00e0 un cluster PostgreSQL 18 + repmgr existant sur Ubuntu 24.04 \u2026 <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/rootfan.com\/fr\/postgresql-repmgr-with-keepalived\/\" class=\"more-link\">Continuer la lecture<span class=\"screen-reader-text\"> de \u00ab\u00a0PostgreSQL repmgr avec Keepalived Ajout d'une VIP flottante\u00a0\u00bb<\/span><\/a><\/p>","protected":false},"author":1,"featured_media":6878,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_focus_keyword":"postgresql keepalived vip","rank_math_title":"PostgreSQL repmgr with Keepalived Adding a Floating VIP","rank_math_description":"Step-by-step guide to adding a floating Virtual IP to a PostgreSQL 18 + repmgr cluster using Keepalived on Ubuntu 24.04. The VIP moves automatically to the current primary after failover or switchover.","rank_math_robots":"","rank_math_og_title":"","rank_math_og_description":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[126],"tags":[127,81],"class_list":["post-6867","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-architecture","tag-step-by-step"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/rootfan.com\/wp-content\/uploads\/8371682954_6b070daa8c_b-1.jpg?fit=1024%2C576&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6867","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/comments?post=6867"}],"version-history":[{"count":9,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6867\/revisions"}],"predecessor-version":[{"id":6880,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6867\/revisions\/6880"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/media\/6878"}],"wp:attachment":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/media?parent=6867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/categories?post=6867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/tags?post=6867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}