Lesson 10: Database Replication - Master-Slave & Master-Master

System Architecture: From Zero to Hero

Part 3: Database Architecture & Data Management

xdev.asia

Introduction

A single database server is Single Point of Failure. If it crashes, the entire system crashes. Database Replication solves this problem by copying data to multiple servers.

---

1. Why do we need Replication?

Goal	How Replication helps
High Availability	Master fails → Slave takes over
Read Scaling	Distribute reads across multiple replicas
Data Locality	Replica near the user (reduces latency)
Backup	Replica as a "hot backup"
Analytics	Run heavy queries on replica, no impact on production

---

2. Synchronous vs Asynchronous Replication

2.1 Synchronous

Client → Master: INSERT INTO orders (...)
Master → Replica 1: "Replicate this!"
Replica 1 → Master: "Done!"
Master → Replica 2: "Replicate this!"
Replica 2 → Master: "Done!"
Master → Client: "INSERT success"

Đảm bảo: Tất cả replicas có data trước khi confirm
Nhược điểm: Chậm (phải đợi tất cả replicas)

2.2 Asynchronous

Client → Master: INSERT INTO orders (...)
Master → Client: "INSERT success"  ← Return ngay!
Master → Replica 1: "Replicate this" (async)
Master → Replica 2: "Replicate this" (async)

Ưu điểm: Nhanh (không đợi replicas)
Rủi ro: Master crash trước khi replicate → data loss

2.3 Semi-Synchronous

Client → Master: INSERT
Master → Replica 1: Sync (đợi 1 replica confirm)
Master → Client: "Success"
Master → Replica 2: Async (replicate sau)

→ Cân bằng giữa durability và performance
→ PostgreSQL: synchronous_commit = on (1 replica)

---

3. Master-Slave (Primary-Replica)

3.1 Architecture

                    Writes
  Client ───────────────────► Master (Primary)
                                │
                      Replication│ Stream
                    ┌───────────┼───────────┐
                    ▼           ▼           ▼
                ┌───────┐  ┌───────┐  ┌───────┐
     Reads ────►│Slave 1│  │Slave 2│  │Slave 3│
                │(Read) │  │(Read) │  │(Read) │
                └───────┘  └───────┘  └───────┘

3.2 Failover Process

Normal:
  App ──write──► Master ──replicate──► Slave 1, 2, 3
  App ──read───► Slave 1, 2, 3

Master fails:
  1. Detect: Health check fails (timeout 30s)
  2. Elect: Chọn Slave có data mới nhất → Promote
  3. Reconfigure: Các Slaves khác trỏ về new Master
  4. Update: App connection string → new Master

  App ──write──► Slave 1 (now Master)
  App ──read───► Slave 2, 3

Timeline:
  T=0:    Master crash
  T=30s:  Detected (health check timeout)
  T=35s:  Slave 1 promoted
  T=40s:  Connections reconfigured
  → Downtime: ~40 giây

3.3 Replication Lag

Vấn đề:
  T=0:   User update profile (write → Master)
  T=0.1: User refresh page (read → Slave)
  Slave chưa có data mới → User thấy data cũ!

  "Tôi vừa update avatar mà sao vẫn thấy avatar cũ?"

Solution:

1. Read-after-write consistency:
   Sau khi write, read từ Master (trong vài giây)

2. Monotonic reads:
   User luôn đọc từ cùng 1 Slave

3. Causal consistency:
   Track version, đảm bảo read ≥ write version

4. Tăng tốc replication:
   Parallel replication, minimize network latency

---

4. Master-Master (Multi-Primary)

4.1 Architecture

  Client A ──write──► Master 1 ◄──sync──► Master 2 ◄──write── Client B
                        │                    │
                   Read + Write         Read + Write

4.2 Conflict Resolution

Vấn đề:
  T=0: Master 1: UPDATE user SET name='John'
  T=0: Master 2: UPDATE user SET name='Jane'
  → Conflict! Tên nào đúng?

Strategies:
  1. Last Write Wins (LWW):
     So sánh timestamp, write mới nhất thắng
     Đơn giản nhưng có thể mất data

  2. Application-level resolution:
     App quyết định merge strategy
     Phức tạp nhưng chính xác

  3. CRDT (Conflict-free Replicated Data Types):
     Data structure tự động merge
     Ví dụ: Counter → Tổng tất cả increments

4.3 Split-Brain Problem

Vấn đề:
  Network partition giữa Master 1 và Master 2
  Cả 2 đều nhận writes → Data diverge

  Master 1: user.balance = 1000 - 500 = 500
  Master 2: user.balance = 1000 - 300 = 700
  
  Network recovered: balance = 500? 700? 200? 

Giải pháp:
  - Quorum-based writes (majority phải đồng ý)
  - Fencing tokens (chỉ 1 master active)
  - External coordination (Zookeeper, etcd)

---

5. PostgreSQL Streaming Replication

5.1 Setup Master (postgresql.conf)

# Master configuration
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
synchronous_standby_names = 'replica1'

5.2 Setup Replica

# Tạo base backup từ Master
pg_basebackup -h master-host -D /var/lib/postgresql/data \
  -U replication -Fp -Xs -P -R

# -R: Tạo standby.signal và postgresql.auto.conf tự động

5.3 Monitoring Replication

-- Trên Master: kiểm tra replicas
SELECT client_addr, state, sent_lsn, write_lsn,
       flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- Trên Replica: kiểm tra lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

---

6. Automated Failover Tools

Tools	Database	Description
Patroni	PostgreSQL	HA cluster management with etcd/ZooKeeper
PgBouncer	PostgreSQL	Connection pooler + failover
Orchestrator	MySQL	Topology management + auto failover
MHA	MySQL	Master High Availability manager
Redis Sentinel	Redis	Auto failover for Redis

---

Summary

Topology	Writes	Reads	Complexity	Use Case
Singles	1 server	1 server	Low	Development, small apps
Master-Slave	Master only	Master + Slaves	Medium	Read-heavy apps
Master-Master	Both	Both	High	Multi-region, high write

---

Exercises

1. Replication Design: E-commerce has 90% reads, 10% writes, 50K QPS. Design replication topology (how many masters, how many slaves?).

2. Replication Lag: The system has replication lag of 500ms. User just changed password and logged in again. If the login query comes to the Slave (no new password yet), the user cannot login. Solution design.

3. Failover Plan: Write failover runbook for PostgreSQL Master-Slave cluster. Including: detection, decision, execution, verification.