Manual to add a VIP IP in Patroni for PostgreSQL in High Availability

Introduction

This manual is part of our series of manuals dedicated to Installation and Configuration of PostgreSQL in High Availability (HA) using Patroni and etcd. In this specific document, we will explain how to add a VIP IP (Virtual IP) to a Patroni cluster, which will allow a floating IP address to be associated with the leader node of the PostgreSQL cluster, improving the high availability and resiliency of the service.

In a high availability environment, especially in database solutions like PostgreSQL, the ability to ensure that the leader node's IP is accessible from clients is essential. Using a VIP IP in a high availability system allows the IP associated with the active node to change dynamically in the event of a failover, ensuring that clients can always connect to the node that is providing the service.

Objective of the Manual

The purpose of this manual is to guide you through the necessary steps to configure and manage a VIP IP in a PostgreSQL cluster managed by Patroni. We will cover creating a script to manage VIP assignment and deletion, as well as configuring the permissions necessary for this script to function correctly. It will also cover how to integrate this process with the PostgreSQL and Patroni configuration, so that the failover works transparently.

Prerequisites

Before you begin, make sure you have the following configured:

• A cluster of PostgreSQL running with Patroni and etcd.
• Administrative access to the server where the cluster is running.
• Installed tools like curl, jq, ip, and bash.
• Basic network configuration that allows the assignment of IPs on the necessary network interfaces.

If you have not yet configured Patroni with PostgreSQL, we recommend following the PostgreSQL Installation Manual in High Availability with Patroni and etcd available at the following link: PostgreSQL Installation Manual in High Availability with Patroni and etcd.

Creation of the Script to Manage the VIP IP

The first step in this process is to create a script that will be executed every time the node changes roles (from leader to replica and vice versa). This script will manage the assignment and removal of the VIP IP on the correct node.

Create the VIP script

In this step, we will create a script called vip.sh which will be responsible for adding or removing the VIP IP from the network interface based on the node's role in the cluster. Open a text editor on the PostgreSQL server and create the file:

sudo vi /etc/patroni/vip.sh

Once in the editor, add the following content to the script:

#!/bin/bash

VIP="192.168.1.220"  
IFACE="eth0"

