PostgreSQL Replication — Streaming, Logical, and Read Replicas

Streaming replication

The primary ships WAL (Write-Ahead Log) segments to standbys in near real-time. Standbys replay the WAL and stay current. Failover is fast. This is the standard HA pattern.

Logical replication

Replicates logical changes (INSERT/UPDATE/DELETE) rather than physical WAL bytes. Allows cross-version replication, partial replication (specific tables), and replication to non-PostgreSQL targets.

Read replicas

Streaming standbys in hot standby mode can serve read-only queries. Route read-heavy analytics queries to replicas to offload the primary. Use a connection pooler (PgBouncer or pgpool-II) to route intelligently.

Replication lag

Monitor replication lag: SELECT * FROM pg_stat_replication; shows sent_lsn vs replay_lsn. Alert when lag exceeds your RPO (Recovery Point Objective). Lag spikes under heavy write load — size the network and replica hardware accordingly.

Patroni for automated failover

Patroni manages primary election and failover using a distributed consensus store (etcd, Consul, ZooKeeper). It is the standard HA solution for self-hosted Postgres clusters.