Vengala Vinay

Having 12+ Years of Experience in Software Development

Home » Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and Ruby Deployments on OVH

Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and Ruby Deployments on OVH

Establishing a High-Availability PostgreSQL Cluster with Patroni

For robust disaster recovery and automated failover in a PostgreSQL deployment, a distributed consensus system is paramount. Patroni, a template for PostgreSQL HA, leverages etcd, Consul, or ZooKeeper for leader election and configuration management. This section details setting up a Patroni cluster using etcd on OVH cloud instances.

We’ll assume three PostgreSQL nodes (e.g., `pg-node-1`, `pg-node-2`, `pg-node-3`) and at least one etcd node (e.g., `etcd-node-1`). For simplicity, etcd can run on the same nodes as PostgreSQL, but a dedicated etcd cluster is recommended for production environments.

Prerequisites: etcd Installation and Configuration

First, ensure etcd is installed and accessible. On each etcd node:

Installing etcd (Ubuntu/Debian)

Download the latest etcd release binary or use a package manager if available. For this example, we’ll use direct binary download.

wget -q --show-progress https://github.com/etcd-io/etcd/releases/download/v3.5.9/etcd-v3.5.9-linux-amd64.tar.gz
tar xzf etcd-v3.5.9-linux-amd64.tar.gz
sudo mv etcd-v3.5.9-linux-amd64/etcd* /usr/local/bin/
rm -rf etcd-v3.5.9-linux-amd64*

Configuring etcd Cluster

Create an etcd systemd service file (e.g., `/etc/systemd/system/etcd.service`). Adjust `INITIAL_CLUSTER` and `ETCD_ADVERTISE_CLIENT_URLS` based on your node IPs.

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

[Service]
User=etcd
Type=simple
ExecStart=/usr/local/bin/etcd \
  --name=etcd-node-1 \
  --data-dir=/var/lib/etcd \
  --listen-client-urls http://0.0.0.0:2379,http://0.0.0.0:4001 \
  --advertise-client-urls http://<NODE_IP>:2379 \
  --listen-peer-urls http://0.0.0.0:2380 \
  --initial-advertise-peer-urls http://<NODE_IP>:2380 \
  --initial-cluster etcd-node-1=http://<NODE_IP_1>:2380,etcd-node-2=http://<NODE_IP_2>:2380,etcd-node-3=http://<NODE_IP_3>:2380 \
  --initial-cluster-token etcd-cluster-1 \
  --initial-cluster-state new

[Install]
WantedBy=multi-user.target

Replace `` with the actual IP of the etcd node and ``, ``, `` with the IPs of all etcd nodes in the cluster. Ensure the `etcd` user and data directory exist and have correct permissions.

sudo useradd -s /sbin/nologin -d /var/lib/etcd etcd
sudo mkdir -p /var/lib/etcd
sudo chown etcd:etcd /var/lib/etcd
sudo systemctl daemon-reload
sudo systemctl enable etcd
sudo systemctl start etcd

Verify the etcd cluster health:

ETCDCTL_API=3 etcdctl member list
ETCDCTL_API=3 etcdctl endpoint health

Patroni Installation and Configuration

Install Patroni and its dependencies. Python 3 and pip are typically required.

sudo apt update
sudo apt install -y python3-pip python3-dev build-essential libpq-dev
sudo pip3 install --upgrade pip
sudo pip3 install "patroni[etcd]" psycopg2-binary

Patroni Configuration File

Create a Patroni configuration file (e.g., `/etc/patroni/patroni.yml`) on each PostgreSQL node. This configuration points to the etcd cluster and defines PostgreSQL settings.

# /etc/patroni/patroni.yml
scope: my_pg_cluster
namespace: /service/

# etcd configuration
etcd:
  host: &etcd_host <ETCD_NODE_IP>:2379  # Replace with your etcd node IP
  protocol: http

# PostgreSQL configuration
postgresql:
  listen: 0.0.0.0:5432
  connect_address: <POSTGRES_NODE_IP>:5432 # Replace with this node's IP
  data_dir: /var/lib/postgresql/14/main # Adjust version as needed
  pg_hba:
    - host    all   all   0.0.0.0/0   md5
  parameters:
    max_connections: 100
    shared_buffers: 256MB
    wal_level: replica
    hot_standby: "on"
    max_wal_senders: 10
    max_replication_slots: 10

# Replication configuration
replication:
  username: replicator
  password: <REPLICATION_PASSWORD> # Set a strong password
  network:
    - 0.0.0.0/0

