DEV Community

SoftwareDevs mvpfactory.io
SoftwareDevs mvpfactory.io

Posted on Originally published at mvpfactory.io

PostgreSQL Write-Ahead Log Shipping for Cross-Region Failover

---
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.

Enter fullscreen mode Exit fullscreen mode


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:

Enter fullscreen mode Exit fullscreen mode


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.

Enter fullscreen mode Exit fullscreen mode


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:**

Enter fullscreen mode Exit fullscreen mode


bash
psql -c "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_is_in_recovery();"


**Step 4.3 — Promote the standby:**

Enter fullscreen mode Exit fullscreen mode


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)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)