Vengala Vinay

Having 12+ Years of Experience in Software Development

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

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

Leveraging PostgreSQL Streaming Replication for High Availability

Achieving automated failover for a PostgreSQL database is paramount for any production system. We’ll focus on setting up synchronous streaming replication, which ensures that a replica is always up-to-date with the primary, minimizing data loss during a failover event. This setup involves configuring the primary and replica instances, managing WAL (Write-Ahead Logging) shipping, and establishing a mechanism for monitoring and promoting the replica.

Primary PostgreSQL Server Configuration

On the primary PostgreSQL server, we need to enable WAL archiving and configure it for streaming replication. This involves modifying the postgresql.conf and pg_hba.conf files.

postgresql.conf Adjustments

Ensure the following parameters are set in your primary’s postgresql.conf. The exact path to this file varies by PostgreSQL version and installation method (e.g., /etc/postgresql/14/main/postgresql.conf on Debian/Ubuntu).

wal_level = replica
wal_sync_method = fsync
wal_log_hints = on
max_wal_senders = 10
wal_keep_size = 1024  # Or a sufficiently large value to prevent replication lag issues
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f' # Example: adjust path as needed
# For synchronous replication, define the synchronous_standby_names
# synchronous_standby_names = 'replica1' # If you have a single replica named 'replica1'
# For multiple replicas, you can use 'ANY 1 (replica1, replica2)' or 'FIRST 1 (replica1, replica2)'
# For this example, we'll assume a single replica for simplicity in the initial setup.

After modifying postgresql.conf, a PostgreSQL service restart is required for these changes to take effect.

pg_hba.conf Configuration

The pg_hba.conf file controls client authentication. We need to allow the replica to connect for replication.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Allow replication connections from the replica server
host    replication     replicator      [REPLICA_IP_ADDRESS]/32   scram-sha-256 # Or md5, depending on your security policy

Replace [REPLICA_IP_ADDRESS] with the actual IP address of your replica server. After updating pg_hba.conf, reload the PostgreSQL configuration:

sudo systemctl reload postgresql

Replica PostgreSQL Server Setup

The replica server needs to be initialized from a base backup of the primary and configured to connect to it. First, ensure PostgreSQL is installed on the replica but not yet initialized.

Creating a Base Backup

Stop the PostgreSQL service on the replica if it’s running. Then, remove the existing data directory (e.g., /var/lib/postgresql/14/main/) and perform a base backup using pg_basebackup.

# On the replica server:
sudo systemctl stop postgresql

