PostgreSQL Read Replicas: Internals, Setup & Production Gotchas

-- ============================================================
-- Run this on your PRIMARY to inspect all connected replicas.
-- pg_stat_replication is the single most important view for
-- understanding the health of your replication setup.
-- ============================================================

SELECT
    client_addr,                         -- IP address of the replica
    application_name,                    -- Name set by the replica (useful when you have many)
    state,                               -- 'streaming' is healthy; 'catchup' means it's behind
    sent_lsn,                            -- Last WAL position sent from primary
    write_lsn,                           -- Last WAL position written to disk on replica
    flush_lsn,                           -- Last WAL position flushed (durable) on replica
    replay_lsn,                          -- Last WAL position actually applied to replica data files
    -- Calculate lag in bytes — this is raw distance, not time
    (sent_lsn - replay_lsn) AS replication_lag_bytes,
    -- Calculate lag as a human-readable time estimate
    -- write_lag: network delay; flush_lag: disk delay; replay_lag: apply delay
    write_lag,
    flush_lag,
    replay_lag,
    sync_state                           -- 'async' or 'sync' — see the comparison table below
FROM
    pg_stat_replication
ORDER BY
    replay_lag DESC NULLS LAST;          -- Put the most-lagged replica first

-- ============================================================
-- Run this on your REPLICA to check its own recovery state.
-- pg_is_in_recovery() = true means this server IS a replica.
-- ============================================================

SELECT
    pg_is_in_recovery()                  AS is_replica,
    pg_last_wal_receive_lsn()            AS last_wal_received,
    pg_last_wal_replay_lsn()             AS last_wal_replayed,
    -- This is the lag in bytes from what was received vs replayed
    pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() AS replay_queue_bytes,
    pg_last_xact_replay_timestamp()      AS last_transaction_replayed_at,
    -- How many seconds behind is this replica right now?
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;

#!/bin/bash
# ============================================================
# STEP 1: Configure the PRIMARY server (run these on primary)
# ============================================================

# Edit postgresql.conf on the primary — these are the minimal
# required settings for streaming replication.
cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'

# --- Replication settings ---
wal_level = replica          # Enables WAL content needed for replication
max_wal_senders = 5          # Max simultaneous replica connections (+2 buffer)
wal_keep_size = 1024         # Keep 1GB of WAL on disk for lagging replicas
                             # Prevents "requested WAL segment has already been removed"
hot_standby = on             # Allow replicas to serve read queries (default on in PG 10+)

# Optional but important for monitoring:
track_commit_timestamp = on  # Needed for pg_last_committed_xact() on replica
EOF

# Create a dedicated replication user — minimal privileges, dedicated password
psql -U postgres -c "
    CREATE ROLE replication_user
    WITH REPLICATION LOGIN
    PASSWORD 'use-a-real-secret-manager-in-production';
"

# Allow the replica's IP to connect for replication in pg_hba.conf
# Replace 10.0.1.22/32 with your actual replica IP
echo "host replication replication_user 10.0.1.22/32 scram-sha-256" \
    >> /etc/postgresql/16/main/pg_hba.conf

# Reload configuration (no restart needed for pg_hba changes)
pg_ctlcluster 16 main reload


# ============================================================
# STEP 2: Take a base backup (run on the REPLICA server)
# ============================================================

# pg_basebackup copies the primary's data directory live.
# -R flag automatically writes primary_conninfo into postgresql.conf
#    AND creates the standby.signal file — huge time saver.
# -P shows progress. -Xs streams WAL during the backup itself
#    so no WAL is missed between backup start and end.
pg_basebackup \
    --host=10.0.1.10 \
    --port=5432 \
    --username=replication_user \
    --pgdata=/var/lib/postgresql/16/main \
    --wal-method=stream \
    --write-recovery-conf \
    --checkpoint=fast \
    --progress \
    --verbose

# Output during backup:
# pg_basebackup: initiating base backup, waiting for checkpoint to complete
# pg_basebackup: checkpoint completed
# pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
# pg_basebackup: starting background WAL receiver
# 30521/30521 kB (100%), 1/1 tablespace
# pg_basebackup: write-ahead log end point: 0/3000100
# pg_basebackup: syncing data to disk ...
# pg_basebackup: renaming backup_manifest.tmp to backup_manifest
# pg_basebackup: base backup completed


# ============================================================
# STEP 3: Verify the replica auto-configured correctly
# (pg_basebackup -R does this, but let's see what it wrote)
# ============================================================
grep -A5 'primary_conninfo' /var/lib/postgresql/16/main/postgresql.conf