# Consultar el estado de Patroni en el nodo actual
ROLE=$(curl -s http://$(hostname):8008/patroni | jq -r .role)

# Función para verificar si la IP existe en la interfaz
check_ip_exists() {
    ip addr show dev $1 | grep -q "$2"
}

# Verificar si el nodo es el master
if [[ "$ROLE" == "master" ]]; then
    echo "$(date) - Este nodo es ahora el líder. Agregando VIP $VIP a $IFACE" >> /var/log/patroni_vip.log
    ip addr add $VIP/24 dev $IFACE || echo "Error al agregar la VIP" >> /var/log/patroni_vip.log
else
    # Verificar si la IP ya existe en la interfaz antes de eliminarla
    if check_ip_exists $IFACE $VIP; then
        echo "$(date) - Este nodo es ahora una réplica. Eliminando VIP $VIP de $IFACE" >> /var/log/patroni_vip.log
        ip addr del $VIP/24 dev $IFACE || echo "Error al eliminar la VIP" >> /var/log/patroni_vip.log
    else
        echo "$(date) - La VIP $VIP no está configurada en $IFACE. No se eliminará." >> /var/log/patroni_vip.log
    fi
fi

This script performs the following actions:

1. Query the current role of the node: Use curl to obtain the Patroni state on the node, determining whether the node is the leader (master) or a replica.
2. Check and configure the VIP IP: If the node is the leader, add the VIP to the configured interface (eth0). If the node is a replica, remove the VIP if present.

Set script permissions

Once you've created the file, you need to set the correct permissions to ensure that the script is executable and accessible to the appropriate users. We achieve this with the following commands:

sudo chown etcd:db-etcd /etc/patroni/vip.sh
sudo chmod +x /etc/patroni/vip.sh

This ensures that the script is owned by the users etcd and db-etcd, and is executable by the system.

Log Configuration

In order to keep track of the changes made by the script, we are going to create a log file. This file will record the actions taken when adding or removing the VIP IP on the system.

Create the log file

Create the log file with the following commands:

sudo touch /var/log/patroni_vip.log
sudo chown etcd:db-etcd /var/log/patroni_vip.log
sudo chmod 775 /var/log/patroni_vip.log

This creates the log file and sets the necessary permissions for the script to write to it.

Integration with Patroni

Once the script is ready, we need to configure it within Patroni to run automatically when the node role changes. This is achieved by setting the parameter on_role_change in the PostgreSQL configuration section in Patroni.

Step 4: Modify Patroni Settings

Open the Patroni configuration file (/etc/patroni/patroni.yml) and add the following line under the section callbacks:

callbacks: 
  on_role_change: "/etc/patroni/vip.sh"

Complete configuration file

scope: keycloak-cluster
namespace: /service/
name: srvlropsql01

restapi:
  listen: 192.168.1.214:8008
  connect_address: 192.168.1.214:8008
  authentication:
    username: admin
    password: admin_password

etcd:
  hosts: 192.168.1.214:2379,192.168.1.215:2379,192.168.1.216:2379
  protocol: https
  cacert: /etc/etcd/etcd-ca.crt
  cert: /etc/etcd/server.crt
  key: /etc/etcd/server.key

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      callbacks: 
        on_role_change: "/etc/patroni/vip.sh"
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 256MB
  initdb:
    - encoding: UTF8
    - data-checksums
  users:
    replication:
      password: replicator_password
      options:
        - replication
    admin:
      password: admin_password
      options:
        - createrole
        - createdb
  post_init: 
    - createuser --superuser admin

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.214:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    archive_mode: "on"
    archive-command: 'pgbackrest --stanza=postgres archive-push %f'
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    wal_keep_size: 256MB
  pg_hba:
    - local all postgres trust
    - hostssl replication replicator 127.0.0.1/32 trust
    - hostssl replication replicator 192.168.1.215/32 trust
    - hostssl replication replicator 192.168.1.216/32 trust
    - hostssl replication replicator 192.168.1.214/32 trust
    - host replication replicator 127.0.0.1/32 trust
    - host replication replicator 192.168.1.215/32 trust
    - host replication replicator 192.168.1.216/32 trust
    - host replication replicator 192.168.1.214/32 trust
    - hostssl all all 192.168.1.0/24 trust
    - host all all 192.168.1.0/24 trust
    - hostssl all pgbackrest 192.168.1.0/24 trust
    - host all pgbackrest 192.168.1.0/24 trust
    - hostssl all all 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5
tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

This ensures that the script vip.sh run automatically every time Patroni detects a role change on the node.

Restart Patroni

With everything configured, it is necessary to restart the Patroni service to apply the changes:

Configuration Verification

To verify that the VIP IP has been assigned correctly, you can run the following command on the leader node (master):

ip a

You should see the VIP IP (192.168.1.220) associated with the interface eth0.

Output

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 08:00:27:8d:c0:4d brd ff:ff:ff:ff:ff:ff
    altname enp0s3
    inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic eth0
       valid_lft 84152sec preferred_lft 84152sec
    inet 192.168.1.220/24 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe8d:c04d/64 scope link 
       valid_lft forever preferred_lft forever

Conclusion

With this manual, we have configured a script in Patroni to manage a VIP IP in a PostgreSQL cluster in high availability. This step is crucial to ensure that the PostgreSQL cluster is accessible even after a failover, since the VIP IP always points to the node that is acting as the leader at all times. This improves the availability and resilience of the database service.

This guide is part of our larger series on PostgreSQL in high availability, and if you want more details on configuring Patroni or managing PostgreSQL, you can consult our other guides.

:wq!