Real-Time Protection: Database Replication

The Limitation of Backups

Problem with daily backups: Lose up to 24 hours of data.

Example:

Last backup: Yesterday 11 PM
Database crashes: Today 10 PM
Data loss: 23 hours of user activity

For critical systems, this is unacceptable.

Continuous Redundancy: The Solution

Replication: Maintain live, continuously updated copy of database.

Every write to main database immediately copies to replica.

Result: Replica is always up-to-date (within seconds).

Master-Replica Architecture

Master Database (Primary):

Handles all writes. All reads (optionally). The "source of truth."

Replica Database (Standby):

Receives copies of all data changes. Can handle reads (optionally). Ready to become master if needed.

Visualization:

Application → Master Database (writes)
              ↓ replication
              Replica Database (standby)

How Replication Works

Synchronous Replication:

1. Application writes data to Master
2. Master writes to its disk
3. Master sends data to Replica
4. Replica writes to its disk
5. Replica confirms: "Got it"
6. Master confirms to Application: "Write successful"

Write completes only after replica confirms.

Advantage: Zero data loss. Master and replica always identical.

Disadvantage: Slower writes. Network latency to replica adds delay.

Asynchronous Replication

1. Application writes to Master
2. Master writes to its disk
3. Master confirms to Application: "Write successful" (immediately)
4. Master sends data to Replica (in background)
5. Replica writes to its disk (eventually)

Write completes immediately. Replication happens after.

Advantage: Fast writes. No latency penalty.

Disadvantage: Replica slightly behind. Master crashes before replicating? Recent writes lost.

Choosing Replication Mode

Financial transactions: Synchronous. Cannot lose money data.

Social media posts: Asynchronous. Losing last few seconds acceptable.

Trade-off: Data safety vs performance.

The Standby Role

Critical: Replica is standby only. Not serving user traffic.

Why?

Its only job: Stay synchronized with master. Be ready to take over instantly.

Serving user reads from replica? Increases load. Slows replication. Defeats the purpose.

Use separate read replicas for that (different pattern, different lesson).

Failover: When Replica Becomes Master

Disaster scenario: Master database crashes. Disk failure. Unrecoverable.

Failover process:

1. Detect master is down (health checks)
2. Promote replica to master
3. Point application at new master
4. Application continues writing to new master

Downtime: Seconds to minutes (depending on automation).

Data loss: None (synchronous) or minimal (asynchronous).

Real-World Replication Example

PostgreSQL Streaming Replication:

Master sends WAL (Write-Ahead Log) stream to replica. Replica applies changes in real-time.

Configuration:

-- On master
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 3;

-- On replica
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=master-db port=5432 dbname=mydb'
PUBLICATION my_publication;

Result: Replica stays synchronized. Failover ready.

Multi-Replica Setup

Production pattern: Multiple replicas.

Application → Master
              ↓ replication
              Replica 1 (US-East)
              Replica 2 (US-West)
              Replica 3 (Europe)

Master crashes: Promote closest replica. Other replicas sync from new master.

Maximum redundancy: Even if two replicas fail, third survives.

Monitoring Replication Lag

Lag: How far behind replica is from master.

Synchronous: Lag = 0 seconds (by definition).

Asynchronous: Lag = seconds to minutes.

Dangerous lag: Hours behind. Replica not keeping up.

Causes: Network issues. Replica overloaded. Configuration problems.

Monitor replication lag constantly. Alert if exceeds threshold (e.g., 60 seconds).

Cost of Continuous Redundancy

Infrastructure: 2-3x database servers.

Storage: 2-3x disk space.

Network: Bandwidth for replication traffic.

Worth it? Absolutely. Compare to cost of catastrophic data loss.

Companies that skip replication: Eventually learn expensive lesson.