# Remove existing data directory (ensure it's empty or backed up if needed)
sudo rm -rf /var/lib/postgresql/14/main/*

# Perform the base backup
sudo -u postgres pg_basebackup -h [PRIMARY_IP_ADDRESS] -p 5432 -U replicator -D /var/lib/postgresql/14/main -Fp -Xs -P -R

# Example with authentication (if replicator user requires a password)
# sudo -u postgres pg_basebackup -h [PRIMARY_IP_ADDRESS] -p 5432 -U replicator -W -D /var/lib/postgresql/14/main -Fp -Xs -P -R

Explanation of flags:

  • -h [PRIMARY_IP_ADDRESS]: Hostname or IP of the primary server.
  • -p 5432: Port of the primary server.
  • -U replicator: Replication user.
  • -D /var/lib/postgresql/14/main: Destination data directory on the replica.
  • -Fp: Format is plain (not tar).
  • -Xs: Stream WAL files.
  • -P: Show progress.
  • -R: Create standby.signal and postgresql.auto.conf for replication setup.

The -R flag is crucial as it automatically creates the necessary configuration files (standby.signal and updates postgresql.auto.conf) to configure the server as a standby.

Replica postgresql.conf and pg_hba.conf

The -R flag in pg_basebackup typically handles most of the replica-specific configuration. However, you might need to adjust postgresql.conf for performance or monitoring. Ensure pg_hba.conf on the replica allows connections from your application servers.

# On the replica server, in postgresql.conf (or postgresql.auto.conf if created by -R)
hot_standby = on # Allows read-only queries on the replica
max_standby_streaming_delay = 30s # Adjust as needed
listen_addresses = '*' # Or specific IPs

# On the replica server, in pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Allow connections from application servers
host    all             all             [APP_SERVER_IP]/32      scram-sha-256
host    replication     replicator      [PRIMARY_IP_ADDRESS]/32   scram-sha-256 # For replication connection back to primary if needed

Start the PostgreSQL service on the replica:

sudo systemctl start postgresql

Verify replication status by querying pg_stat_replication on the primary and pg_stat_wal_receiver on the replica.

-- On Primary:
SELECT * FROM pg_stat_replication;

-- On Replica:
SELECT * FROM pg_stat_wal_receiver;

Automated Failover Orchestration with Patroni

Manual failover is error-prone and slow. For true automation, we’ll use Patroni, a template for PostgreSQL HA. Patroni uses a distributed configuration store (like etcd, Consul, or ZooKeeper) to manage cluster state and orchestrate failovers.

Setting up etcd

Patroni requires a distributed key-value store. etcd is a popular choice. For a production setup, you’d deploy a clustered etcd instance across multiple availability zones. For simplicity here, we’ll assume a single etcd node.

# Example: Installing etcd on a Debian/Ubuntu system
curl -L https://github.com/etcd-io/etcd/releases/download/v3.5.9/etcd-v3.5.9-linux-amd64.tar.gz -o etcd-v3.5.9-linux-amd64.tar.gz
tar xzvf etcd-v3.5.9-linux-amd64.tar.gz
sudo mv etcd-v3.5.9-linux-amd64/etcd* /usr/local/bin/
sudo mv etcd-v3.5.9-linux-amd64/Documentation/ /usr/share/doc/etcd/

# Create systemd service for etcd (simplified example)
sudo nano /etc/systemd/system/etcd.service

[Unit]
Description=etcd key-value store
Documentation=man:etcd(1)

[Service]
User=etcd
ExecStart=/usr/local/bin/etcd \
  --name infra0 \
  --listen-client-urls http://0.0.0.0:2379 \
  --advertise-client-urls http://[ETCD_NODE_IP]:2379 \
  --listen-peer-urls http://0.0.0.0:2380 \
  --initial-advertise-peer-urls http://[ETCD_NODE_IP]:2380 \
  --initial-cluster infra0=http://[ETCD_NODE_IP]:2380 \
  --initial-cluster-state new \
  --data-dir /var/lib/etcd

[Install]
Reboot.Target

Replace [ETCD_NODE_IP] with the IP address of your etcd node. Create the etcd user and data directory, then start and enable the service.

sudo groupadd --system etcd
sudo useradd --system -g etcd -d /var/lib/etcd -s /sbin/nologin etcd
sudo mkdir -p /var/lib/etcd
sudo systemctl daemon-reload
sudo systemctl start etcd
sudo systemctl enable etcd

Patroni Installation and Configuration

Install Patroni and its dependencies. The recommended way is using pip.

# On each PostgreSQL server (primary and replica)
pip install --upgrade pip
pip install "patroni[etcd]" psycopg2-binary

Create a Patroni configuration file (e.g., /etc/patroni/patroni.yml) on each PostgreSQL node. This configuration will be largely identical across nodes, with minor differences for node-specific settings.

# /etc/patroni/patroni.yml on each PostgreSQL node

# General Patroni configuration
scope: my_postgres_cluster # Unique name for this cluster
namespace: /service/ # Base path in etcd for this cluster

# PostgreSQL configuration
postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/postgresql/14/main
  bin_dir: /usr/lib/postgresql/14/bin # Adjust path as per your installation
  pg_hba:
    - host    replication     replicator      [REPLICA_IP_ADDRESS]/32   scram-sha-256
    - host    replication     replicator      [PRIMARY_IP_ADDRESS]/32   scram-sha-256
    - host    all             all             [APP_SERVER_IP]/32      scram-sha-256
  parameters:
    max_connections: 100
    shared_buffers: 256MB
    effective_cache_size: 768MB
    maintenance_work_mem: 64MB
    wal_level: replica
    wal_sync_method: fsync
    wal_log_hints: on
    max_wal_senders: 10
    hot_standby: on
    synchronous_commit: on # For synchronous replication
    synchronous_standby_names: "replica1" # This will be managed by Patroni

# Replication configuration
replication:
  synchronous_mode: true # Enable synchronous replication
  synchronous_node_names: # Patroni will manage this list
    - replica1

# REST API configuration
restapi:
  listen: 0.0.0.0:8008
  connect_address: [NODE_IP]:8008 # Node-specific IP

# Distributed configuration store (etcd)
etcd:
  host: [ETCD_NODE_IP]:2379
  protocol: http # Use https for production with TLS

# Tags for node identification
tags:
  nofailover: false # Set to true to prevent this node from being elected as primary
  clonefrom: false # Set to true if this node can be cloned from

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

Crucially, on the primary node, you’ll set synchronous_standby_names in postgresql.conf to a placeholder or an empty string, as Patroni will manage this dynamically. On the replica(s), ensure hot_standby is enabled. Patroni will handle the promotion and demotion of replicas and update the synchronous_standby_names parameter on the primary.

Running Patroni as a Service

Create a systemd service file for Patroni on each PostgreSQL node (e.g., /etc/systemd/system/patroni.service).

[Unit]
Description=Patroni - High-availability for PostgreSQL
After=network.target etcd.service # Ensure etcd is running before Patroni

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

[Install]
WantedBy=multi-user.target

Start and enable the Patroni service on all nodes:

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

Patroni will now bootstrap the cluster. It will elect a leader (primary), configure replication, and manage the cluster state in etcd. You can check the Patroni logs (/var/log/patroni/patroni.log) and etcd for status.

Python Application Integration and Load Balancing

Your Python applications need to be aware of the PostgreSQL cluster’s primary. Directly connecting to a fixed IP address will break during failover. We’ll use a virtual IP (VIP) managed by Patroni or a DNS-based approach.

Virtual IP (VIP) Management

Patroni can manage a VIP using tools like keepalived. This involves configuring keepalived on your PostgreSQL nodes to monitor Patroni’s REST API and take ownership of the VIP when a node becomes the primary.

# Example keepalived.conf on each PostgreSQL node
# This configuration is simplified and assumes Patroni is running on port 8008
vrrp_script check_patroni {
  script "/usr/bin/curl --fail http://localhost:8008/primary"
  interval 2
  weight 20
  fall 2
  rise 2
}

vrrp_instance VI_1 {
  state BACKUP # Start as BACKUP on all nodes
  interface eth0 # Your network interface
  virtual_router_id 51
  priority 100 # Lower priority for backup nodes
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass your_vrrp_password
  }
  virtual_ipaddress {
    [VIRTUAL_IP_ADDRESS]/24 dev eth0 # The IP your applications will connect to
  }
  track_script {
    check_patroni
  }
}

When a node becomes primary, Patroni’s REST API will respond successfully to /primary. keepalived will detect this, increase the node’s priority, and take over the VIP. Applications connect to this VIP.

DNS-based Failover

Alternatively, you can use DNS. Patroni can be configured to update DNS records (e.g., using AWS Route 53, Google Cloud DNS, or an on-premise DNS server with dynamic updates). This requires a Patroni handler script or integration with a DNS provider’s API.

# Example Patroni handler for DNS update (conceptual)
# This would be a separate script Patroni calls on role change events.

import requests
import json
import os

def update_dns_record(record_name, ip_address):
    # Logic to update DNS record via API (e.g., Boto3 for Route 53)
    print(f"Updating DNS record {record_name} to {ip_address}")
    # ... API calls to update DNS ...
    pass

def main():
    event_data = json.loads(os.environ.get('PATRONI_EVENT_DATA'))
    role = event_data.get('role')
    new_primary_ip = event_data.get('new_primary_ip')
    cluster_scope = event_data.get('scope')

    if role == 'primary':
        dns_record_name = f"{cluster_scope}.yourdomain.com"
        update_dns_record(dns_record_name, new_primary_ip)
    elif role == 'replica':
        # Potentially remove or update DNS record if it was pointing to the old primary
        pass

if __name__ == "__main__":
    main()

Your Python application’s database connection string would then point to the DNS name (e.g., my_postgres_cluster.yourdomain.com), which resolves to the current primary’s IP.

Python Application Connection Pooling

Regardless of the failover mechanism, robust connection pooling in your Python application is essential. Libraries like SQLAlchemy with its connection pool or dedicated poolers like PgBouncer (though integrating PgBouncer with Patroni requires careful configuration) can help manage connections efficiently and retry failed connections.

# Example using SQLAlchemy with connection pooling and retry logic
from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError
import time

DATABASE_URL = "postgresql://user:password@[VIRTUAL_IP_OR_DNS_NAME]:5432/mydatabase"
MAX_RETRIES = 5
RETRY_DELAY = 5 # seconds

engine = create_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    pool_timeout=30, # seconds
    connect_args={"connect_timeout": 10} # seconds
)

def get_db_connection():
    retries = 0
    while retries < MAX_RETRIES:
        try:
            conn = engine.connect()
            return conn
        except OperationalError as e:
            print(f"Connection failed: {e}. Retrying in {RETRY_DELAY}s...")
            retries += 1
            time.sleep(RETRY_DELAY)
    raise Exception("Failed to connect to the database after multiple retries.")

# Usage:
# conn = get_db_connection()
# result = conn.execute("SELECT 1")
# print(result.scalar())
# conn.close()

This pattern ensures that transient connection errors during a failover are handled gracefully by retrying the connection attempt.

OVH Specific Considerations

When deploying on OVH, consider the following:

  • Network Latency: Deploy your PostgreSQL primary and replicas within the same OVH region and preferably the same availability zone for minimal replication lag. If cross-zone replication is necessary, ensure your network configuration is optimized.
  • Instance Sizing: Choose PostgreSQL instance sizes that can handle your read/write load and provide sufficient IOPS for WAL operations.
  • Security Groups/Firewalls: Configure OVH security groups to allow PostgreSQL traffic (port 5432) between your application servers, PostgreSQL nodes, and etcd nodes. Also, allow Patroni’s REST API port (8008) for keepalived or other monitoring tools.
  • Managed PostgreSQL: If using OVH’s managed PostgreSQL service, check their documentation for HA and failover capabilities. They might offer built-in solutions that simplify this process, though Patroni provides more granular control.
  • Monitoring: Implement comprehensive monitoring for PostgreSQL (e.g., using Prometheus with the `postgres_exporter`), Patroni, etcd, and keepalived. Set up alerts for replication lag, cluster health, and failover events.

By combining PostgreSQL’s robust replication features with Patroni for orchestration and careful application integration, you can build a highly available PostgreSQL deployment on OVH that automatically handles failover, ensuring minimal downtime for your Python applications.

Recent Posts

Top Categories

Our Products

Our Services