Vengala Vinay

Having 12+ Years of Experience in Software Development

Home » Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and C Deployments on Linode

Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and C Deployments on Linode

Establishing a Highly Available PostgreSQL Cluster with Patroni

For critical PostgreSQL deployments, a robust disaster recovery strategy is paramount. This involves not just backups, but an automated failover mechanism that minimizes downtime. We’ll architect this using Patroni, a template for PostgreSQL HA, which leverages etcd or Consul for distributed configuration and leader election. This example focuses on etcd for its simplicity and widespread adoption.

Our setup will consist of at least three PostgreSQL nodes. One will be the primary, and the others will be replicas. Patroni will manage the state of these nodes, ensuring that if the primary fails, a replica is promoted to become the new primary with minimal human intervention.

Prerequisites and Initial Setup

Before diving into Patroni configuration, ensure you have the following:

  • A Linode account with at least three Compute Instances.
  • SSH access to all instances.
  • sudo privileges on all instances.
  • A distributed configuration store: We’ll use etcd. Install etcd on at least three separate nodes (or dedicated nodes if available) for etcd’s own HA. For simplicity in this example, we’ll assume etcd is running and accessible on a known IP address (e.g., 192.168.1.100).

On each PostgreSQL node, install PostgreSQL and Patroni. The exact installation method depends on your Linux distribution. For Debian/Ubuntu:

Installing PostgreSQL and Patroni on Debian/Ubuntu

First, update your package lists and install PostgreSQL. It’s recommended to use a recent, stable version.

sudo apt update
sudo apt install postgresql postgresql-contrib python3-pip -y
sudo systemctl enable postgresql
sudo systemctl start postgresql

Next, install Patroni and its dependencies. We’ll use pip for this.

sudo pip3 install 'patroni[etcd]' psycopg2-binary

Ensure the PostgreSQL user has a password set, as Patroni will use it for replication and connection checks. Replace your_pg_password with a strong password.

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_pg_password';"

Patroni Configuration for High Availability

Patroni’s configuration is typically stored in a YAML file. Create this file on each PostgreSQL node. The key is to have a shared configuration that Patroni reads from etcd, but each node also needs its own local configuration pointing to etcd.

Let’s define the patroni.yml file. This configuration will be pushed to etcd.

# /etc/patroni/patroni.yml
scope: my_postgres_cluster
namespace: /service/
restapi:
  listen: 0.0.0.0:8008
  connect_address: &connect_address <NODE_IP>:8000
etcd:
  host: 192.168.1.100:2379
  protocol: http
  loop_wait: 10
  ttl: 30
  retry_timeout: 10
  retry_delay: 5
postgresql:
  listen: 0.0.0.0:5432
  connect_address: *connect_address
  data_dir: /var/lib/postgresql/13/main # Adjust version as needed
  bin_dir: /usr/lib/postgresql/13/bin # Adjust version as needed
  pg_hba:
    - host replication replicator <REPLICA_SUBNET>/24 md5
    - host all all <CLIENT_SUBNET>/24 md5
  replication:
    username: replicator
    password: your_replication_password # Use a strong, dedicated password
    ssl: false
  parameters:
    max_connections: 100
    shared_buffers: 128MB
    effective_cache_size: 384MB
    maintenance_work_mem: 64MB
    wal_level: replica
    wal_sync_method: fsync
    wal_writer_delay: 200ms
    wal_buffers: 16MB
    checkpoint_completion_target: 0.9
    checkpoint_timeout: 5min
    max_wal_senders: 10
    max_replication_slots: 10
    hot_standby: "on"
    default_transaction_isolation: read committed
    log_statement: "ddl"
    log_replication_commands: "on"
    log_connections: "on"
    log_disconnections: "on"
    logging_collector: "on"
    log_directory: "log"
    log_filename: "postgresql-%a.log"
    log_min_duration_statement: 250ms
    log_checkpoints: "on"
    log_lock_waits: "on"
    log_temp_files: 0
    log_autovacuum_min_duration: 0
    autovacuum_max_workers: 3
    autovacuum_naptime: 1min
    autovacuum_vacuum_threshold: 50
    autovacuum_analyze_threshold: 50
    vacuum_cost_delay: 10ms
    vacuum_cost_page_hit: 1
    vacuum_cost_page_miss: 10
    vacuum_cost_page_unavailable: 50
    vacuum_cost_limit: 1000
    synchronous_commit: "on"
    synchronous_standby_names: "stream_replica_1,stream_replica_2" # Example, adjust as needed
    synchronous_commit_on_remote_write: "off"
    synchronous_commit_on_remote_apply: "off"
    listen_addresses: "*"
    port: 5432
tags:
  clone: false
  nofailover: false
  nosync: false
  clonefrom: false
  noload: false
  nodrain: false
  nosync: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog: false
  nolog

Recent Posts

Top Categories

Our Products

Our Services