Implementing Database Replication
1. Understanding Replication Types
| Type | Description |
|---|---|
| Physical (streaming WAL) | Byte-for-byte block changes |
| Logical | Row-level events; can filter/transform |
| Statement-based | Replays SQL (legacy MySQL) |
| Row-based | Replays row images (modern MySQL default) |
| Multi-master | Writes accepted on multiple nodes |
| Snapshot | Periodic full copy (Oracle materialized refresh) |
2. Configuring Replication Topology
| Topology | Use |
|---|---|
| Primary + Replicas | Read scaling, HA |
| Cascading | Replica feeds replicas (reduce primary load) |
| Multi-region | DR + local read latency |
| Active-Active | Multi-master with conflict resolution |
| Quorum cluster | Raft/Paxos (CockroachDB, Spanner) |
3. Implementing Synchronous Replication
| Property | Detail |
|---|---|
| Ack required | Replica confirms before commit |
| Postgres setting | synchronous_commit = on, synchronous_standby_names |
| Latency cost | Round-trip per commit |
| Use | Zero RPO, financial workloads |
| Risk | Lost replica blocks writes — use quorum (ANY 2 OF n) |
4. Implementing Asynchronous Replication
| Property | Detail |
|---|---|
| Ack immediate | Primary commits without waiting |
| RPO > 0 | Lose recent writes on failover |
| Throughput | Higher than sync |
| Use | Read scaling, geo-distribution |
5. Handling Replication Lag
| Detect | How |
|---|---|
| Postgres | pg_stat_replication, pg_last_wal_replay_lsn() |
| MySQL | SHOW REPLICA STATUS — Seconds_Behind_Source |
| Mitigation | Bigger replica, faster network, batch writes |
| Lag-aware reads | Skip replica if lag > threshold |
6. Managing Failover and Switchover
| Term | Detail |
|---|---|
| Failover | Unplanned: primary dies, replica promoted |
| Switchover | Planned: graceful role swap |
| Tools | Patroni, repmgr, Orchestrator, RDS Multi-AZ |
| Fencing (STONITH) | Prevent split-brain by killing old primary |
| Connection rerouting | Virtual IP, DNS, proxy |
7. Implementing Read Replicas
| Use | Detail |
|---|---|
| Scale reads | Distribute SELECT load |
| Analytics | Heavy queries off primary |
| Backups | Run pg_basebackup on replica |
| Geo-local reads | Replica in user's region |
| Routing | App router, ProxySQL, Aurora reader endpoint |
8. Resolving Replication Conflicts
| Strategy | Detail |
|---|---|
| Last-write-wins (LWW) | Timestamp tiebreak; risk of lost writes |
| CRDTs | Mathematically merge concurrent changes |
| Application-defined | Custom merge logic |
| Avoid conflicts | Partition writes by region/shard |
| Vector clocks | Detect concurrent updates |
9. Monitoring Replication Health
| Metric | Watch |
|---|---|
| Replication lag (sec/bytes) | Alert > SLO |
| WAL retained | Avoid running out of replication slots |
| Active replicas | Synchronous quorum maintained |
| Replication errors | Apply failures, conflicts |
| Failover history | Track frequency + reason |
10. Testing Disaster Recovery Scenarios
| Drill | Test |
|---|---|
| Primary loss | Failover time, data loss |
| Region loss | Cross-region promotion |
| Network partition | Split-brain prevention |
| Corruption | Restore from backup |
| Document RTO/RPO measured | Vs targets |