postgresql.conf (replica)

10 * 1024 * 1024; // 10MB private pools: Map<string, Pool> = new Map();

constructor(private replicas: string[]) { this.replicas.forEach(host => { this.pools.set(host, new Pool({ host, user: 'app_read', database: 'app_db' })); }); }

async getHealthyReplica(): Promise<PoolClient | null> { const healthChecks = await Promise.all( this.replicas.map(async host => { const pool = this.pools.get(host)!; try { const res = await pool.query( SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS lag_bytes ); const lagBytes = Number(res.rows[0].lag_bytes); return { instance: host, lagBytes, isReady: lagBytes <= this.MAX_LAG_BYTES }; } catch { return { instance: host, lagBytes: Infinity, isReady: false }; } }) );

const healthy = healthChecks.find(r => r.isReady);
if (!healthy) return null;
return this.pools.get(healthy.instance)!.connect();

} }

### Step 4: Monitoring & Validation Pipeline
Lag metrics alone are insufficient. Track WAL generation rate, checkpoint duration, and replication slot retention age. Export metrics to Prometheus via `pg_stat_replication`, `pg_stat_archiver`, and `pg_replication_slots`. Alert on:
- Slot age > 2 hours
- WAL send queue depth > 100MB
- Replica replay pause > 5 seconds
- Synchronous commit timeout spikes

Architecture decisions:
- **Replication network isolation:** Route replication traffic over a dedicated VPC subnet or interface to prevent client query contention from starving WAL shipping.
- **Slot management:** Use `pg_replication_slots` with automated cleanup on replica decommission. Never drop slots manually in production.
- **Failover governance:** Implement automated promotion only after quorum verification and LSN alignment. Use tools like Patroni, MHA, or cloud-native failover controllers.
- **Read consistency boundaries:** Define explicit RPO/RTO SLAs per workload. Financial reads require strict lag thresholds; analytics workloads tolerate async drift.

## Pitfall Guide

1. **Treating replication lag as a static threshold instead of a time-series signal**
   Lag spikes during checkpoint stalls or long-running transactions. Alert on sustained lag (>30s) and rate-of-change, not instantaneous values. Use exponential moving averages to filter noise.

2. **Not reserving replication slots**
   Without slots, the primary removes WAL segments once they're archived, breaking replication after brief downtime. Always create slots and monitor `pg_replication_slots.active`. Drop slots only after replica decommissioning.

3. **Mixing backup and replication strategies**
   Replication is not backup. It propagates corruption, drops, and logical errors instantly. Maintain separate point-in-time recovery (PITR) with WAL archiving and periodic full snapshots. Never rely on replicas for disaster recovery without validation.

4. **Ignoring transaction ID wraparound on long-running replicas**
   PostgreSQL uses 32-bit XIDs. Replicas that fall behind for months may hit wraparound, forcing a vacuum freeze or shutdown. Monitor `age(datfrozenxid)` and schedule aggressive autovacuum on replicas if they serve read traffic.

5. **Failover without quorum verification**
   Promoting a replica during a network partition creates split-brain. Require quorum checks, LSN alignment, and client disconnect before promotion. Use fencing mechanisms (`fence_agent`, `pg_rewind`) to prevent dual-primary scenarios.

6. **Hardcoding replica endpoints**
   Replicas scale, fail, and rotate. Use service discovery (Consul, Kubernetes Endpoints, or DNS SRV) with health-aware routing. Hardcoded IPs break during scaling events and increase failover time.

7. **Skipping post-promotion consistency validation**
   After promotion, verify `pg_is_in_recovery()` returns false, check for unapplied WAL, and run schema/data checksums. Stale promotions leave orphaned connections and inconsistent indexes.

**Best practices from production:**
- Implement automated `pg_verify_checksums` on replicas weekly
- Use connection poolers (PgBouncer, ProxySQL) with lag-aware routing
- Run chaos tests simulating network partitions and WAL sender crashes
- Enforce RPO/RTO in CI/CD with synthetic failover drills
- Document replication topology as code (Terraform, Ansible, or GitOps)

## Production Bundle

### Action Checklist
- [ ] Configure `wal_level = replica` and reserve physical replication slots on primary
- [ ] Isolate replication traffic on a dedicated network interface or VPC subnet
- [ ] Implement lag-aware read routing with explicit RPO thresholds at the application layer
- [ ] Set up automated monitoring for WAL send queue, slot age, and checkpoint duration
- [ ] Configure synchronous or semi-synchronous commit with timeout tuning matching network latency
- [ ] Run weekly consistency validation and checksum verification on all replicas
- [ ] Document and automate failover procedures with quorum checks and fencing

### Decision Matrix

| Scenario | Recommended Approach | Why | Cost Impact |
|----------|---------------------|-----|-------------|
| Low-latency read scaling | Asynchronous + lag-aware routing | Maximizes throughput; lag filtered at application layer | Low infrastructure, moderate dev effort |
| Zero-downtime failover (RPO=0) | Synchronous replication + automated promotion | Guarantees no data loss; requires low-latency network | High network cost, strict topology constraints |
| Cross-region disaster recovery | Semi-synchronous + WAL archiving + async read replica | Balances RPO with cross-AZ latency; archiving enables PITR | Moderate infrastructure, higher storage costs |
| Cost-optimized analytics | Asynchronous logical replication to separate cluster | Isolates analytical load; tolerates minutes of lag | Low primary impact, separate compute costs |

### Configuration Template

**Primary (`postgresql.conf`)**
```conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 2GB
synchronous_commit = on
synchronous_standby_names = 'replica1'
listen_addresses = '*'

Primary (pg_hba.conf)

host    replication     repl_user     10.0.0.0/8          md5
host    all             all           10.0.0.0/8          md5

Replica (postgresql.conf)

primary_conninfo = 'host=primary.internal port=5432 user=repl_user password=secret application_name=replica1'
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_timeline = 'latest'
hot_standby = on

Replica initialization

touch /var/lib/postgresql/data/standby.signal
pg_ctl -D /var/lib/postgresql/data start

Slot creation (SQL)

SELECT pg_create_physical_replication_slot('replica1_slot');

Quick Start Guide

1. Spin up primary container: docker run --name pg-primary -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16-alpine
2. Create replication user and slot: docker exec pg-primary psql -U postgres -c "CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'secret'; SELECT pg_create_physical_replication_slot('repl_slot');"
3. Initialize replica: docker run --name pg-replica -e POSTGRES_PASSWORD=secret -v pgdata-replica:/var/lib/postgresql/data -d postgres:16-alpine then copy base backup: pg_basebackup -h 127.0.0.1 -U repl_user -D /var/lib/postgresql/data --wal-method=stream --checkpoint=fast --slot=repl_slot
4. Start replica in standby mode: touch /var/lib/postgresql/data/standby.signal, set primary_conninfo in postgresql.conf, then pg_ctl -D /var/lib/postgresql/data start. Verify with SELECT * FROM pg_stat_replication; on primary.

Total setup time: ~4 minutes. Validation: replica streams WAL, lag stays under 50ms under normal load, and read routing excludes the replica if lag exceeds threshold.