PostgreSQL High Availability: Complete Implementation Guide

[Application Servers]
         ↓
[HAProxy/PgPool-II]
    ↙     ↓     ↘
[Primary] → [Sync Standby] → [Async Standby]
    ↑           ↑               ↑
[Patroni/Repmgr for Orchestration]
    ↓           ↓               ↓
[etcd/Consul for Cluster State]

# Install required packages
sudo apt update
sudo apt install -y postgresql-14 postgresql-14-repmgr patroni haproxy etcd

# Install monitoring tools
sudo apt install -y prometheus grafana node_exporter
sudo apt install -y postgresql-14-prometheus-exporter

# postgresql.conf
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (standby1)'

# pg_hba.conf
hostssl replication replicator 192.168.1.0/24 scram-sha-256
hostssl all all 192.168.1.0/24 scram-sha-256

# postgresql.conf
primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=secure_password application_name=standby1'
hot_standby = on
hot_standby_feedback = on

-- On primary: Create replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

-- Create replication slot
SELECT pg_create_physical_replication_slot('standby1_slot');

-- On standby: Initialize replica
pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/14/main -U replicator -P -v -R \
    -X stream -S standby1_slot

# patroni.yml
scope: postgres-cluster
namespace: /db/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.101:8008

etcd:
  hosts: 192.168.1.100:2379,192.168.1.101:2379,192.168.1.102:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 1000
        shared_buffers: 4GB
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
        synchronous_commit: "on"
        synchronous_standby_names: "FIRST 1 (standby1)"

  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.101:5432
  data_dir: /var/lib/postgresql/14/main
  bin_dir: /usr/lib/postgresql/14/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: secure_password
    superuser:
      username: postgres
      password: secure_password
  parameters:
    unix_socket_directories: '/var/run/postgresql'

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

# /etc/systemd/system/patroni.service
[Unit]
Description=Patroni Postgres HA Service
After=network.target

[Service]
Type=simple
User=postgres
Group=postgres
Environment=PATH=/usr/lib/postgresql/14/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target

# /etc/haproxy/haproxy.cfg
global
    maxconn 100000
    log /dev/log local0
    user haproxy
    group haproxy
    daemon
    stats socket /run/haproxy/admin.sock mode 660 level admin

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats refresh 5s

listen postgres_write
    bind *:5000
    mode tcp
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_1 192.168.1.101:5432 maxconn 100 check port 8008
    server postgresql_2 192.168.1.102:5432 maxconn 100 check port 8008
    server postgresql_3 192.168.1.103:5432 maxconn 100 check port 8008

listen postgres_read
    bind *:5001
    mode tcp
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_1 192.168.1.101:5432 maxconn 100 check port 8008
    server postgresql_2 192.168.1.102:5432 maxconn 100 check port 8008
    server postgresql_3 192.168.1.103:5432 maxconn 100 check port 8008

# prometheus.yml
global:
  scrape_interval: 15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metrics_path: '/metrics'

  - job_name: 'patroni'
    static_configs:
      - targets: ['192.168.1.101:8008', '192.168.1.102:8008', '192.168.1.103:8008']
    metrics_path: '/metrics'

-- Check replication status
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Check replication slots
SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- Monitor WAL archiving
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

# prometheus/alerts.yml
groups:
- name: PostgresAlerts
  rules:
  - alert: PostgresqlDown
    expr: pg_up == 0
    for: 1m
    labels:
      severity: critical
    annotations:
      summary: "PostgreSQL instance down"
      description: "PostgreSQL instance has been down for more than 1 minute"

  - alert: PostgresqlReplicationLag
    expr: pg_replication_lag > 300
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "PostgreSQL replication lag"
      description: "Replication lag is more than 5 minutes"

  - alert: PostgresqlHighConnections
    expr: pg_stat_activity_count > 100
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "High number of connections"
      description: "More than 100 connections for 5 minutes"

# Continuous WAL archiving
archive_command = 'test ! -f /mnt/archive/%f && cp %p /mnt/archive/%f'
archive_mode = on
archive_timeout = 60

# Base backup script
#!/bin/bash
BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
pg_basebackup -D ${BACKUP_DIR}/base_${DATE} -F tar -X fetch -P -v

# Retention policy
find ${BACKUP_DIR} -type d -mtime +7 -exec rm -rf {} +

# recovery.conf (PostgreSQL 12+)
restore_command = 'cp /mnt/archive/%f %p'
recovery_target_timeline = 'latest'
recovery_target_time = '2025-01-30 11:55:48+05:30'

# Stop PostgreSQL
systemctl stop postgresql

# Clear data directory
rm -rf /var/lib/postgresql/14/main/*

# Restore base backup
tar xf /backup/base_20250130_115548.tar -C /var/lib/postgresql/14/main

# Create recovery.signal
touch /var/lib/postgresql/14/main/recovery.signal

# Start PostgreSQL
systemctl start postgresql