PostgreSQL 16 & Patroni: Alta Disponibilidad con Consenso etcd

En entornos transaccionales donde la integridad de datos bancaria y el cumplimiento normativo son de tolerancia cero, Mañuti implementa un clúster de PostgreSQL 16 orquestado por Patroni 3.x y respaldado por una capa de consenso distribuido (DCS) basada en etcd v3.

A diferencia de los esquemas multi-maestro síncronos, esta arquitectura opera bajo el modelo Líder Único con Réplicas Físicas por Streaming, eliminando colisiones de certificación en escrituras y automatizando el failover transparente mediante HAProxy con comprobaciones de estado HTTP de nivel 7.

flowchart TD
    LB["HAProxy Load Balancer<br/>(192.168.1.169)"]
    
    LEADER[("PostgreSQL Líder Activo<br/>(192.168.1.170)")]
    
    REP1[("PostgreSQL Réplica<br/>(192.168.1.171)")]
    REP2[("PostgreSQL Réplica<br/>(192.168.1.172)")]
    
    DCS["Malla DCS de Consenso etcd v3 (2379 / 2380)<br/>Detección de fallo, elección de quórum y guarda Anti-Split-Brain"]

    LB -->|"HTTP Health Check (Port 8008 == 200 OK)<br/>SQL Traffic (Port 5432)"| LEADER
    
    LEADER -->|"Streaming Replication (WAL)"| REP1
    LEADER -->|"Streaming Replication (WAL)"| REP2
    
    DCS --> REP1
    DCS --> REP2
    
    %% Opcionalmente, si etcd también monitorea al líder (como es habitual en Patroni/etcd), 
    %% el diagrama implícitamente lo envuelve, pero conectamos explícitamente según tu esquema.

1. Topología de Red y Roles del Clúster

2. Hardening de Red y Firewall Quirúrgico (UFW)

Para evitar desincronizaciones de estado, el acceso al puerto 5432 queda completamente cerrado para el tráfico exterior en los nodos de base de datos; únicamente el balanceador y los miembros de replicación pueden comunicarse con ellos.

2.1 En el Balanceador (Node-LB - 192.168.1.169)

sudo ufw --force reset
sudo ufw default deny incoming
sudo ufw default allow outgoing

# SSH Administrativo
sudo ufw allow 22/tcp

# Punto de entrada único para la aplicación Mañuti y Navicat
sudo ufw allow from 192.168.1.111 to any port 5432 proto tcp comment 'Navicat Management'
sudo ufw allow from 192.168.1.147 to any port 5432 proto tcp comment 'App Worker 01'
sudo ufw allow from 192.168.1.148 to any port 5432 proto tcp comment 'App Worker 02'
sudo ufw allow from 192.168.1.149 to any port 5432 proto tcp comment 'App Worker 03'

sudo ufw enable

2.2 En los Nodos de Datos (Node-DB1, Node-DB2, Node-DB3)

Ejecutar en los tres servidores (.170, .171 y .172):

sudo ufw --force reset
sudo ufw default deny incoming
sudo ufw default allow outgoing

sudo ufw allow 22/tcp

# Permitir exclusivamente al Balanceador enviar tráfico y verificar salud
sudo ufw allow from 192.168.1.169 to any port 5432 proto tcp comment 'Consultas SQL desde LB'
sudo ufw allow from 192.168.1.169 to any port 8008 proto tcp comment 'Health Checks Patroni desde LB'

# Malla de Consenso (etcd) y Replicación entre los 3 nodos
sudo ufw allow from 192.168.1.170 to any port 2379,2380,5432,8008 proto tcp
sudo ufw allow from 192.168.1.171 to any port 2379,2380,5432,8008 proto tcp
sudo ufw allow from 192.168.1.172 to any port 2379,2380,5432,8008 proto tcp

sudo ufw enable

3. Preparación de Kernel y Sistema (Todos los Nodos de Base de Datos)

3.1 Ajuste de Memoria Compartida en el Kernel (/etc/sysctl.conf)

PostgreSQL 16 y Patroni utilizan memoria compartida dinámica (mmap). Se incrementan los topes del kernel para evitar errores en transiciones rápidas de failover:

sudo sysctl -w kernel.shmmax=17179869184
sudo sysctl -w kernel.shmall=4194304
echo "kernel.shmmax=17179869184" | sudo tee -a /etc/sysctl.conf
echo "kernel.shmall=4194304" | sudo tee -a /etc/sysctl.conf

3.2 Generación de Locale Estándar

Evita fallos de inicialización con initdb:

sudo locale-gen en_US.UTF-8
sudo update-locale LANG=en_US.UTF-8

4. Instalación de Dependencias, PostgreSQL 16 y Patroni

4.1 Repositorio PGDG y Paquetes Base (Ejecutar en .170, .171 y .172)

sudo apt update && sudo apt upgrade -y
sudo apt install -y curl ca-certificates gnupg software-properties-common python3-pip python3-dev

