Skip to main content

PostgreSQL High Availability

Introduction

In today's 24/7 digital world, database downtime can result in significant financial losses, damaged reputation, and poor user experience. High Availability (HA) in PostgreSQL refers to a set of techniques and configurations that ensure your database remains operational and accessible even when hardware fails, network issues occur, or during maintenance activities.

This guide explores the core concepts of PostgreSQL High Availability, various implementation strategies, and practical examples to help you design robust database systems that minimize downtime and maintain data integrity.

What is High Availability?

High Availability is measured by the percentage of time a system remains operational. This is often expressed as "nines" - for example:

  • Two nines (99%) = Up to 3.65 days of downtime per year
  • Three nines (99.9%) = Up to 8.76 hours of downtime per year
  • Four nines (99.99%) = Up to 52.56 minutes of downtime per year
  • Five nines (99.999%) = Up to 5.26 minutes of downtime per year

For most production systems, three or four nines is typically the goal, while critical financial or healthcare systems may aim for five nines.

Key Metrics in High Availability

Before diving into implementation details, let's understand two crucial metrics:

  1. Recovery Time Objective (RTO): The maximum acceptable time to restore system functionality after a failure.
  2. Recovery Point Objective (RPO): The maximum acceptable data loss measured in time.

Your HA strategy will be heavily influenced by your specific RTO and RPO requirements.

PostgreSQL HA Components and Strategies

1. Replication

Replication creates and maintains copies of your database to provide redundancy and is the foundation of most HA solutions.

Physical Replication

Physical replication copies the entire database at the binary level.

Streaming Replication is PostgreSQL's built-in replication mechanism, where changes from the Write-Ahead Log (WAL) are streamed from the primary to one or more standby servers.

sql
# On primary server postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Create a user with replication privileges:

sql
CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'strongpassword';

Set up authentication in pg_hba.conf:

# Allow replication connections from the standby server
host replication replicator 192.168.1.100/32 md5

On the standby server, use pg_basebackup to create an initial copy:

bash
pg_basebackup -h primary_server -D /var/lib/postgresql/data -U replicator -P -v

Configure the standby in postgresql.conf:

primary_conninfo = 'host=primary_server port=5432 user=replicator password=strongpassword'

And create a standby.signal file:

bash
touch /var/lib/postgresql/data/standby.signal

Logical Replication

Logical replication replicates data changes at the logical level (tables, rows) rather than the physical file level. This was introduced in PostgreSQL 10 and offers more flexibility but less completeness than physical replication.

sql
-- On primary server:
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- On standby server:
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=primary_server port=5432 dbname=mydb user=replicator password=strongpassword'
PUBLICATION all_tables;

2. Failover Mechanisms

Failover is the process of switching from the primary to a standby server when the primary fails.

Manual Failover

The simplest approach is manual intervention, where a DBA promotes a standby server when needed:

bash
# On the standby server
pg_ctl promote

Automated Failover

Several tools can automate the failover process:

Patroni is a popular solution that uses a distributed configuration store (like etcd, Consul, or ZooKeeper) to manage PostgreSQL high-availability.

Here's a simplified Patroni configuration:

yaml
scope: postgres-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.101:8008
etcd:
host: 192.168.1.201:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
max_connections: 100
shared_buffers: 4GB
wal_level: replica
hot_standby: "on"
max_wal_senders: 10
max_replication_slots: 10
wal_keep_segments: 100
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.101:5432
data_dir: /var/lib/postgresql/data
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: strongpassword
superuser:
username: postgres
password: postgres_password
parameters:
unix_socket_directories: '/var/run/postgresql'

Let's visualize a Patroni-managed PostgreSQL cluster:

Repmgr is another popular failover manager for PostgreSQL:

bash
# Create a repmgr.conf file
node_id=1
node_name=node1
conninfo='host=192.168.1.101 user=repmgr dbname=repmgr'
data_directory='/var/lib/postgresql/data'

PgPool-II provides load balancing, connection pooling, and automated failover:

# Sample pgpool.conf snippet
backend_hostname0 = 'primary.example.com'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'standby.example.com'
backend_port1 = 5432
backend_weight1 = 1

3. Load Balancing

Load balancers distribute database connections across multiple servers. This improves both performance and availability.

PgPool-II can be configured to send write operations to the primary and read operations to standbys:

load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

HAProxy is another popular option:

frontend pgsql
bind *:5432
mode tcp
default_backend postgresql-cluster

backend postgresql-cluster
mode tcp
balance roundrobin
option pgsql-check user repmgr
server postgres-1 192.168.1.101:5432 check
server postgres-2 192.168.1.102:5432 check backup

4. Regular Backups

Even with replication, regular backups are essential:

bash
# Full backup
pg_basebackup -D /backup/full-$(date +%Y%m%d) -F tar -X fetch -z -v

# Or using pg_dump for logical backups
pg_dump -Fc -v -f /backup/logical-$(date +%Y%m%d).dump dbname