# Tags for node identification
tags:
  nofailover: false
  clonefrom: false

# REST API configuration
restapi:
  listen: 0.0.0.0:8008
  connect_address: <POSTGRES_NODE_IP>:8008 # Replace with this node's IP

# Logging
log:
  level: INFO
  dir: /var/log/patroni
  file: patroni.log

Key points:

  • scope: A unique name for your PostgreSQL cluster.
  • namespace: The etcd path prefix for cluster state.
  • etcd.host: The IP address and port of your etcd node(s).
  • postgresql.connect_address: The IP address this node will advertise for PostgreSQL connections.
  • postgresql.data_dir: The PostgreSQL data directory. Ensure it exists and is owned by the PostgreSQL user.
  • replication.password: A strong password for replication users.
  • restapi.connect_address: The IP address this node will advertise for its REST API.

PostgreSQL User and Replication Setup

Before starting Patroni, ensure the PostgreSQL user for replication exists and has the necessary privileges. This is typically done on the initial primary node.

-- Connect to PostgreSQL as a superuser
CREATE USER replicator WITH REPLICATION PASSWORD '<REPLICATION_PASSWORD>';
ALTER USER replicator WITH LOGIN; -- Optional, if you need to connect as replicator
GRANT CONNECT ON DATABASE template1 TO replicator; -- Or a specific database

Ensure the PostgreSQL data directory is correctly set up and owned by the `postgres` user. Patroni can initialize the cluster if the data directory is empty.

Running Patroni as a Service

Create a systemd service file for Patroni (e.g., `/etc/systemd/system/patroni.service`).

[Unit]
Description=Patroni PostgreSQL HA
After=network.target

[Service]
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target

Start and enable the Patroni service on all PostgreSQL nodes:

sudo systemctl daemon-reload
sudo systemctl enable patroni
sudo systemctl start patroni

Initial Cluster Bootstrap

Patroni will automatically elect a leader on the first start if the data directory is empty and no cluster state exists in etcd. Ensure only one node attempts to bootstrap initially. You can achieve this by starting services sequentially or by manually creating an initial leader entry in etcd.

To manually bootstrap (if automatic fails or for specific control):

# On the intended initial primary node, after starting Patroni service:
# Check logs for errors. If it's trying to connect to an existing cluster and failing,
# you might need to clear etcd or ensure no prior state exists.
# If it successfully initializes, you'll see logs indicating it became leader.

Verify cluster status via Patroni’s REST API:

curl http://<POSTGRES_NODE_IP>:8008/cluster

The output should show the cluster state, including the leader and any replicas.

Integrating Ruby Applications with Auto-Failover PostgreSQL

Connecting a Ruby application to a Patroni-managed PostgreSQL cluster requires a strategy that abstracts away the primary node’s IP address. This ensures that when a failover occurs, the application can seamlessly connect to the new primary without manual intervention.

Connection Pooling and Load Balancing

The most robust approach involves using a connection pooler that can dynamically discover the PostgreSQL primary. PgBouncer is a popular choice, but it doesn’t natively support dynamic discovery of Patroni leaders. A more direct method for Ruby applications is to leverage a service that provides a stable endpoint for the current primary.

Option 1: DNS-based Service Discovery (e.g., Consul DNS)

If you are using Consul for service discovery, you can register your PostgreSQL cluster with Consul. Patroni can be configured to update Consul with the primary’s IP. Your Ruby application can then connect to a DNS name (e.g., `postgres-primary.service.consul`) which resolves to the current primary’s IP.

Patroni configuration for Consul integration:

# In patroni.yml
consul:
  host: <CONSUL_HOST>:8500 # Replace with your Consul agent IP/port
  register_as: <POSTGRES_NODE_IP>:5432 # This node's IP and PG port
  tag: postgresql
  name: my_pg_cluster # Service name in Consul
  deregister_critical_service_after: 10m
  checks:
    - name: "PostgreSQL health check"
      tcp: <POSTGRES_NODE_IP>:5432
      interval: 10s
      timeout: 5s

Your Ruby application’s database configuration (e.g., in `database.yml` for Rails) would then use this Consul-resolved DNS name:

# config/database.yml (Rails example)
production:
  adapter: postgresql
  encoding: unicode
  database: myapp_production
  pool: 5
  host: postgres-primary.service.consul # Consul DNS name
  port: 5432
  username: app_user
  password: <APP_USER_PASSWORD>

Option 2: HAProxy as a Smart Proxy

