PostgreSQL repmgr avec Keepalived Ajout d'une VIP flottante

# On server1
sudo apt update
sudo apt install -y keepalived

keepalived --version
# Expected: Keepalived v2.x.x

# On server2
sudo apt update
sudo apt install -y keepalived

keepalived --version

# On server1
sudo tee /usr/local/bin/check_postgres_primary.sh > /dev/null << 'EOF'
#!/bin/bash
result=$(psql -t -c "SELECT pg_is_in_recovery();" 2>/dev/null | tr -d '[:space:]')
[ "$result" = "f" ]
EOF

# The script must be executable — Keepalived will not run it otherwise
sudo chmod +x /usr/local/bin/check_postgres_primary.sh

# On server1
sudo -u postgres /usr/local/bin/check_postgres_primary.sh
echo $?
# Expected: 0 if server1 is currently the primary, 1 if it is a standby
# Always test as postgres — running as root will give a different result

# On server2
sudo tee /usr/local/bin/check_postgres_primary.sh > /dev/null << 'EOF'
#!/bin/bash
result=$(psql -t -c "SELECT pg_is_in_recovery();" 2>/dev/null | tr -d '[:space:]')
[ "$result" = "f" ]
EOF

sudo chmod +x /usr/local/bin/check_postgres_primary.sh

sudo -u postgres /usr/local/bin/check_postgres_primary.sh
echo $?
# Expected: 1 — server2 is currently a standby

sudo cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.20260519 2>/dev/null || true

# On server1
sudo tee /etc/keepalived/keepalived.conf > /dev/null << 'EOF'
global_defs {
    # Run health check scripts as the postgres OS user
    # This allows the script to connect via Unix socket using peer authentication
    script_user postgres postgres
    enable_script_security
}

vrrp_script check_postgres {
    script "/usr/local/bin/check_postgres_primary.sh"
    # Run the check every 2 seconds
    interval 2
    # If the script fails, subtract 50 from this node's effective priority
    # server1 base priority is 100 — on failure it drops to 50, losing to server2 (base 90)
    weight -50
    # Number of consecutive failures before declaring the script failed
    fall 2
    # Number of consecutive successes before declaring the script recovered
    rise 2
}

vrrp_instance VI_POSTGRES {
    state BACKUP
    interface enp0s3
    virtual_router_id 51
    # Base priority — must differ between nodes; server1 is preferred primary candidate
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass pg_vip_2026
    }

    virtual_ipaddress {
        192.168.0.180/24
    }

    track_script {
        check_postgres
    }
}
EOF

# On server2
sudo tee /etc/keepalived/keepalived.conf > /dev/null << 'EOF'
global_defs {
    script_user postgres postgres
    enable_script_security
}

vrrp_script check_postgres {
    script "/usr/local/bin/check_postgres_primary.sh"
    interval 2
    # server2 base priority is 90 — on failure it drops to 40
    weight -50
    fall 2
    rise 2
}

vrrp_instance VI_POSTGRES {
    state BACKUP
    interface enp0s3
    virtual_router_id 51
    # Lower base priority than server1 — server1 holds the VIP when both are healthy
    priority 90
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass pg_vip_2026
    }

    virtual_ipaddress {
        192.168.0.180/24
    }

    track_script {
        check_postgres
    }
}
EOF

# On server1
sudo systemctl enable keepalived
sudo systemctl start keepalived
sudo systemctl status keepalived
# Expected: active (running)
# If failed: sudo journalctl -u keepalived -n 30

# On server2
sudo systemctl enable keepalived
sudo systemctl start keepalived
sudo systemctl status keepalived
# Expected: active (running)

# On server1
ip addr show enp0s3 | grep 192.168.0.180
# Expected: inet 192.168.0.180/24 — VIP is present if server1 is the current primary
# If not present and server1 IS the primary: wait 10 seconds and retry

# On server2
ip addr show enp0s3 | grep 192.168.0.180
# Expected: no output — server2 is standby and does not hold the VIP

# On server1
sudo -u postgres bash -c 'echo "192.168.0.180:5432:repmgr:repmgr:repmgr" >> /var/lib/postgresql/.pgpass'
sudo -u postgres bash -c 'echo "192.168.0.180:5432:replication:repmgr:repmgr" >> /var/lib/postgresql/.pgpass'

# On server2
sudo -u postgres bash -c 'echo "192.168.0.180:5432:repmgr:repmgr:repmgr" >> /var/lib/postgresql/.pgpass'
sudo -u postgres bash -c 'echo "192.168.0.180:5432:replication:repmgr:repmgr" >> /var/lib/postgresql/.pgpass'

# On server1 or server2
ping -c 3 192.168.0.180
# Expected: replies from 192.168.0.180

# Connect to PostgreSQL via VIP — must run as postgres OS user to use .pgpass
sudo -u postgres psql -h 192.168.0.180 -U repmgr -d repmgr -c "SELECT pg_is_in_recovery(), inet_server_addr();"
# Expected: f (false) | 192.168.0.180 — connected to the primary through the VIP

# On server1
ip addr show enp0s3 | grep 192.168.0.180
# Record which node holds the VIP before triggering the failover

# On server1
sudo systemctl stop postgresql
# repmgrd on server2 will detect this and promote server2 after ~60 seconds

# On server2
sudo journalctl -u keepalived -f
# Expected sequence:
#   Script check_postgres_primary.sh succeeded — server2 now primary after promotion
#   VRRP_Instance(VI_POSTGRES) Entering MASTER STATE

# On server2
ip addr show enp0s3 | grep 192.168.0.180
# Expected: inet 192.168.0.180/24 — VIP is now on server2

psql -h 192.168.0.180 -U repmgr -d repmgr -c "SELECT pg_is_in_recovery(), inet_server_addr();"
# Expected: f | 192.168.0.182 — VIP now connects to server2, which is the new primary

# On server1 (assuming server1 is currently standby)
sudo -u postgres repmgr standby switchover

# On the demoted node (the old primary)
sudo systemctl start postgresql

# On server1
ip addr show enp0s3 | grep 192.168.0.180
# Expected: inet 192.168.0.180/24 — VIP is back on server1

psql -h 192.168.0.180 -U repmgr -d repmgr -c "SELECT pg_is_in_recovery(), inet_server_addr();"
# Expected: f | 192.168.0.181 — VIP connects to server1, now the primary