Implementing a Complete HA Solution

Let's build a complete HA solution combining multiple components:

  1. Main Components:
    • Primary PostgreSQL server
    • Multiple standby servers
    • Load balancer (HAProxy)
    • Failover manager (Patroni)
    • Consistent distributed configuration store (etcd)
    • Regular backup solution

Let's see how this all fits together:

Practical Example: Setting Up a Simple HA Cluster

Let's walk through a simplified example of setting up a basic HA PostgreSQL cluster using streaming replication and Patroni.

Step 1: Install PostgreSQL and Patroni on all nodes

bash
# On all nodes (Ubuntu/Debian)
apt update
apt install -y postgresql-14 python3-pip
pip3 install patroni[etcd] psycopg2-binary

Step 2: Set up etcd cluster

bash
# Install and configure etcd on separate nodes
apt install -y etcd

# Configure etcd to form a cluster
# Example etcd.conf for node1
ETCD_NAME="etcd1"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="http://192.168.1.201:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.201:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.201:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.1.201:2380,etcd2=http://192.168.1.202:2380,etcd3=http://192.168.1.203:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="postgres-cluster-token"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.201:2379"

Step 3: Create Patroni configuration on all nodes

For the first node (primary):

yaml
# /etc/patroni/patroni.yml
scope: postgres-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.101:8008
etcd:
hosts: ["192.168.1.201:2379", "192.168.1.202:2379", "192.168.1.203:2379"]
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
max_connections: 100
shared_buffers: 1GB
wal_level: replica
hot_standby: "on"
max_wal_senders: 10
max_replication_slots: 10
wal_keep_segments: 100
initdb:
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.101:5432
data_dir: /var/lib/postgresql/data
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: strongpassword
superuser:
username: postgres
password: postgres_password
parameters:
unix_socket_directories: '/var/run/postgresql'

For the standby nodes, create similar configurations with different name and connect_address values.

Step 4: Create a systemd service for Patroni

ini
# /etc/systemd/system/patroni.service
[Unit]
Description=Patroni PostgreSQL Cluster
After=network.target

[Service]
User=postgres
Group=postgres
Type=simple
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
KillMode=process
TimeoutSec=30
Restart=always

[Install]
WantedBy=multi-user.target

Step 5: Start the services

bash
# Enable and start the service
systemctl enable patroni
systemctl start patroni

# Check status
systemctl status patroni

# View cluster status
patronictl -c /etc/patroni/patroni.yml list

Step 6: Set up HAProxy for load balancing

# /etc/haproxy/haproxy.cfg
global
maxconn 100
log 127.0.0.1 local0 notice

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 /

listen postgres
bind *:5432
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server postgresql_node1 192.168.1.101:5432 maxconn 100 check port 8008
server postgresql_node2 192.168.1.102:5432 maxconn 100 check port 8008
server postgresql_node3 192.168.1.103:5432 maxconn 100 check port 8008

Step 7: Test failover

To test a failover scenario:

bash
# Stop PostgreSQL on the primary
patronictl -c /etc/patroni/patroni.yml pause
systemctl stop patroni

# Check that a standby was promoted
patronictl -c /etc/patroni/patroni.yml list

Common Challenges and Solutions

Time Synchronization

Ensure all database servers use NTP for time synchronization to prevent replication issues:

bash
apt install -y ntp
systemctl enable ntp
systemctl start ntp

Network Partition Handling

Configure proper fencing mechanisms to prevent "split-brain" scenarios where multiple nodes think they're primary:

yaml
# In patroni.yml
watchdog:
mode: automatic
device: /dev/watchdog
safety_margin: 5

Monitoring

Set up comprehensive monitoring using tools like Prometheus and Grafana:

yaml
# In patroni.yml
postgresql:
parameters:
shared_preload_libraries: 'pg_stat_statements'

Best Practices

  1. Regular Testing: Conduct scheduled failover drills to ensure your HA system works as expected.

  2. Backup Verification: Regularly restore backups to test their validity.

  3. Documentation: Maintain detailed documentation of your HA setup, including configurations and recovery procedures.

  4. Monitoring: Implement comprehensive monitoring to detect issues before they cause outages.

  5. Network Redundancy: Implement redundant network paths between all components.

Summary

PostgreSQL High Availability is critical for maintaining business continuity. By implementing replication, automated failover, load balancing, and regular backups, you can build a robust system that minimizes downtime and data loss.

Remember that high availability is not just about technology—it's also about processes, monitoring, and regular testing. The right approach depends on your specific requirements, particularly your RTO and RPO goals.

Additional Resources

Exercises

  1. Set up a simple streaming replication between two PostgreSQL servers.
  2. Configure Patroni on a three-node PostgreSQL cluster and test manual failover.
  3. Implement a backup strategy with daily base backups and hourly WAL archiving.
  4. Configure HAProxy to load balance read queries across multiple PostgreSQL servers.
  5. Simulate a primary server failure and document the failover process and recovery steps.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)