HAProxy can be configured to act as a highly available proxy for PostgreSQL. Patroni can update HAProxy’s configuration when a leader changes. This is a common and effective pattern.

First, set up HAProxy. Install HAProxy on a dedicated server or on one of your application servers.

sudo apt update
sudo apt install -y haproxy

Configure HAProxy (e.g., `/etc/haproxy/haproxy.cfg`):

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

listen pgsql_cluster
    bind *:5000 # HAProxy listens on this port
    mode tcp
    option tcp-check
    balance roundrobin
    # Backend servers will be dynamically added/removed by Patroni
    # Example static entry (will be managed by Patroni script)
    # server pg-node-1 <PG_NODE_1_IP>:5432 check port 5432 inter 2s fall 3 rise 2
    # server pg-node-2 <PG_NODE_2_IP>:5432 check port 5432 inter 2s fall 3 rise 2
    # server pg-node-3 <PG_NODE_3_IP>:5432 check port 5432 inter 2s fall 3 rise 2

Patroni needs to be configured to update HAProxy. This is typically done via a script that Patroni calls on leader changes. Patroni’s `callbacks` section in `patroni.yml` is used for this.

# In patroni.yml
callbacks:
  on_role_change: /usr/local/bin/patroni_haproxy_updater.sh

Create the `patroni_haproxy_updater.sh` script (e.g., `/usr/local/bin/patroni_haproxy_updater.sh`):

#!/bin/bash

# Configuration
HAPROXY_CONFIG="/etc/haproxy/haproxy.cfg"
HAPROXY_SERVICE="haproxy"
PG_PORT="5432"
PG_LISTEN_PORT="5000" # HAProxy listen port
ETCD_HOST="<ETCD_NODE_IP>:2379" # Your etcd host

# Get cluster state from Patroni API
PATRONI_API_URL="http://localhost:8008/cluster"
LEADER_IP=$(curl -s $PATRONI_API_URL | jq -r '.leader' | cut -d: -f1)

# Get all node IPs from etcd (or Patroni API)
# This is a simplified example; a more robust script would query Patroni API for all members
# or use etcd directly to get all registered PostgreSQL nodes.
# For simplicity, we'll assume a static list of potential backend IPs.
# In a real scenario, query Patroni's /cluster endpoint for all members.
ALL_NODES=$(curl -s $PATRONI_API_URL | jq -r '.members[] | .host' | sed "s/:.*//")

# Function to update HAProxy config
update_haproxy() {
    # Backup original config
    cp $HAPROXY_CONFIG $HAPROXY_CONFIG.$(date +%Y%m%d%H%M%S).bak

    # Remove existing backend servers for pgsql_cluster
    sed -i "/listen pgsql_cluster/,/$/{/server pg-/d}" $HAPROXY_CONFIG

    # Add new backend servers
    for NODE_IP in $ALL_NODES; do
        # Check if the node is the current leader
        if [ "$NODE_IP" == "$LEADER_IP" ]; then
            # Add leader as active server
            echo "server pg-node-$NODE_IP $NODE_IP:$PG_PORT check port $PG_PORT inter 2s fall 3 rise 2" >> $HAPROXY_CONFIG
        else
            # Add replicas as backup servers (optional, for read replicas)
            # Or simply don't add them if HAProxy is only for primary failover
            echo "server pg-node-$NODE_IP $NODE_IP:$PG_PORT backup check port $PG_PORT inter 2s fall 3 rise 2" >> $HAPROXY_CONFIG
        fi
    done

    # Reload HAProxy
    systemctl reload $HAPROXY_SERVICE
}

# Execute update
update_haproxy

exit 0

Make the script executable and ensure the `postgres` user (or the user Patroni runs as) can execute it and has permissions to reload HAProxy.

sudo chmod +x /usr/local/bin/patroni_haproxy_updater.sh
# Ensure postgres user can reload haproxy, e.g., via sudoers
# Example: echo "postgres ALL=(ALL) NOPASSWD: /usr/sbin/service haproxy reload" | sudo tee -a /etc/sudoers.d/haproxy

Your Ruby application’s database configuration would then point to HAProxy:

# config/database.yml (Rails example)
production:
  adapter: postgresql
  encoding: unicode
  database: myapp_production
  pool: 5
  host: <HAPROXY_IP> # IP of the HAProxy server
  port: 5000       # HAProxy listen port
  username: app_user
  password: <APP_USER_PASSWORD>

Option 3: Direct Application Logic (Less Recommended)

