Module Review: Replication & HA

[!NOTE] This module explores the core principles of Review & Cheat Sheet, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

1. Key Takeaways

WAL is King: Replication is just shipping the Write-Ahead Log. If you lose the WAL, you lose the ability to replicate.
Slots Save Lives: Always use Replication Slots to prevent the Primary from deleting WAL files needed by a disconnected Standby.
Physical vs Logical:
- Physical (Streaming): Byte-for-byte copy. Whole cluster. Same major version.
- Logical: Table-level. Cross-version. Good for ETL.
Split-Brain Kills: Never automate failover without a Quorum system (like Patroni + Etcd).
Synchronous Cost: Sync replication guarantees zero data loss (RPO=0) but risks availability if the Standby dies.

2. Module Review: Replication & HA

3. Flashcards

What is the role of a Replication Slot?

(Click to flip)

It forces the Primary to retain WAL files until the Standby confirms it has received them. Without a slot, the Primary might delete old WALs, causing the Standby to break.

What is the difference between Synchronous and Asynchronous replication?

Async (Default): Primary commits locally, then sends WAL. Fast, but risk of data loss.

Sync: Primary waits for Standby confirmation before committing. Zero data loss, but slower latency.

What is Split-Brain?

A situation where two nodes both think they are the Primary and accept conflicting writes, usually caused by a network partition.

Which process handles Logical Replication decoding?

The WAL Sender process on the Publisher uses a Decoding Plugin (like pgoutput) to transform raw WAL bytes into logical change messages.

What is STONITH?

Shoot The Other Node In The Head. A mechanism to forcibly power off or isolate a failed Primary to ensure it cannot write data, preventing Split-Brain.

4. Module Review: Replication & HA

5. Cheat Sheet

Configuration (`postgresql.conf`)

Parameter	Value	Description
`wal_level`	`replica` / `logical`	Level of detail written to WAL. `logical` needed for logical rep.
`max_wal_senders`	`10`	Max concurrent connections from standbys/slots.
`max_replication_slots`	`10`	Max number of replication slots.
`hot_standby`	`on`	Allows read-only queries on the Standby.
`synchronous_standby_names`	`'node2'`	List of standbys that must acknowledge writes for Sync Rep.

SQL Commands

-- Create a Physical Slot
SELECT pg_create_physical_replication_slot('standby1_slot');

-- Create a Publication (Publisher)
CREATE PUBLICATION my_pub FOR ALL TABLES;

-- Create a Subscription (Subscriber)
CREATE SUBSCRIPTION my_sub CONNECTION 'host=...' PUBLICATION my_pub;

-- Monitor Replication Lag
SELECT * FROM pg_stat_replication;

-- Check Slots
SELECT * FROM pg_replication_slots;

6. Module Review: Replication & HA