# Repositorio PGDG Oficial
sudo install -d /usr/share/postgresql-common/pgdg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

sudo apt update
sudo apt install -y postgresql-16

4.2 Deshabilitar PostgreSQL Nativo

Crítico: PostgreSQL no debe arrancar de forma autónoma con el sistema; Patroni debe gestionar su ciclo de vida y sockets.

sudo systemctl stop postgresql
sudo systemctl disable postgresql

4.3 Instalación de Patroni con Soporte etcd3 y Adaptador Psycopg2

En Ubuntu 24.04 (Python 3.12), se debe instalar explícitamente el paquete de soporte binario y el bundle etcd3:

sudo pip install patroni[etcd3] psycopg2-binary --break-system-packages

5. Despliegue de la Capa de Consenso Distribuido (etcd v3)

Dado que el paquete etcd no está empaquetado nativamente en los repositorios oficiales de Ubuntu 24.04, se instalan directamente los binarios oficiales compilados.

5.1 Instalación de Binarios de etcd (En los 3 Nodos)

curl -L https://github.com/etcd-io/etcd/releases/download/v3.5.12/etcd-v3.5.12-linux-amd64.tar.gz -o etcd.tar.gz
tar xzvf etcd.tar.gz
sudo mv etcd-v3.5.12-linux-amd64/etcd* /usr/local/bin/
rm -rf etcd*

# Estructura de persistencia
sudo mkdir -p /var/lib/etcd /etc/etcd
sudo chown -R postgres:postgres /var/lib/etcd /etc/etcd

5.2 Unidad de Systemd para etcd (/etc/systemd/system/etcd.service)

[Unit]
Description=etcd - highly-available key value store
Documentation=https://github.com/coreos/etcd
After=network.target

[Service]
Type=notify
User=postgres
EnvironmentFile=/etc/etcd/etcd.conf
ExecStart=/usr/local/bin/etcd
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

5.3 Archivo de Variables de etcd (/etc/etcd/etcd.conf)

Crear el archivo en cada nodo adaptando la variable ETCD_NAME e IPs correspondientes:

En Node-DB1 (192.168.1.170):

sudo tee /etc/etcd/etcd.conf <<EOF
ETCD_NAME="node_170"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="manuti-etcd-pg-cluster"
ETCD_INITIAL_CLUSTER="node_170=http://192.168.1.170:2380,node_171=http://192.168.1.171:2380,node_172=http://192.168.1.172:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.170:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.170:2379"
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
EOF

(En Node-DB2 sustituir 170 por 171; en Node-DB3 sustituir por 172).

5.4 Inicialización y Validación de Salud de etcd

sudo systemctl daemon-reload
sudo systemctl enable --now etcd

# Verificar que los 3 miembros establecieron quórum
export ETCDCTL_API=3
etcdctl endpoint health --cluster --endpoints=http://192.168.1.170:2379,http://192.168.1.171:2379,http://192.168.1.172:2379

Salida esperada:

http://192.168.1.170:2379 is healthy: successfully committed proposal: took = 4.2ms
http://192.168.1.171:2379 is healthy: successfully committed proposal: took = 5.1ms
http://192.168.1.172:2379 is healthy: successfully committed proposal: took = 4.8ms

6. Configuración de Patroni

6.1 Estructura y Permisos

sudo mkdir -p /etc/patroni /var/lib/postgresql/16/main /var/run/postgresql
sudo chown -R postgres:postgres /etc/patroni /var/lib/postgresql /var/run/postgresql
sudo chmod 700 /var/lib/postgresql/16/main

6.2 Archivo de Configuración /etc/patroni/patroni.yml

Generar el archivo con credenciales estándar seguras para el clúster.

En Node-DB1 (192.168.1.170):

scope: manuti_pg_cluster
namespace: /service/
name: pg_node_170

etcd3:
  hosts:
  - 192.168.1.170:2379
  - 192.168.1.171:2379
  - 192.168.1.172:2379

bootstrap:
  method: initdb
  initdb:
  - auth-host: md5
  - auth-local: trust
  - encoding: UTF8
  - locale: en_US.UTF-8
  - data-checksums
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: logical
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby: "on"

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.170:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  authentication:
    replication:
      username: replicator
      password: ReplClusterSecurePass_2025!
    superuser:
      username: postgres
      password: MasterSuperAdminPass_2025!
  pg_hba:
    - host replication replicator 192.168.1.0/24 md5
    - host all all 0.0.0.0/0 md5
  parameters:
    unix_socket_directories: '/var/run/postgresql'
    dynamic_shared_memory_type: mmap

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.170:8008

(En los archivos de Node-DB2 y Node-DB3, cambiar name a pg_node_171 / pg_node_172 y las directivas connect_address a su IP respectiva).

6.3 Servicio Systemd (/etc/systemd/system/patroni.service)

[Unit]
Description=Patroni for PostgreSQL High Availability
After=network.target etcd.service
Requires=etcd.service

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl enable patroni

