Database Replication

How a backup database stays perfectly in sync with the primary.

The idea

If your Primary database machine catches fire, your company is dead unless you have a Replica ready to take over. But how does the Replica get the data? It doesn't run the exact SQL queries over again. Instead, it streams the Write-Ahead Log (WAL). The WAL is a literal, binary list of every single disk-level change the Primary ever made. The Replica downloads this stream and plays it back on its own hard drive, recreating the exact state of the Primary byte-for-byte.

How it works (Asynchronous Streaming)

In standard Asynchronous Replication, the Primary saves the data locally, immediately tells the user "Success!", and then streams the WAL to the Replica in the background. The Replica is usually a few milliseconds behind the Primary. This makes writes very fast, but introduces a tiny window of vulnerability.

# PostgreSQL Replication Setup (Simplified)
# On Primary:
wal_level = replica
max_wal_senders = 5

# On Replica:
primary_conninfo = 'host=primary_ip user=repl_user'
# The Replica connects via TCP, acts like a client,
# and asks the Primary to stream WAL bytes forever.

Cost

The alternative is Synchronous Replication. The Primary writes the data, streams it to the Replica, waits for the Replica to confirm it saved it, and only then tells the user "Success!". This guarantees zero data loss if the Primary dies, but makes every single write 2x to 10x slower because it has to wait for a network round-trip.

Watch out for

• Network Partitions (Split Brain): If the network cable between the Primary and Replica gets cut, they can't communicate. In Asynchronous mode, the Primary keeps accepting writes, and the Replica falls further and further behind. If your automated failover system panics and promotes the Replica to Primary while the original Primary is still alive, you get "Split Brain"—two databases accepting conflicting writes.