The application itself could periodically query Patroni’s API or a service discovery endpoint to find the current primary. This adds complexity to the application and couples it tightly to the HA mechanism.

A Ruby gem like `patron` or custom logic could be implemented. For example, a background job or a custom connection adapter could fetch the leader IP from Patroni’s REST API and update the connection string if it changes.

# Example conceptual Ruby code (not a full solution)
require 'net/http'
require 'uri'
require 'json'

def get_patroni_leader(patroni_api_url)
  uri = URI.parse(patroni_api_url)
  response = Net::HTTP.get_response(uri)
  if response.is_a?(Net::HTTPSuccess)
    data = JSON.parse(response.body)
    data['leader'] # Returns "ip:port"
  else
    nil
  end
rescue => e
  puts "Error fetching patroni leader: #{e.message}"
  nil
end

# In your application's database connection setup:
PATRONI_API_URL = "http://localhost:8008/cluster" # Or the IP of a Patroni node
leader_address = get_patroni_leader(PATRONI_API_URL)

if leader_address
  host, port = leader_address.split(':')
  # Configure your ActiveRecord connection with host and port
  ActiveRecord::Base.establish_connection(
    adapter: 'postgresql',
    database: 'myapp_production',
    username: 'app_user',
    password: '',
    host: host,
    port: port
  )
else
  # Handle connection failure - perhaps retry or raise an error
end

This approach requires careful handling of connection re-establishment and potential race conditions. It’s generally preferred to offload this logic to a dedicated proxy like HAProxy or a service discovery system.

Testing Failover Scenarios

Thorough testing is crucial to validate your auto-failover setup. Simulate various failure conditions and observe the system’s behavior.

Simulating Node Failures

Scenario 1: Primary Node Failure

  • Identify the current primary node using `curl http://<PATRONI_NODE_IP>:8008/cluster`.
  • Gracefully stop PostgreSQL on the primary node: `sudo systemctl stop postgresql`.
  • Observe Patroni logs on the remaining nodes. A new leader should be elected within seconds.
  • Verify the new primary using `curl http://<NEW_PRIMARY_IP>:8008/cluster`.
  • Test application connectivity to the new primary (via HAProxy or DNS).

Scenario 2: Network Partition

This is more complex. If the primary node becomes unreachable from the etcd cluster, Patroni will detect this and initiate a failover. If the etcd cluster itself experiences a partition, it can lead to split-brain scenarios if not configured carefully (e.g., quorum). Testing network partitions often involves using tools like `iptables` or cloud provider network isolation features.

Scenario 3: etcd Node Failure

If an etcd node fails, the etcd cluster should remain available as long as a quorum is maintained. Patroni will continue to operate. If a majority of etcd nodes fail, the cluster will become read-only or unavailable, preventing new leader elections and thus failovers.

Testing Application Connectivity

After each failover test, ensure your Ruby application can connect and perform read/write operations. Use automated tests or manual checks.

# Example: Run a simple script to connect and query
# ruby -r pg -e "conn = PG.connect(host: '', port: 5000, dbname: 'myapp_production', user: 'app_user', password: ''); puts conn.exec('SELECT 1').getvalue(0,0)"

If using DNS discovery, ensure DNS records are updated correctly and that your application’s DNS cache is not causing delays in picking up the new IP.

OVH Specific Considerations

When deploying on OVH, consider the following:

  • IP Addressing: Use Public IPs or Floating IPs for your PostgreSQL nodes and HAProxy. Floating IPs are particularly useful as they can be reassigned to a different server in case of hardware failure, simplifying failover orchestration.
  • Network Latency: Deploy your PostgreSQL nodes and HAProxy within the same OVH region and availability zone to minimize latency. If cross-region DR is required, consider asynchronous replication and a more complex failover strategy.
  • Security Groups/Firewalls: Ensure that necessary ports (PostgreSQL 5432, Patroni API 8008, etcd 2379/2380, HAProxy 5000) are open between your nodes and from your application servers.
  • Instance Sizing: Choose instance types that provide sufficient CPU, RAM, and I/O for your PostgreSQL workload.
  • Monitoring: Implement comprehensive monitoring for PostgreSQL, Patroni, etcd, and HAProxy using tools like Prometheus, Grafana, or OVH’s built-in monitoring.

By architecting your PostgreSQL cluster with Patroni and integrating it seamlessly with your Ruby applications via HAProxy or service discovery, you can achieve a highly available and resilient database layer, minimizing downtime during PostgreSQL failures.

Recent Posts

Top Categories

Our Products

Our Services