The PITR Workflow

pg_basebackup -R creates standby.signal and writes the connection info to postgresql.auto.conf, making the recovery instance ready to connect as a standby — useful for "restore and promote" workflows.

---

Failover: What Actually Happens

Failover — promoting a standby to primary — is conceptually simple but operationally complex. Here is the precise sequence of events.

Manual Failover

When promoted:

1. The startup process stops waiting for WAL from the old primary
2. A new timeline is started (timeline ID increments by 1)
3. A timeline history file is written to pg_wal/ recording when the timeline changed
4. The standby becomes a writable primary on the new timeline

The application must be updated to point to the new primary's address. This is why automatic failover tooling exists — manual failover with application reconfiguration is too slow for most production SLAs.

What Happens to In-Flight Transactions

At the moment of failover, there are potentially two classes of transactions on the old primary:

Committed but not yet replicated (async mode): These transactions are committed on the old primary's WAL but the standby has not received them. After promotion, these transactions are permanently lost. Clients received COMMIT acknowledgement for data that no longer exists.

This is why synchronous_commit = remote_apply or at minimum remote_write is required for financial workloads — it ensures the standby has the data before the client is told the commit succeeded.

In-progress at failover: These transactions were not committed. The new primary's MVCC state correctly shows them as aborted (their xmin is not in the committed set). No data loss, no inconsistency.

Timeline Divergence

After promotion, the new primary writes new WAL on timeline 2. If the old primary comes back online (e.g., it was a network partition, not a hardware failure), it has WAL on timeline 1 that the new primary does not have — split brain.

Postgres handles this via timeline history files. A standby connecting to the new primary will detect the timeline switch and refuse to replay WAL that conflicts with the new timeline. Properly configured tooling ensures the old primary is fenced (prevented from accepting writes) before the standby is promoted.

---

Automatic Failover: Patroni

Patroni is the standard open-source HA solution for Postgres. It runs alongside each Postgres instance and uses a distributed consensus store (etcd, Consul, or ZooKeeper) to coordinate leader election.

Architecture

Each Patroni agent:

• Holds a leader lease in etcd (renewed every ttl seconds, default 30s)
• Monitors the local Postgres instance health
• If the primary's agent fails to renew its lease → other agents race to acquire it → winner promotes its standby

The Fencing Problem

Before promoting, Patroni must ensure the old primary is not still running and accepting writes. This is done via:

1. pg_ctl stop -m fast — Patroni attempts to stop the old primary before promoting
2. Watchdog — a kernel module (softdog or hardware watchdog) that reboots the primary node if Patroni fails to stop Postgres
3. STONITH (Shoot The Other Node In The Head) — cloud API calls to forcibly terminate the instance

Without fencing, split-brain is possible: both nodes accept writes, data diverges, and you have a corrupt cluster.

Patroni Configuration Snippet

maximum_lag_on_failover is critical: it prevents promoting a standby that is far behind the primary from becoming the new primary with stale data. A standby more than 1MB behind will not be promoted — Patroni waits for it to catch up or picks another standby.

---

Read Replicas: What Is Safe to Route There

A standby in hot_standby mode accepts read queries. But read replicas have consistency properties that must be understood before routing traffic to them.

The Read-After-Write Problem

If apply_lag > 0, a client that just wrote to the primary may not see its own write when reading from the standby. For most web applications, this is an invisible UX problem: a user updates their profile, is redirected to the profile page, and sees the old version.

Solutions:

1. Route writes and immediately-following reads to primary

2. Use synchronous_commit = remote_apply for writes that must be immediately readable on standbys. The commit does not return until the standby has applied it — guaranteed read-after-write.

3. Read your own writes via session pinning — route all requests in a session to the same backend until a delay expires.

Queries That Are Always Unsafe on Standbys

The Standby Query Cancellation Problem

Long-running queries on standbys can be cancelled by the recovery process when it needs to apply a conflicting WAL record (e.g., a vacuum that reclaims pages the standby query is reading).

With hot_standby_feedback = on (covered above), the primary's autovacuum avoids reclaiming tuples still needed by standby queries — but this delays autovacuum on the primary.

For analytics replicas running long queries: set max_standby_streaming_delay = -1 (never cancel) and accept that the standby may fall behind during heavy analytical workloads.

---

Logical Replication

Physical replication copies every byte. Logical replication copies row-level changes decoded from WAL — it is table-selective, schema-independent, and can replicate to a different Postgres version or even a different database system.

When Logical Replication Is the Right Tool

