🐘 Part 4: Implementing a High-Availability PostgreSQL 17 Cluster with Patroni & Etcd on Debian 13

Welcome to the fourth installment of our “Debian 13 Clustering & Distribution” series. In the previous parts, we focused on setting up our secure APT repository server, building Telephony Server, and packaging Kamailio. Now, we shift our focus to the data layer. In this guide, we will build a self-healing, high-availability PostgreSQL 17 database cluster using Patroni as our manager and Etcd as our consensus store on Debian 13 (Trixie). We will also detail configuring the Linux softdog watchdog device to prevent split-brain conditions.

---

🏗️ Why High-Availability at the Database Layer?

For telephony and administrative backends, a database crash means dropped registrations, billing failures, and portal outages. We require a data layer that guarantees:

1. Zero Data Loss (RPO ≈ 0): Real-time PostgreSQL replication.
2. Automatic Failover (RTO < 30 seconds): Instant promotion of a standby node if the leader crashes.
3. Split-Brain Prevention: Defensive consensus checks to ensure only one node ever acts as leader.

---

🏛️ Cluster Architecture

Our cluster will use 3 Nodes to ensure quorum for consensus votes.

Role	Hostname	IP	Function
Node 1	pg-node-01	192.168.10.31	Initial Leader / Etcd Member 1
Node 2	pg-node-02	192.168.10.32	Standby Replica / Etcd Member 2
Node 3	pg-node-03	192.168.10.33	Standby Replica / Etcd Member 3

---

🛠️ Step 1: Environment Preparation (All 3 Nodes)

Log into each node and complete the base setup.

1.1 Configure Hostnames

Assign hostnames to distinguish nodes in the cluster:

# On pg-node-01
hostnamectl set-hostname pg-node-01

# On pg-node-02
hostnamectl set-hostname pg-node-02

# On pg-node-03
hostnamectl set-hostname pg-node-03

1.2 Local Name Resolution (/etc/hosts)

Add resolution lines to /etc/hosts on all 3 nodes so they can communicate using hostnames:

cat << 'EOF' >> /etc/hosts
192.168.10.34 pg-node-01
192.168.10.35 pg-node-02
192.168.10.36 pg-node-03
EOF

1.3 Install Build Tools and Prerequisites on all 3 nodes

apt update && apt upgrade -y
apt install -y etcd-server etcd-client
apt install -y curl gnupg2 sudo lsb-release vim git \
    python3-pip python3-venv python3-dev python3-etcd \
    patroni libpq-dev

