- Published on
Building High Availability PostgreSQL A Comprehensive Setup Guide
- Authors
- Name
- Adil ABBADI
Introduction
Ensuring your PostgreSQL database is highly available is crucial for mission-critical applications. High availability (HA) setups minimize downtime, maintain data integrity, and provide uninterrupted service—even in the event of hardware failures or network outages. In this guide, we will walk you through the process of architecting and deploying a high availability PostgreSQL cluster, leveraging leading technologies such as streaming replication, automated failover, and managed proxy layers.
- Preparing the Environment
- Configuring Streaming Replication
- Implementing Automatic Failover
- Adding Load Balancing with PgBouncer
- Conclusion
- Take Your PostgreSQL Cluster to the Next Level
Preparing the Environment
The first step to achieving PostgreSQL high availability is preparing your infrastructure. You’ll need at least three servers: one primary (master) and two replicas (standbys). Optionally, deploy a witness node to facilitate consensus for failover.

You should also ensure the following prerequisites:
- Same PostgreSQL version across all nodes
- Secure private networking between nodes
- Passwordless SSH or certificate-based authentication
You can use cloud instances, on-premises VMs, or a hybrid environment.
# Create postgres user on all nodes (as root)
useradd -m postgres
# Set up SSH keys for seamless connectivity
sudo -i -u postgres ssh-keygen
# Distribute the public key to all nodes
ssh-copy-id postgres@pg-node-2
ssh-copy-id postgres@pg-node-3
Configuring Streaming Replication
Streaming replication is the core mechanism for real-time standby nodes. The primary continuously ships WAL (Write-Ahead Log) segments to replicas to keep data in sync.
- Edit
postgresql.conf
on the primary:
wal_level = replica
max_wal_senders = 5
wal_keep_size = 128MB
hot_standby = on
- Set up
pg_hba.conf
to allow replica connections:
# Allow replication connections from standby servers
host replication all 192.168.1.0/24 md5
- Base backup and initialize replicas:
# On the replica, as postgres user
pg_basebackup -h <primary_ip> -D /var/lib/postgresql/15/main -U replication_user -P --wal-method=stream
- Create
standby.signal
to enable standby mode (PostgreSQL 12+):
touch /var/lib/postgresql/15/main/standby.signal
- Configure
primary_conninfo
in the replica’spostgresql.conf
:
primary_conninfo = 'host=192.168.1.10 port=5432 user=replication_user password=yourpassword'
Implementing Automatic Failover
Automated failover ensures that your application experiences minimal disruption if the primary server fails. Tools like Patroni, repmgr, or PgPool-II are commonly used for automated monitoring and failover.
Here, we'll use Patroni for its Etcd/Consul/DCS integration:

- Install Patroni on all nodes:
pip install patroni[etcd]
- Initialize a configuration (
patroni.yml
):
scope: postgres-ha
namespace: /db/
name: node1
etcd:
host: 192.168.1.20:2379
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.11:8008
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.11:5432
data_dir: /var/lib/postgresql/15/main
authentication:
replication:
username: replicator
password: replicator_password
- Start Patroni on each node and observe leader election and replication:
patroni /etc/patroni.yml
Patroni will manage the state, automate failovers, and restore the cluster if a node recovers.
Adding Load Balancing with PgBouncer
To distribute read workload and optimize application connectivity, a layer such as PgBouncer or PgPool-II is recommended. This enables connection pooling—reducing resource usage—and allows for seamless routing to replicas.
# Install PgBouncer
sudo apt install pgbouncer
# Configure pgbouncer.ini
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
server_reset_query = DISCARD ALL
To direct queries to standbys for load balancing:
# In your application’s connection string
host=pgbouncer_host port=6432 dbname=mydb user=myuser
You can also use scripts or plugins to route read-only queries to replicas for scaling.
Conclusion
A robust PostgreSQL high availability setup combines streaming replication, automated failover, and load balancing to deliver a resilient and scalable database cluster. By deploying these key components, you effectively minimize downtime, achieve better performance, and improve overall service reliability for any mission-critical PostgreSQL workload.
Take Your PostgreSQL Cluster to the Next Level
Explore advanced topics like synchronous replication, geo-replication, and custom health checks. Experiment with backup automation and disaster recovery drills. High availability is a continuous journey—keep optimizing and stay prepared!