• Zero-downtime major version upgrades: set up logical replication to a new-version standby, let it catch up, switch application over
• Selective replication: replicate only specific tables to a reporting database
• Cross-database replication: replicate between different databases on the same or different servers
• CDC consumers: Debezium, custom consumers via pg_logical or the replication protocol

The Logical Replication Write Cost

wal_level = logical (required for logical replication) adds modest WAL overhead on its own (mainly catalog xmin tracking needed for logical decoding). The overhead that actually gets large — full old-row values written to WAL on every UPDATE/DELETE — comes from setting REPLICA IDENTITY FULL on a table, which is only necessary when the table lacks a usable primary key or unique index for the replication target to match rows against. With a normal primary key and default replica identity, expect the modest wal_level=logical overhead, not a blanket 10–30% figure — that range is realistic specifically for tables running under REPLICA IDENTITY FULL.

---

CAP Theorem Applied to Postgres HA

CAP theorem states that a distributed system can guarantee at most two of: Consistency, Availability, Partition tolerance.

All production systems must tolerate network partitions (P). The real choice is between consistency (C) and availability (A) during a partition.

Postgres Default Configuration: CP

In the default async configuration with automatic failover (Patroni):

During normal operation: consistent reads and writes, highly available.

During a network partition:

• If the primary cannot reach etcd, it cannot renew its lease → Patroni demotes it (stops accepting writes) rather than risk split-brain
• The cluster is unavailable for writes until the partition heals or a standby is promoted
• C is preserved (no split-brain), A is sacrificed (writes blocked)

This is Postgres's default choice: consistency over availability during partitions.

Synchronous Replication: Stronger C, Weaker A

With synchronous_commit = remote_apply and synchronous_standby_names = 'ANY 1 (s1, s2)':

• A commit only succeeds if at least one standby confirms it
• If all standbys become unreachable: primary blocks on all commits until they reconnect
• Stronger durability guarantee (no data loss on failover) at the cost of availability during standby outages

Async Replication: Weaker C, Stronger A

With synchronous_commit = off and no synchronous_standby_names:

• Commits succeed regardless of standby state
• If primary fails and standby is promoted: committed transactions in the last 200ms may be lost
• Available during partial outages, but weaker durability

The Practical Matrix

For most production systems: quorum remote_apply with 2+ standbys is the correct balance. No data loss on failover, tolerates one standby failure without blocking writes.

---

Monitoring: The Queries That Matter

---

The Production Incident: Split-Brain From an Incomplete Fence

Context: A blockchain indexer with Patroni + etcd, primary and two standbys across two availability zones.

What happened:

A network partition isolated the primary from both etcd and the standbys. From the primary's perspective, etcd was unreachable — it could not renew its leader lease. Patroni's configured behavior: demote the primary by stopping Postgres after ttl seconds.

However, the watchdog device (/dev/watchdog) was not properly configured — watchdog_mode was set to automatic instead of required. When Patroni tried to use the watchdog for fencing, it silently skipped it (automatic mode treats watchdog as optional).

Patroni stopped Postgres on the primary via pg_ctl stop. But the stop command timed out (a long-running VACUUM was holding the shutdown). Patroni logged the timeout and proceeded to exit — but Postgres was still running.

Meanwhile, etcd detected the expired lease. One standby won the election and promoted itself.

For approximately 90 seconds, both nodes were accepting writes:

• The old primary: still running, accepting connections from applications that had not yet received the DNS failover
• The new primary: accepting writes from applications that had received the DNS failover

Both wrote conflicting data to the transactions table. After the network partition healed, the conflict was discovered during reconciliation — rows with duplicate block_height + sender combinations existed in different timelines.

The resolution:

The old primary's timeline diverged at LSN 3/A2F1C820. Every write after that point on the old primary was discarded. The new primary's timeline was authoritative. 90 seconds of writes on the old primary — approximately 180,000 transactions at 2,000 TPS — were permanently lost.

The fixes:

The lessons:

1. watchdog_mode = required — never automatic in production
2. pg_ctl_timeout prevents indefinite hanging during failover
3. STONITH via cloud API is the reliable fence for cloud deployments
4. Test your failover. Regularly. Chaos engineering for databases is not optional.

---

Summary

---

Module 11 steps back from Postgres-specific mechanics to answer the question engineers argue about constantly but rarely reason through rigorously: when is Postgres the right database, and when is it not?

Next: Module 11 — SQL vs NoSQL: An Engineer's Framework, Not a Marketing Debate →

---