# Stop and disable automatically started default standalone etcd, and clean its database
systemctl stop etcd
systemctl disable etcd
rm -rf /var/lib/etcd/default/*

1.4 Firewall Configuration (nftables)

We must restrict database and consensus traffic to our cluster IPs. Update /etc/nftables.conf on all 3 nodes:

cat <<EOF > /etc/nftables.conf
#!/usr/sbin/nft -f

flush ruleset

table inet filter {
    chain input {
        type filter hook input priority 0; policy drop;

        # Accept loopback traffic
        iif "lo" accept

        # Accept established and related connections
        ct state established,related accept

        # Accept SSH (Port 22)
        tcp dport 22 accept

        # Accept ICMP Ping
        ip protocol icmp accept

        # Cluster Traffic: PostgreSQL (5432), Patroni REST API (8008), Etcd (2379, 2380)
        ip saddr { 192.168.10.34, 192.168.10.35, 192.168.10.36 } tcp dport { 5432, 8008, 2379, 2380 } accept

        # Client Traffic: Allow VoIP subnet to access PostgreSQL (5432) and Patroni REST API (8008)
        ip saddr { 192.168.10.0/24 } tcp dport { 5432, 8008 } accept
    }
    chain forward { type filter hook forward priority 0; policy drop; }
    chain output { type filter hook output priority 0; policy accept; }
}
EOF

systemctl enable --now nftables
nft list ruleset

---

🐘 Step 2: Install PostgreSQL 17 (All Nodes)

Debian 13 (Trixie) includes PostgreSQL 17 in its standard library pools:

apt install -y postgresql postgresql-client

[!WARNING] Disable Default Service: Once installed, the OS helper scripts will launch a standalone PostgreSQL instance automatically. You MUST stop and disable this instance immediately. Patroni must be the sole process managing the lifecycle of the database.

systemctl stop postgresql
systemctl disable postgresql

---

🧠 Step 3: Configure Etcd (All Nodes)

Etcd provides a distributed key-value store to maintain cluster state (“Who is the leader?”).

3.1 Edit Cluster Configs

Configure /etc/default/etcd on each node:

On Node 1 (pg-node-01):

cat << 'EOF' > /etc/default/etcd
ETCD_NAME="etcd01"
ETCD_DATA_DIR="/var/lib/etcd/default"
ETCD_LISTEN_PEER_URLS="http://192.168.10.34:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.10.34:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.10.34:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.10.34:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.10.34:2380,etcd02=http://192.168.10.35:2380,etcd03=http://192.168.10.36:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="ring2all-etcd-cluster"
EOF

On Node 2 (pg-node-02):

cat << 'EOF' > /etc/default/etcd
ETCD_NAME="etcd02"
ETCD_DATA_DIR="/var/lib/etcd/default"
ETCD_LISTEN_PEER_URLS="http://192.168.10.35:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.10.35:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.10.35:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.10.35:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.10.34:2380,etcd02=http://192.168.10.35:2380,etcd03=http://192.168.10.36:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="ring2all-etcd-cluster"
EOF

On Node 3 (pg-node-03):

cat << 'EOF' > /etc/default/etcd
ETCD_NAME="etcd03"
ETCD_DATA_DIR="/var/lib/etcd/default"
ETCD_LISTEN_PEER_URLS="http://192.168.10.36:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.10.36:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.10.36:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.10.36:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.10.34:2380,etcd02=http://192.168.10.35:2380,etcd03=http://192.168.10.36:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="ring2all-etcd-cluster"
EOF

3.2 Start and Verify Etcd

Enable and start the services across all nodes:

systemctl enable --now etcd

Verify that the consensus cluster is healthy and all members are registered:

etcdctl member list
etcdctl endpoint health --cluster

Expected Output:

http://192.168.10.31:2379 is healthy: successfully committed proposal: took = 2.12ms
http://192.168.10.32:2379 is healthy: successfully committed proposal: took = 1.95ms
http://192.168.10.33:2379 is healthy: successfully committed proposal: took = 2.01ms

---

🐕 Step 4: Configure Linux Watchdog (All Nodes)

Patroni can interface with Linux’s kernel watchdog driver (softdog) to enforce fencing. If a leader node hangs or loses connectivity to Etcd, the watchdog timer will expire, forcing the kernel to instantly reboot the server. This prevents the offline leader from writing data if a standby has already been promoted.

Load the watchdog module and configure permissions:

# Load module immediately
modprobe softdog

# Load module on boot
echo "softdog" > /etc/modules-load.d/softdog.conf

# Grant postgres user control of the watchdog interface
echo 'KERNEL=="watchdog", MODE="0660", GROUP="postgres"' > /etc/udev/rules.d/60-watchdog.rules
udevadm control --reload-rules
udevadm trigger

# Verify permissions
ls -l /dev/watchdog
# crw-rw---- 1 root postgres ...

---

🧹 Step 5: Clean Data Directories (All Nodes)

Before configuring Patroni, we must clean all pre-existing database storage directories. Patroni will initialize the database schema and replicate nodes from scratch; existing data will cause the bootstrap phase to crash.

# Verify postgres is stopped
systemctl stop postgresql

# Clean main storage paths
rm -rf /var/lib/postgresql/17/main/*

# Fix permission ownership
chown postgres:postgres /var/lib/postgresql/17/main
chmod 700 /var/lib/postgresql/17/main

---

🤖 Step 6: Install & Configure Patroni (All Nodes)

Install Patroni alongside the Python Etcd3 driver:

apt install -y patroni python3-etcd3

Create /etc/patroni/config.yml on all 3 nodes using the commands below:

On Node 1 (pg-node-01):

cat << 'EOF' > /etc/patroni/config.yml
scope: ring2all-db-cluster
namespace: /db/
name: pg-node-01

restapi:
  listen: 192.168.10.34:8008
  connect_address: 192.168.10.34:8008

etcd3:
  hosts:
    - 192.168.10.34:2379
    - 192.168.10.35:2379
    - 192.168.10.36:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      pg_hba:
        - host replication replicator 192.168.10.0/24 md5
        - host all postgres 192.168.10.0/24 md5
        - host all all 0.0.0.0/0 md5
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 8
        max_wal_senders: 5
        max_replication_slots: 5
        wal_log_hints: "on"
        archive_mode: "on"
        archive_command: mkdir -p /var/lib/postgresql/17/archive && test ! -f /var/lib/postgresql/17/archive/%f && cp %p /var/lib/postgresql/17/archive/%f

  initdb:
  - encoding: UTF8
  - data-checksums

postgresql:
  listen: 192.168.10.34:5432
  connect_address: 192.168.10.34:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: DB_REPLICATION_PASSWORD_SECRET  # <--- Use a secure password!
    superuser:
      username: postgres
      password: DB_ADMIN_PASSWORD_SECRET  # <--- Use a secure password!

  parameters:
    unix_socket_directories: '/var/run/postgresql'

  pg_hba:
    - local all all trust
    - host replication replicator 192.168.10.0/24 md5
    - host all postgres 192.168.10.0/24 md5
    - host all all 0.0.0.0/0 md5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
EOF

On Node 2 (pg-node-02):

cat << 'EOF' > /etc/patroni/config.yml
scope: ring2all-db-cluster
namespace: /db/
name: pg-node-02

restapi:
  listen: 192.168.10.35:8008
  connect_address: 192.168.10.35:8008

etcd3:
  hosts:
    - 192.168.10.34:2379
    - 192.168.10.35:2379
    - 192.168.10.36:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      pg_hba:
        - host replication replicator 192.168.10.0/24 md5
        - host all postgres 192.168.10.0/24 md5
        - host all all 0.0.0.0/0 md5
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 8
        max_wal_senders: 5
        max_replication_slots: 5
        wal_log_hints: "on"
        archive_mode: "on"
        archive_command: mkdir -p /var/lib/postgresql/17/archive && test ! -f /var/lib/postgresql/17/archive/%f && cp %p /var/lib/postgresql/17/archive/%f

  initdb:
  - encoding: UTF8
  - data-checksums

postgresql:
  listen: 192.168.10.35:5432
  connect_address: 192.168.10.35:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: DB_REPLICATION_PASSWORD_SECRET  # <--- Use a secure password!
    superuser:
      username: postgres
      password: DB_ADMIN_PASSWORD_SECRET  # <--- Use a secure password!

  parameters:
    unix_socket_directories: '/var/run/postgresql'

  pg_hba:
    - local all all trust
    - host replication replicator 192.168.10.0/24 md5
    - host all postgres 192.168.10.0/24 md5
    - host all all 0.0.0.0/0 md5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
EOF

On Node 3 (pg-node-03):

cat << 'EOF' > /etc/patroni/config.yml
scope: ring2all-db-cluster
namespace: /db/
name: pg-node-03

restapi:
  listen: 192.168.10.36:8008
  connect_address: 192.168.10.36:8008

etcd3:
  hosts:
    - 192.168.10.34:2379
    - 192.168.10.35:2379
    - 192.168.10.36:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      pg_hba:
        - host replication replicator 192.168.10.0/24 md5
        - host all postgres 192.168.10.0/24 md5
        - host all all 0.0.0.0/0 md5
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 8
        max_wal_senders: 5
        max_replication_slots: 5
        wal_log_hints: "on"
        archive_mode: "on"
        archive_command: mkdir -p /var/lib/postgresql/17/archive && test ! -f /var/lib/postgresql/17/archive/%f && cp %p /var/lib/postgresql/17/archive/%f

  initdb:
  - encoding: UTF8
  - data-checksums

postgresql:
  listen: 192.168.10.36:5432
  connect_address: 192.168.10.36:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: DB_REPLICATION_PASSWORD_SECRET  # <--- Use a secure password!
    superuser:
      username: postgres
      password: DB_ADMIN_PASSWORD_SECRET  # <--- Use a secure password!

  parameters:
    unix_socket_directories: '/var/run/postgresql'

  pg_hba:
    - local all all trust
    - host replication replicator 192.168.10.0/24 md5
    - host all postgres 192.168.10.0/24 md5
    - host all all 0.0.0.0/0 md5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
EOF

---

🚀 Step 7: Launch the Cluster

To avoid split-brain logic races, we must start Patroni sequentially, starting with our intended primary node.

7.1 Start Node 1 (Intended Leader)

systemctl enable --now patroni

Monitor the startup logs:

journalctl -u patroni -f

You should see: Lock owner: pg-node-01; I am the leader.

7.2 Start Nodes 2 and 3 (standby Standbys)

Once pg-node-01 is elected as leader, start the replicas:

systemctl enable --now patroni

Replicas will log: bootstrapping from leader 'pg-node-01' and copy the database binary files over the network using pg_basebackup.

7.3 Verify Cluster Health

Run the Patroni CLI list command to verify replication:

patronictl -c /etc/patroni/config.yml list

Expected Output:

+ Cluster: ring2all-db-cluster (709283084920) ------+ ----+-----------+
| Member     | Host           | Role    | State     | TL  | Lag in MB |
+------------+----------------+---------+-----------+-----+-----------+
| pg-node-01 | 192.168.10.34  | Leader  | running   |   1 |           |
| pg-node-02 | 192.168.10.35  | Replica | streaming |   1 |         0 |
| pg-node-03 | 192.168.10.36  | Replica | streaming |   1 |         0 |
+------------+----------------+---------+-----------+-----+-----------+

---

🗄️ Step 8: Install Ring2All Schema

Now that the clustering framework is online, log into pg-node-01 (the leader) and install the database schema packages from the repository configured in Part 1:

# Install repo certificate and script
wget -qO- https://repo.softswitchone.com/apt/setup_repo | bash
apt-get update

# Install databases and seeds
apt-get install -y softswitch-db

This installer automatically seeds the core databases (ss_admin, ss_telephony, ss_cdr, ss_cc, ss_logs, freeswitch), and saves random security credentials to /etc/softswitch/db-credentials.

---

🔌 Step 9: Client Configuration (HAProxy Sidecar Proxying)

Applications and telephony servers should not connect directly to a single database server’s IP address. If the leader changes, connections will fail. Instead, we deploy HAProxy as a local service (sidecar) on each client server. HAProxy continuously polls the Patroni API endpoints to determine who the current leader is, routing writes to port 5000 and read-only traffic to port 5001.

9.1 Install HAProxy on the Client Node

Run this command on your application or telephony server:

apt install -y haproxy
systemctl enable haproxy

9.2 Configure HAProxy

Edit /etc/haproxy/haproxy.cfg and add the following configuration:

global
    log /dev/log    local0
    log /dev/log    local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

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

# --- HAProxy Stats Dashboard (Port 7000) ---
listen stats
    bind *:7000
    mode http
    stats enable
    stats uri /
    stats refresh 5s
    stats auth admin:SECURE_STATS_PASSWORD  # <--- Change this password!

# --- PostgreSQL Write Pool (Port 5000) ---
# Routes exclusively to the active master
frontend postgres_write
    bind *:5000
    default_backend backend_postgres_write

backend backend_postgres_write
    option httpchk GET /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server pg-node-01 192.168.10.31:5432 maxconn 100 check port 8008
    server pg-node-02 192.168.10.32:5432 maxconn 100 check port 8008
    server pg-node-03 192.168.10.33:5432 maxconn 100 check port 8008

# --- PostgreSQL Read Pool (Port 5001) ---
# Balances read queries across standby replicas
frontend postgres_read
    bind *:5001
    default_backend backend_postgres_read

backend backend_postgres_read
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

    server pg-node-01 192.168.10.31:5432 maxconn 100 check port 8008
    server pg-node-02 192.168.10.32:5432 maxconn 100 check port 8008
    server pg-node-03 192.168.10.33:5432 maxconn 100 check port 8008

Verify and reload HAProxy:

# Validate syntax
haproxy -c -f /etc/haproxy/haproxy.cfg

# Restart service
systemctl restart haproxy

9.3 Verify Client Connections

On the client machine, test connectivity through your local HAProxy ports:

# Test write connection (should point to Leader and show pg_is_in_recovery as false/f)
psql -h 127.0.0.1 -p 5000 -U ss_db_user -d ss_admin -c "SELECT inet_server_addr(), pg_is_in_recovery();"

# Test read connection (should point to Standby and show pg_is_in_recovery as true/t)
psql -h 127.0.0.1 -p 5001 -U ss_db_user -d ss_admin -c "SELECT inet_server_addr(), pg_is_in_recovery();"

Configure your application services (like softswitch-api or Telephony Server ODBC configuration) to connect to 127.0.0.1:5000.

---

⏭️ What’s Next?

Our data layer is now active-active, secure, and self-healing. In Part 5: Distributed Storage with GlusterFS on Debian 13, we will configure high-availability file systems to store call recordings and voicemail assets across cluster nodes.