7. Secuencia de Arranque del Clúster (Bootstrap)

Regla de Bootstrap: Iniciar primero el Nodo 170 en solitario. Patroni detectará que la clave en etcd está vacía y ejecutará initdb, convirtiéndose en Leader. Solo después se inician los nodos 171 y 172 para clonar datos con pg_basebackup.

# 1. En Node-DB1 (192.168.1.170):
sudo systemctl start patroni

# Verificar que es el Líder activo:
patronictl -c /etc/patroni/patroni.yml list

Una vez que el nodo 170 aparezca en estado Leader | running:

# 2. En Node-DB2 (192.168.1.171):
sudo systemctl start patroni

# 3. En Node-DB3 (192.168.1.172):
sudo systemctl start patroni

Salida de Validación Final del Clúster (patronictl list):

+ Cluster: manuti_pg_cluster (74129482910) -------+----+-----------+
| Member      | Host          | Role    | State     | TL | Lag in MB |
+-------------+---------------+---------+-----------+----+-----------+
| pg_node_170 | 192.168.1.170 | Leader  | running   |  1 |           |
| pg_node_171 | 192.168.1.171 | Replica | streaming |  1 |         0 |
| pg_node_172 | 192.168.1.172 | Replica | streaming |  1 |         0 |
+-------------+---------------+---------+-----------+----+-----------+

8. Configuración del Balanceador HAProxy (Node-LB - 192.168.1.169)

HAProxy interroga la API REST de Patroni en el puerto 8008. La API devuelve código HTTP 200 OK únicamente al nodo que ostenta el rol de Líder actual; a las réplicas devuelve 503 Service Unavailable, garantizando que todas las conexiones de escritura lleguen al nodo primario.

8.1 Configuración (/etc/haproxy/haproxy.cfg)

global
    log /dev/log local0
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode tcp
    option tcplog
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

# Pool de Conexión de Datos (Mañuti Core App)
listen postgresql_cluster
    bind 192.168.1.169:5432
    mode tcp
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    
    server pg_node_170 192.168.1.170:5432 check port 8008
    server pg_node_171 192.168.1.171:5432 check port 8008
    server pg_node_172 192.168.1.172:5432 check port 8008

sudo systemctl restart haproxy

9. Troubleshooting Avanzado y Continuidad Operativa

9.1 Divergencia de Timeline (TL 1 vs TL 2)

Ocurre tras una desconexión abrupta o un failover incompleto. Si una réplica no logra entrar a streaming porque su Timeline es inferior al del líder:

# Forzar la re-sincronización y clonación de basebackup desde el líder
patronictl -c /etc/patroni/patroni.yml reinit manuti_pg_cluster pg_node_171

9.2 Error Crítico: FATAL: could not open shared memory segment

Este error se manifiesta cuando procesos huérfanos de PostgreSQL retienen semáforos o identificadores de memoria compartida en /dev/shm tras reinicios violentos o cambios de TL.

Protocolo de Saneamiento y Resurrección: Ejecutar en orden: réplica degradada, réplica sana y finalmente en el líder:

# 1. Detener el agente Patroni
sudo systemctl stop patroni

# 2. Purgar procesos zombis de PostgreSQL
sudo pkill -9 -u postgres postgres 2>/dev/null

# 3. Eliminar segmentos de memoria y sockets retenidos
sudo rm -f /dev/shm/PostgreSQL.*
sudo rm -f /tmp/.s.PGSQL.*
sudo rm -f /var/lib/postgresql/16/main/postmaster.pid

# 4. Liberar semáforos del kernel bloqueados
ipcs -s | grep postgres | awk '{print $2}' | xargs -n 1 sudo ipcrm -s 2>/dev/null

# 5. Reiniciar Patroni
sudo systemctl start patroni

9.3 Switchover Manual Controlado (Mantenimiento sin Downtime)

Para transferir el liderazgo del nodo 170 al nodo 171 de forma transparente:

patronictl -c /etc/patroni/patroni.yml switchover manuti_pg_cluster \
  --leader pg_node_170 --candidate pg_node_171 --scheduled now

10. Integración en el Aplicativo Mañuti (.env y Database Config)

Laravel se conecta exclusivamente a la IP del balanceador HAProxy:

DB_CONNECTION=pgsql
DB_HOST=192.168.1.169
DB_PORT=5432
DB_DATABASE=manuti_core
DB_USERNAME=postgres
DB_PASSWORD=MasterSuperAdminPass_2025!

Para asegurar que las desconexiones durante un failover de 2-3 segundos se manejen sin excepciones fatales en los workers de Horizon / Octane:

// config/database.php
'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '192.168.1.169'),
    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE', 'manuti_core'),
    'username' => env('DB_USERNAME', 'postgres'),
    'password' => env('DB_PASSWORD', 'MasterSuperAdminPass_2025!'),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
    'options' => [
        PDO::ATTR_TIMEOUT => 3,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ],
],