A single database server is a single point of failure. If it goes down, your application goes down. Replication gives you a copy of your data on another server — for failover, for read scaling, and for backups.

How Streaming Replication Works

PostgreSQL primary-replica replication works through the Write-Ahead Log (WAL). Every change to the primary is recorded in WAL files. The replica connects to the primary, streams those WAL records, and replays them to stay in sync.

The replica is a read-only copy. You can run SELECT queries against it but not INSERT, UPDATE, or DELETE.

Docker Compose Setup

A local primary-replica setup for development:

# docker-compose.yml
version: "3.9"

services:
  primary:
    image: postgres:17
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydb
    command: >
      postgres
        -c wal_level=replica
        -c max_wal_senders=3
        -c wal_keep_size=64
    volumes:
      - primary_data:/var/lib/postgresql/data
      - ./init-replication.sh:/docker-entrypoint-initdb.d/init-replication.sh
    ports:
      - "5432:5432"

  replica:
    image: postgres:17
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      PGPASSWORD: mypassword
    command: >
      bash -c "
        until pg_basebackup -h primary -U myuser -D /var/lib/postgresql/data -P -Xs -R; do
          echo 'Waiting for primary...'; sleep 2;
        done
        postgres
      "
    volumes:
      - replica_data:/var/lib/postgresql/data
    ports:
      - "5433:5432"
    depends_on:
      - primary

volumes:
  primary_data:
  replica_data:

# init-replication.sh (creates replication user)
psql -U myuser -c "CREATE USER replicator REPLICATION LOGIN PASSWORD 'replpass';"
echo "host replication replicator all md5" >> /var/lib/postgresql/data/pg_hba.conf

Start it:

docker compose up -d
# Primary on port 5432, replica on port 5433

Verifying Replication

-- On primary: check replication status
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

-- On replica: check if it's in recovery (read-only)
SELECT pg_is_in_recovery();
-- Returns: t (true = replica)

-- Check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Routing Reads to the Replica

In your application, use two connection pools: one for writes (primary) and one for reads (replica):

import { Pool } from "pg";

// Write pool → primary
const writePool = new Pool({
  connectionString: process.env.DATABASE_PRIMARY_URL,
});

// Read pool → replica
const readPool = new Pool({
  connectionString: process.env.DATABASE_REPLICA_URL,
});

// Use writePool for mutations
async function createUser(email: string, name: string) {
  const { rows } = await writePool.query(
    "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *",
    [email, name]
  );
  return rows[0];
}

// Use readPool for queries
async function getUsers() {
  const { rows } = await readPool.query("SELECT * FROM users ORDER BY created_at DESC");
  return rows;
}

pgBouncer: Connection Pooling

PostgreSQL creates a new process for each connection. Opening 1000 connections means 1000 OS processes. This does not scale.

pgBouncer is a connection pooler that sits between your application and PostgreSQL. It maintains a small pool of real connections and multiplexes thousands of application connections through them.

# pgbouncer.ini
[databases]
mydb = host=primary port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Pool modes:

  • transaction — connection returned to pool after each transaction (recommended)
  • session — connection held for the entire session (safe for all features)
  • statement — returned after each statement (breaks transactions)

Your application connects to pgBouncer on port 6432 instead of PostgreSQL on 5432.

Monitoring Replication Health

-- On primary: monitor all connected replicas
SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  (sent_lsn - replay_lsn) * 8192 / 1024 / 1024 AS lag_mb
FROM pg_stat_replication;

-- On replica: check last replay time
SELECT
  pg_last_wal_receive_lsn() AS received,
  pg_last_wal_replay_lsn() AS replayed,
  pg_last_xact_replay_timestamp() AS last_replay_time,
  now() - pg_last_xact_replay_timestamp() AS lag;

Alert when replication lag exceeds your tolerance (e.g., 30 seconds).

Logical Replication

Streaming replication copies the entire database. Logical replication copies specific tables — useful for migrating data between databases or replicating to a different PostgreSQL major version.

-- On source database: create a publication
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- On target database: create a subscription
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=source_host dbname=mydb user=replicator password=replpass'
  PUBLICATION my_pub;

High Availability with Patroni

For production, use Patroni to automate failover. Patroni monitors the primary and promotes a replica automatically if the primary becomes unreachable.

Architecture:

  • 2-3 PostgreSQL nodes (1 primary + 1-2 replicas)
  • Patroni agent on each node
  • etcd or ZooKeeper for distributed consensus (decides who is primary)
  • HAProxy in front to route connections

This setup provides automatic failover with minimal downtime (~30 seconds). Managed services (AWS RDS, Google Cloud SQL, Supabase) handle all of this for you.

Managed PostgreSQL Options

For most applications, a managed service is simpler than running your own replication:

ServiceStandby replicasConnection poolingAutomated failover
AWS RDS✅ Multi-AZRDS Proxy (proprietary, not PgBouncer)✅
Google Cloud SQL✅ HABuilt-in✅
Supabase✅ Read replicasBuilt-in (pgBouncer)✅
NeonServerlessBuilt-in✅

What’s Next?

You now understand replication and high availability. Let’s move on to MongoDB — a document database that stores data as JSON-like documents.

Next: Database Tutorial #9: MongoDB Setup and CRUD