# Expected output:
# primary_conninfo = 'host=10.0.1.10 port=5432 user=replication_user \
#                     password=use-a-real-secret-manager-in-production \
#                     application_name=replica-us-east'

ls -la /var/lib/postgresql/16/main/standby.signal
# Expected output:
# -rw------- 1 postgres postgres 0 Nov 14 09:15 standby.signal
# An empty file — its mere presence tells PostgreSQL "I am a replica"


# ============================================================
# STEP 4: Start the replica
# ============================================================
pg_ctlcluster 16 main start

# Tail the logs to confirm it connected and started streaming:
tail -f /var/log/postgresql/postgresql-16-main.log

# Healthy startup output:
# LOG:  entering standby mode
# LOG:  redo starts at 0/3000028
# LOG:  consistent recovery state reached at 0/3000100
# LOG:  database system is ready to accept read-only connections
# LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

import psycopg
from psycopg.rows import dict_row
import logging
from contextlib import contextmanager
from typing import Generator

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# ============================================================
# Connection strings for primary and replica.
# In production, pull these from environment variables or
# a secrets manager — never hardcode credentials.
# ============================================================
PRIMARY_DSN = (
    "host=10.0.1.10 "
    "port=5432 "
    "dbname=app_production "
    "user=app_user "
    "password=secret "
    "target_session_attrs=read-write"  # Guarantees we connect to writable primary
)

REPLICA_DSN = (
    "host=10.0.1.22,10.0.1.45 "        # List multiple replicas — driver picks one
    "port=5432 "
    "dbname=app_production "
    "user=app_user "
    "password=secret "
    "target_session_attrs=prefer-standby"  # Prefer replica, fall back to primary
)


@contextmanager
def get_primary_connection() -> Generator[psycopg.Connection, None, None]:
    """Use for all writes and reads where consistency is critical."""
    with psycopg.connect(PRIMARY_DSN, row_factory=dict_row) as conn:
        yield conn


@contextmanager
def get_replica_connection() -> Generator[psycopg.Connection, None, None]:
    """Use for reads that can tolerate a small amount of lag."""
    with psycopg.connect(REPLICA_DSN, row_factory=dict_row) as conn:
        yield conn


def get_replica_lag_seconds(replica_conn: psycopg.Connection) -> float:
    """
    Check how many seconds behind this replica is before trusting
    it for a time-sensitive read. Returns 0.0 if on primary.
    """
    with replica_conn.cursor() as cursor:
        cursor.execute("""
            SELECT
                CASE
                    WHEN pg_is_in_recovery() THEN
                        EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
                    ELSE
                        0  -- We're actually on the primary, no lag
                END AS lag_seconds;
        """)
        result = cursor.fetchone()
        return float(result['lag_seconds'] or 0)


def create_user_and_fetch_profile(username: str, email: str) -> dict:
    """
    Demonstrates the write-then-read consistency problem.

    WRONG pattern: write to primary, immediately read from replica.
    The replica may not have replayed the INSERT yet.

    RIGHT pattern: write to primary, confirm WAL position, then
    either read from primary or wait for replica to catch up.
    """
    created_user_id = None
    write_lsn = None

    # --- WRITE: Always goes to primary ---
    with get_primary_connection() as primary_conn:
        with primary_conn.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO users (username, email, created_at)
                VALUES (%s, %s, now())
                RETURNING id;
                """,
                (username, email)
            )
            created_user_id = cursor.fetchone()['id']
            primary_conn.commit()

            # Capture the LSN immediately after commit.
            # This is the WAL position the replica must reach
            # before it will have our new user.
            cursor.execute("SELECT pg_current_wal_lsn() AS current_lsn;")
            write_lsn = cursor.fetchone()['current_lsn']
            logger.info(f"User {created_user_id} created. WAL LSN at write: {write_lsn}")

    # --- READ: Use replica, but verify it's caught up past our write ---
    with get_replica_connection() as replica_conn:
        with replica_conn.cursor() as cursor:
            # Wait for the replica to replay past our write LSN.
            # pg_wal_lsn_diff returns 0 when replica has caught up.
            # Timeout of 5 seconds is a production-reasonable ceiling.
            cursor.execute(
                """
                SELECT pg_wal_replay_wait(%s, timeout => 5000);
                """,
                (write_lsn,)
                # NOTE: pg_wal_replay_wait is PostgreSQL 16+.
                # For older versions, use a polling loop with
                # pg_last_wal_replay_lsn() and pg_wal_lsn_diff().
            )

            lag_after_wait = get_replica_lag_seconds(replica_conn)
            logger.info(f"Replica lag after wait: {lag_after_wait:.3f}s")

            # Now safe to read — replica has replayed our write
            cursor.execute(
                """
                SELECT id, username, email, created_at
                FROM users
                WHERE id = %s;
                """,
                (created_user_id,)
            )
            user_profile = cursor.fetchone()
            return dict(user_profile)


# ============================================================
# ANALYTICS QUERIES — these are perfect replica candidates:
# large scans, aggregate reports, no freshness requirement.
# ============================================================
def get_daily_signups_report(days_back: int = 30) -> list[dict]:
    """Heavy aggregation — ideal for replica to absorb."""
    with get_replica_connection() as conn:
        lag = get_replica_lag_seconds(conn)
        if lag > 60:
            # If replica is more than 60s behind, warn but continue —
            # for a daily report, 60s stale data is acceptable.
            logger.warning(f"Replica is {lag:.1f}s behind. Report may be slightly stale.")

        with conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT
                    date_trunc('day', created_at) AS signup_date,
                    COUNT(*) AS new_users
                FROM users
                WHERE created_at >= now() - make_interval(days => %s)
                GROUP BY 1
                ORDER BY 1 DESC;
                """,
                (days_back,)
            )
            return [dict(row) for row in cursor.fetchall()]


