{"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\">Your repmgr cluster fails over in 60 seconds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Your application still points at the old primary\u2019s IP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A floating VIP solves this: one stable address that always connects to the current primary, regardless of which physical node that is.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived implements this using VRRP \u2014 a standard protocol designed for exactly this purpose.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This guide adds a VIP to a working two-node PostgreSQL + repmgr cluster.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you do not have that cluster yet, follow the <a href=\"https:\/\/rootfan.com\/fr\/configuration-de-repmgr-pour-postgresql\/\">repmgr setup guide<\/a> first, then come back here.<\/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\">How It Works<\/a><\/li><li><a href=\"#the-environment\">L'environnement<\/a><\/li><li><a href=\"#step-1-install-keepalived-on-both-servers\">Step 1 \u2014 Install Keepalived on Both Servers<\/a><\/li><li><a href=\"#step-2-create-the-health-check-script-on-both-servers\">Step 2 \u2014 Create the Health Check Script on Both Servers<\/a><\/li><li><a href=\"#step-3-configure-keepalived-on-both-servers\">Step 3 \u2014 Configure Keepalived on Both Servers<\/a><ul><li><a href=\"#server-1-configuration-priority-100\">server1 configuration (priority 100)<\/a><\/li><li><a href=\"#server-2-configuration-priority-90\">server2 configuration (priority 90)<\/a><\/li><\/ul><\/li><li><a href=\"#step-4-start-keepalived-and-verify-the-vip\">Step 4 \u2014 Start Keepalived and Verify the VIP<\/a><\/li><li><a href=\"#step-5-test-vip-failover\">Step 5 \u2014 Test VIP Failover<\/a><\/li><li><a href=\"#step-6-test-vip-switchover\">Step 6 \u2014 Test VIP Switchover<\/a><\/li><li><a href=\"#frequently-asked-questions\">Foire aux questions<\/a><ul><li><a href=\"#faq-question-1779310385824\">Why does the health check script use psql directly instead of sudo -u postgres psql?<\/a><\/li><li><a href=\"#faq-question-1779310386824\">Why do both nodes use state BACKUP instead of one using state MASTER?<\/a><\/li><li><a href=\"#faq-question-1779310387824\">How long does it take for the VIP to move after a failover?<\/a><\/li><li><a href=\"#faq-question-1779310388824\">Does the VIP move during a planned switchover?<\/a><\/li><li><a href=\"#faq-question-1779310389824\">What is VRRP and why is it used here?<\/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\">How It Works<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived runs a health check script on each node every 2 seconds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The script connects to the local PostgreSQL instance via Unix socket and queries <code>pg_is_in_recovery()<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If the node is the primary (the function returns <code>f<\/code>), the script exits 0 \u2014 Keepalived keeps or claims the VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If the node is a standby or PostgreSQL is unreachable (the function returns <code>t<\/code> or the connection fails), the script exits 1 \u2014 Keepalived releases the VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The node with the highest effective priority holds the VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Server1 has a base priority of 100, server2 has a base priority of 90.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The health check script is configured with a weight of -50.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When a node\u2019s script fails, its effective priority drops by 50: server1 drops from 100 to 50, server2 from 90 to 40.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The primary node always wins \u2014 its script succeeds and its effective priority stays at its base value.<\/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>Prerequisites:<\/strong> a working two-node PostgreSQL 18 + repmgr cluster.<\/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>server1 (Ubuntu 24.04)<\/td><td>192.168.0.181<\/td><td>PostgreSQL node<\/td><\/tr><tr><td>server2 (Ubuntu 24.04)<\/td><td>192.168.0.182<\/td><td>PostgreSQL node<\/td><\/tr><tr><td>VIP<\/td><td>192.168.0.180<\/td><td>Floats to the current primary<\/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\">Step 1 \u2014 Install Keepalived on Both Servers<\/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\">Step 2 \u2014 Create the Health Check Script on Both Servers<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The script connects to the local PostgreSQL instance via Unix socket and checks whether the node is the primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It exits 0 on the primary, 1 on a standby or if PostgreSQL is unreachable.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">One pitfall matters here: Keepalived runs this script as the <code>PostgreSQL<\/code> OS user, configured via <code>script_user postgres postgres<\/code> en <code>global_defs<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Because the script already runs as postgres, call <code>psql<\/code> directly \u2014 do not use <code>runuser<\/code> ou <code>sudo -u postgres<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>runuser<\/code> requires root and will fail silently when called by a non-root user, causing the script to always exit 1 on both nodes and the VIP to behave incorrectly.<\/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\">Test the script manually as the postgres user:<\/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\">Step 3 \u2014 Configure Keepalived on Both Servers<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Back up the default config on both servers before overwriting:<\/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\">server1 configuration (priority 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\">server2 configuration (priority 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\">Both nodes use <code>state BACKUP<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Keepalived elects the master dynamically based on effective priority \u2014 there is no need to set one node to <code>state MASTER<\/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\">Step 4 \u2014 Start Keepalived and Verify the 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\">Allow 5\u201310 seconds after startup for the health checks to stabilise, then verify the VIP is on the current primary.<\/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\">Add the VIP to <code>.pgpass<\/code> on both servers so repmgr can connect through it without a password prompt:<\/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\">Verify the VIP is reachable and connects to the primary:<\/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\">Step 5 \u2014 Test VIP Failover<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Note which node currently holds the 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\">Stop PostgreSQL on the primary to trigger automatic failover:<\/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\">Watch Keepalived on server2 pick up the 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\">Verify the VIP has moved:<\/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\">Step 6 \u2014 Test VIP Switchover<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A clean switchover also moves the VIP.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The old primary becomes a standby \u2014 its health check script fails \u2014 the VIP moves to the new primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Before running a switchover, re-integrate the failed node from the previous test as a standby.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">On the standby (the node that will become the new primary):<\/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\">During switchover, repmgr stops the old primary via SSH but does not restart it automatically.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Start PostgreSQL manually on the demoted node when the switchover output shows \u201cwaiting for node X to connect\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\">Verify the VIP moved back to server1:<\/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>Why does the health check script use psql directly instead of sudo -u postgres psql?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Keepalived runs the script as the postgres OS user via <code>script_user postgres postgres<\/code> en <code>global_defs<\/code>.<br \/>The script already runs as postgres, so calling <code>psql<\/code> directly connects via Unix socket using peer authentication.<br \/>Utilisation <code>runuser<\/code> ou <code>sudo -u postgres<\/code> inside the script will fail silently &#8212; both require root, and the script is not running as root.<br \/>The result is that both nodes always exit 1, and neither holds the VIP correctly.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310386824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>Why do both nodes use state BACKUP instead of one using state MASTER?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>With a weight-based health check, the VRRP master is determined dynamically by effective priority, not by the static <code>\u00e9tat<\/code> directive.<br \/>If one node is set to <code>state MASTER<\/code>, it will claim the VIP at startup regardless of the health check result, causing a race condition during the first few seconds.<br \/>Utilisation <code>state BACKUP<\/code> on both nodes lets the health check script determine which node should hold the VIP from the start.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310387824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>How long does it take for the VIP to move after a failover?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>The VIP moves once two conditions are met: repmgrd has promoted the standby to primary (approximately 60 seconds with default settings), and Keepalived's health check has confirmed the promotion (up to <code>fall 2<\/code> &times; <code>interval 2<\/code> = 4 seconds).<br \/>Total time from primary failure to VIP moving is approximately 60&#8211;70 seconds with the configuration in this 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>Does the VIP move during a planned switchover?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>Oui.<br \/>When <code>repmgr standby switchover<\/code> demotes the old primary, that node's health check script starts returning 1 (because the node is now a standby).<br \/>Keepalived detects the change within <code>fall 2<\/code> &times; <code>interval 2<\/code> = 4 seconds and moves the VIP to the new primary.<\/p>\n\n<\/div>\n<\/div>\n<div id=\"faq-question-1779310389824\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question\"><strong>What is VRRP and why is it used here?<\/strong><\/h3>\n<div class=\"rank-math-answer\">\n\n<p>VRRP (Virtual Router Redundancy Protocol) is a standard network protocol (RFC 5798) designed to provide automatic assignment of IP routers to participating hosts.<br \/>Keepalived implements VRRP to manage the floating VIP &#8212; multiple nodes participate in a VRRP group and elect a master based on priority.<br \/>The master holds the VIP; if the master fails or its priority drops below another node's, a new master is elected and the VIP moves.<\/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 adds a floating VIP to an existing PostgreSQL + repmgr cluster with minimal configuration.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The health check script is the critical component: it must run as the postgres OS user and call <code>psql<\/code> directly \u2014 not through <code>runuser<\/code> ou <code>sudo<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With <code>fall 2<\/code> et <code>interval 2<\/code>, the VIP moves within 4 seconds of Keepalived detecting the role change, which happens automatically after repmgrd promotes the standby.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you are designing a PostgreSQL high-availability architecture and want a second opinion before going to production, <a href=\"https:\/\/rootfan.com\/fr\/services\/\">prendre contact \u2192<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>TL;DR: A repmgr cluster handles automatic failover \u2014 but applications still need to know which node is the current primary. Keepalived solves this with a floating Virtual IP (VIP) that moves automatically to whichever node holds the primary role. This guide adds a VIP to an existing PostgreSQL 18 + repmgr cluster on Ubuntu 24.04 &hellip; <\/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 &laquo;&nbsp;PostgreSQL repmgr with Keepalived Adding a Floating VIP&nbsp;&raquo;<\/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_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}]}}