--- title: "PostgreSQL WAL Shipping: Cross-Region Failover That Survives a Primary Outage" published: true description: "Master PostgreSQL WAL shipping for cross-region failover. Learn segment archiving vs streaming replication, replication slot hazards, lag monitoring with pg_stat_replication, and the promotion runbook that minimizes data loss." tags: postgresql, architecture, api, performance canonical_url: https://blog.mvpfactory.co/postgresql-wal-shipping-cross-region-failover --- ## What We Will Build By the end of this workshop, you will have a production-grade mental model — and working SQL and shell snippets — for PostgreSQL cross-region failover using WAL shipping. We will cover segment archiving versus streaming replication, the replication slot disk bomb that has taken down real production systems, standby lag monitoring with `pg_stat_replication`, and a step-by-step promotion runbook you can run at 3 AM on a Saturday. ## Prerequisites - PostgreSQL 13+ (some commands work on 12+, noted inline) - A standby replica configured with `primary_conninfo` - Basic familiarity with `postgresql.conf` and `pg_ctl` - Access to `psql` on both primary and standby hosts --- ## Step 1 — Understand the Replication Methods Side by Side Let me show you a pattern I use in every project: before choosing a replication topology, map your RPO requirements against the actual mechanics. | Method | Typical RPO | Lag Visibility | Disk Pressure | Complexity | |---|---|---|---|---| | WAL Segment Archiving | Minutes (1 segment = 16 MB) | None (pull-based) | Archive storage only | Low | | Streaming Replication | Sub-second | `pg_stat_replication` | Primary disk (slots) | Medium | | Streaming + Archive fallback | Sub-second (normal), minutes (reconnect) | Full | Both | Medium-High | Segment archiving ships completed 16 MB WAL files via `archive_command`. On an idle primary, a segment may not rotate for minutes — meaning your cross-region standby is perpetually 10–15 minutes behind during low-write periods. Streaming replication sends WAL records as they are written, keeping standby lag typically under 100 ms on a healthy inter-region link. **Production guidance:** use streaming replication as the primary channel, with segment archiving as a fallback and for point-in-time recovery. Configure `recovery_min_apply_delay` only if you need a delayed standby for logical corruption protection. --- ## Step 2 — Defuse the Replication Slot Time Bomb Here is the gotcha that will save you hours — or your entire production database.
sql
-- Check active slots and their retained WAL
SELECT slot_name, active, restart_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
If your standby disconnects — network partition, maintenance, crash — a replication slot on the primary will hold every WAL segment from that point forward. On a write-heavy system, this means gigabytes per hour accumulating until the primary's disk fills and PostgreSQL hard-stops accepting writes. Fix it immediately:
ini
postgresql.conf
max_slot_wal_keep_size = 10GB # PostgreSQL 13+
This caps retained WAL per slot. The trade-off: if the standby falls behind more than this limit, it must be rebuilt from a base backup. That is acceptable. A primary with a full disk is not. --- ## Step 3 — Monitor Lag With the Right Dimensions The docs do not mention this, but alerting on replication lag in bytes alone will mislead you. Time-based lag tells you exactly how much data you lose if the primary fails right now.
sql
SELECT client_addr,
state,
write_lag,
flush_lag,
replay_lag,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS total_lag_bytes
FROM pg_stat_replication;
Alert thresholds I use in production: - `replay_lag > 30s` → warning (network degradation) - `replay_lag > 120s` → critical (standby falling behind) - `total_lag_bytes > 500MB` → critical (approaching slot retention limit) --- ## Step 4 — Run the Promotion Runbook Here is the minimal setup to get this working under pressure. Practice this quarterly — failovers that have never been rehearsed will surprise you at the worst possible moment. **Step 4.1 — Confirm the primary is truly unreachable**, not just partitioned from your monitoring host. Use at least two independent probes (application health check + direct TCP) before proceeding. **Step 4.2 — Check the standby's recovery position:**
bash
psql -c "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_is_in_recovery();"
**Step 4.3 — Promote the standby:**
bash
PostgreSQL 12+
pg_ctl promote -D /var/lib/postgresql/data
Or via signal file for older versions
touch /var/lib/postgresql/data/promote
**Step 4.4 — Update connection routing** — DNS, pgBouncer, or your service mesh — to point to the new primary. **Step 4.5 — Fence the old primary.** Before it comes back online, ensure it cannot accept writes. Revoke network access or shut it down to prevent split-brain. The data loss window equals the gap between `pg_last_wal_receive_lsn()` and the primary's last written LSN at failure time. With streaming replication and `synchronous_standby_names` set to at least `FIRST 1`, this window shrinks to zero — at the cost of write latency. --- ## Gotchas **Your standby is a data contract consumer, not a passive mirror.** Treat it as a first-class service dependent on your primary's availability guarantees. That framing — borrowed from Harrer and Wider's work on data contracts — changes your topology decisions from day one. **An uncapped slot on a disconnected standby is a time bomb.** This is not a theoretical risk. It has taken down production systems. Set `max_slot_wal_keep_size` before you need it. **Lag bytes hide the latency story.** A 500 MB lag could represent 2 seconds or 20 minutes depending on write volume. Always alert on `replay_lag` in seconds alongside bytes. **Never promote on a single probe failure.** Network partitions can isolate your monitoring host while the primary remains healthy. Two independent probes before any promotion decision. **Test promotion completion time.** The goal is under five minutes from decision to applications reconnecting cleanly. If you have never measured it, you do not know your actual RTO. --- ## Conclusion WAL streaming replication gives you sub-second RPO, but it ships with operational responsibility: replication slot retention hazards, time-based lag monitoring, and a promotion runbook that must be practiced, not just documented. Three things to do before the next outage finds you: 1. Set `max_slot_wal_keep_size = 10GB` in `postgresql.conf` today. 2. Add `replay_lag`-based alerts in seconds alongside your byte-based alerts. 3. Schedule a quarterly promotion drill — confirm it completes in under five minutes, applications reconnect cleanly, and the old primary can be safely fenced. **Further reading:** - [PostgreSQL Replication Slots documentation](https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS) - [pg_stat_replication reference](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW) - [High Availability, Load Balancing, and Replication — PostgreSQL docs](https://www.postgresql.org/docs/current/high-availability.html) 