if __name__ == "__main__":
    # Demonstrate lag-safe write-then-read
    profile = create_user_and_fetch_profile("alice_dev", "alice@example.com")
    print(f"Profile fetched from replica: {profile}")

    # Demonstrate analytics query routed to replica
    report = get_daily_signups_report(days_back=7)
    print(f"Daily signups (last 7 days): {report}")

-- ============================================================
-- On PRIMARY: configure which replicas must acknowledge commits.
-- The application_name in primary_conninfo must match these names.
-- ============================================================

-- Option A: Wait for ONE specific replica (simple, brittle)
-- ALTER SYSTEM SET synchronous_standby_names = 'replica-us-east';

-- Option B: Quorum — wait for ANY 1 of 2 replicas (resilient)
-- If one replica goes down, commits still succeed via the other.
ALTER SYSTEM SET synchronous_standby_names =
    'ANY 1 (replica-us-east, replica-eu-west)';

SELECT pg_reload_conf(); -- Apply without restart

-- Verify the setting took effect:
SHOW synchronous_standby_names;
-- Result: ANY 1 (replica-us-east, replica-eu-west)


-- ============================================================
-- Per-transaction synchronous_commit control.
-- Use this to selectively require durability without
-- making every transaction pay the latency cost.
-- ============================================================

-- Example: financial transfer requires synchronous durability
BEGIN;
    SET LOCAL synchronous_commit = on;   -- This transaction: wait for replica ACK
    UPDATE accounts SET balance = balance - 500.00 WHERE account_id = 1001;
    UPDATE accounts SET balance = balance + 500.00 WHERE account_id = 2004;
    INSERT INTO transfer_log (from_account, to_account, amount, transferred_at)
        VALUES (1001, 2004, 500.00, now());
COMMIT;
-- The COMMIT here will block until at least 1 of the 2 replicas acknowledges
-- the WAL write. Safe to tell the user "your transfer is confirmed."

-- Example: event tracking can tolerate some loss — use async for speed
BEGIN;
    SET LOCAL synchronous_commit = off;  -- This transaction: don't wait for replica
    INSERT INTO page_view_events (user_id, page_url, viewed_at)
        VALUES (8821, '/products/widget', now());
COMMIT;
-- Returns immediately. In a crash scenario, this event might be lost.
-- That's an acceptable trade-off for non-critical analytics data.


-- ============================================================
-- Monitor synchronous replication ACK status in real time
-- ============================================================
SELECT
    application_name,
    sync_state,              -- 'sync', 'potential', or 'async'
    sent_lsn,
    flush_lsn,
    replay_lsn,
    -- 'sync': currently the active synchronous replica
    -- 'potential': would become sync if the active one fails
    -- 'async': purely asynchronous
    CASE sync_state
        WHEN 'sync'      THEN 'ACTIVE sync replica — commits wait for this one'
        WHEN 'potential' THEN 'STANDBY sync replica — takes over if sync fails'
        WHEN 'async'     THEN 'Pure async — commits never wait for this one'
    END AS sync_role_description
FROM
    pg_stat_replication
ORDER BY
    sync_state;