Replication and High Availability

Running PostgreSQL in production means planning for two things: what happens when the primary server fails, and how you handle more read traffic than one server can serve. Replication solves both. A standby server that stays in sync with the primary can take over in seconds after a failure, and in the meantime it can serve read-only queries to offload the primary. Connection pooling solves a separate but equally important problem — the overhead of short-lived application connections at scale.

The Write-Ahead Log (WAL)

Every change in PostgreSQL — INSERT, UPDATE, DELETE, DDL — is written to the Write-Ahead Log before it is applied to the actual data files. This “write ahead” guarantee means that if the server crashes mid-write, PostgreSQL replays the WAL on restart to recover a consistent state without data loss.

WAL is also the foundation of replication. By shipping WAL records to another server, that server can replay the same changes and stay in sync with the primary.

-- Check current WAL position on the primary
SELECT pg_current_wal_lsn();

-- Monitor how far behind each standby is (run on the primary)
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Streaming Replication (Physical)

Streaming replication sends WAL bytes from the primary to one or more standby servers in near real time. The standby is a byte-for-byte copy of the primary — same schema, same data, same PostgreSQL major version. It’s the standard approach for high availability and disaster recovery.

Setting up a primary

In postgresql.conf:

wal_level = replica          # minimum level required for replication
max_wal_senders = 5          # max simultaneous standby connections
wal_keep_size = 1GB          # retain enough WAL for standbys to catch up after lag
listen_addresses = '*'

In pg_hba.conf, allow the standby to connect using the replication protocol:

host  replication  replicator  10.0.0.2/32  scram-sha-256

Create the replication role:

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'rep-pass';

Setting up a standby

Use pg_basebackup to clone the primary, then configure postgresql.auto.conf (PostgreSQL 12+):

primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=rep-pass'
restore_command = ''

Create a standby.signal file in the data directory. The standby starts in recovery mode, continuously applying WAL from the primary.

Synchronous vs Asynchronous

By default replication is asynchronous — the primary confirms a commit before the standby has applied it. This maximizes write performance but means a crash could lose the last few transactions that hadn’t yet reached the standby.

Synchronous replication makes the primary wait for at least one standby to confirm WAL before acknowledging the commit. This gives zero data loss but increases write latency proportionally to the network round-trip time between primary and standby.

-- Check synchronous state of connected standbys
SELECT application_name, sync_state FROM pg_stat_replication;
-- sync_state: 'async' or 'sync'

Using Standbys for Read Scaling

A standby in hot standby mode accepts read-only queries, offloading reporting and analytics workloads from the primary. This is one of the most cost-effective ways to scale read capacity — you’re already running the standby for HA, so serving reads from it costs nothing extra.

-- On the standby: verify it's in recovery mode (i.e., it is a standby)
SELECT pg_is_in_recovery();  -- returns true on standbys

-- Queries are read-only on standbys — writes are rejected
SELECT count(*) FROM orders WHERE created_at >= '2024-01-01';

Point your read-heavy application queries at the standby’s connection string. Send all writes to the primary.

Logical Replication

Logical replication decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and streams them as logical messages. This is more flexible than physical replication because it doesn’t require an identical copy — you can replicate a subset of tables, replicate between different PostgreSQL major versions, or stream changes to non-PostgreSQL systems like Kafka or Debezium.

Publications and Subscriptions

The publisher defines what to replicate; the subscriber consumes it. The model is similar to a message queue: the publisher produces a stream of changes, and subscribers process that stream independently.

-- On the primary: create a publication for specific tables
CREATE PUBLICATION my_pub FOR TABLE orders, customers;

-- Or publish all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- On the subscriber (a different server or a different database on the same server):
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=10.0.0.1 dbname=prod user=replicator password=rep-pass'
  PUBLICATION my_pub;

-- Check subscription status
SELECT subname, subenabled, received_lsn FROM pg_stat_subscription;

The subscriber must have the same table definitions (columns, types) as the publisher. Schema changes are not automatically replicated — you must apply DDL on both sides manually.

Replication Slots

A replication slot ensures the primary retains WAL until the standby has consumed it. This prevents the standby from losing WAL after falling behind, but also means WAL accumulates indefinitely if a standby goes offline. An inactive slot can fill your disk.

-- List replication slots and their lag
SELECT slot_name, slot_type, active, restart_lsn FROM pg_replication_slots;

-- Drop a slot that is no longer needed (WAL will stop accumulating for it)
SELECT pg_drop_replication_slot('my_slot');

Monitor slot lag closely. An inactive slot holding back WAL can fill your disk.

Connection Pooling with PgBouncer

Each PostgreSQL connection is a separate OS process consuming roughly 5–10 MB of RAM. An application with 200 threads each holding a connection consumes 1–2 GB of RAM just for idle connections, plus significant CPU for connection setup and teardown. PgBouncer sits between the application and PostgreSQL, maintaining a smaller pool of actual server connections and multiplexing many client connections onto them.

Pooling Modes

Session mode — one server connection per client session. Lowest compatibility risk. Barely reduces connection count.

Transaction mode — a server connection is held only for the duration of a transaction, then returned to the pool. Most efficient. Incompatible with SET, advisory locks, and LISTEN/NOTIFY that persist across transactions.

Statement mode — connection returned after every statement. Rarely used; incompatible with multi-statement transactions.

# pgbouncer.ini — the key settings for a typical production setup
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # most efficient mode
max_client_conn = 1000           # how many clients PgBouncer accepts
default_pool_size = 25           # actual PostgreSQL server connections

Applications connect to PgBouncer on port 6432 exactly as they would connect to PostgreSQL on 5432. The change is transparent to application code.

Automatic Failover with Patroni

Manual failover — promoting a standby after a primary crash — is slow and error-prone. Patroni is the standard tool for automatic failover in PostgreSQL clusters. It uses a distributed consensus store (etcd, Consul, or ZooKeeper) to elect a leader and promote a standby automatically, typically within 30 seconds of a primary failure.

Key concepts:

• Leader election via etcd/Consul — only one node can hold the leader key at a time
• Fencing — the old primary is blocked from accepting writes before promotion to prevent split-brain
• HAProxy or DNS-based routing in front of the cluster routes writes to the current leader

# Check cluster status (on any Patroni node)
patronictl -c /etc/patroni/config.yml list

# Manual switchover (graceful, planned — for maintenance)
patronictl -c /etc/patroni/config.yml switchover --master primary1 --candidate standby1

Connection String Best Practices

Production connection strings should always include SSL mode, a connection timeout, and an application name (which appears in pg_stat_activity for monitoring). For high-availability clusters, list multiple hosts so the driver tries the next one if the first is unavailable.

postgresql://app_user:pass@db-host:5432/mydb
  ?sslmode=require
  &connect_timeout=5
  &application_name=web-api

For HA clusters with a primary and standby, list both hosts:

postgresql://app_user:pass@primary:5432,standby:5432/mydb
  ?target_session_attrs=read-write
  &sslmode=require

target_session_attrs=read-write ensures the driver connects only to the primary for write connections. Use any for read-only connections that can go to the standby.