Database Replication

Cross-database replication reference — beginner to advanced.

1. Why Replication

2. Sync vs Async — Latency and RPO

Every replication design is really a choice about where on this spectrum a given write sits — how much latency you're willing to pay on the critical path in exchange for how much data you're willing to lose if the primary dies one instant after acknowledging the write.

Semi-sync is the practical middle ground production MySQL clusters actually run: it avoids async's silent data-loss window without paying sync's full apply-confirmation latency on every write — the trade is a replica that's formally "ack'd" a write it may not have replayed yet.

Diagram: Write Path

sequenceDiagram
    participant C as Client
    participant P as Primary
    participant R as Replica

    rect rgb(60, 45, 20)
    Note over C,R: Async replication — fire and forget
    C->>P: WRITE
    P->>P: Write to local disk (WAL/binlog)
    P-->>C: ACK (before the replica has seen it)
    P--)R: Ship WAL/binlog (background, no wait)
    end

    rect rgb(30, 50, 65)
    Note over C,R: Semi-sync replication (MySQL rpl_semi_sync)
    C->>P: WRITE
    P->>P: Write to local disk
    P->>R: Ship WAL/binlog
    R-->>P: Acknowledge receipt only (relay log, not yet applied)
    P-->>C: ACK
    R->>R: Apply asynchronously afterward
    end

    rect rgb(30, 65, 45)
    Note over C,R: Sync replication
    C->>P: WRITE
    P->>P: Write to local disk
    P->>R: Ship WAL/binlog
    R->>R: Apply
    R-->>P: Confirm received AND applied
    P-->>C: ACK
    end

Try It Yourself: Live Replication Lag

The diagram above shows the write path once. This one's live — flip between sync and async, hammer the Write button, and watch the offsets either stay glued together or drift apart. Async's queue is exactly the window of acknowledged-but-not-yet-durable-on-a-second-node writes that a "Kill Master" would expose as gone.

3. Physical vs Logical Replication

4. PostgreSQL Replication

Streaming Replication (Physical)

-- On primary: postgresql.conf
wal_level = replica
max_wal_senders = 5
hot_standby = on

-- Create replication user
CREATE USER replicator REPLICATION LOGIN PASSWORD 'secret';

# Bootstrap replica
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R

recovery.conf (PG < 12) or standby.signal + postgresql.conf (PG ≥ 12):

primary_conninfo = 'host=primary user=replicator'

Replication Slots

Slots prevent the primary from discarding WAL until the replica has consumed it. Prevents replication gaps but risks disk fill if replica goes offline.

-- Create
SELECT pg_create_physical_replication_slot('replica1');

-- Check
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

-- Drop if replica is gone (or disk fills)
SELECT pg_drop_replication_slot('replica1');

Logical Replication

-- Publisher
ALTER SYSTEM SET wal_level = logical;
CREATE PUBLICATION mypub FOR TABLE orders, users;

-- Subscriber (different cluster or version)
CREATE SUBSCRIPTION mysub
  CONNECTION 'host=primary dbname=app user=replicator'
  PUBLICATION mypub;

synchronous_commit Settings

-- Per-transaction override
SET LOCAL synchronous_commit = remote_apply;

5. MySQL Replication

Binary Log Formats

-- Enable GTID replication
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- Connect replica
CHANGE MASTER TO
  MASTER_HOST='primary',
  MASTER_USER='replicator',
  MASTER_PASSWORD='secret',
  MASTER_AUTO_POSITION=1;   -- GTID-based, no binlog coords needed

START REPLICA;
SHOW REPLICA STATUS\G

Semi-Sync

-- On primary
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- On replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

Multi-Source Replication

-- Replica receiving from two primaries
CHANGE MASTER TO MASTER_HOST='primary1', ... FOR CHANNEL 'source1';
CHANGE MASTER TO MASTER_HOST='primary2', ... FOR CHANNEL 'source2';
START REPLICA FOR CHANNEL 'source1';
START REPLICA FOR CHANNEL 'source2';

6. MongoDB Replication

Replica Set Architecture

graph TD
    classDef primary fill:#e74c3c,stroke:#c0392b,color:#fff
    classDef secondary fill:#3498db,stroke:#2471a3,color:#fff

    P["Primary<br/>accepts all writes,<br/>appends to local.oplog.rs"]:::primary
    S1["Secondary 1<br/>tails oplog, replays entries"]:::secondary
    S2["Secondary 2<br/>tails oplog, replays entries"]:::secondary

    P -->|oplog stream| S1
    P -->|oplog stream| S2

    subgraph SET["3-member set — odd count avoids tied elections"]
        P
        S1
        S2
    end

Oplog

• Capped collection (local.oplog.rs) on every member
• Operations are idempotent; secondaries replay the oplog
• Oplog window = how far behind a secondary can fall before needing full resync

// Check oplog window
rs.printReplicationInfo()

// Check replication lag
rs.printSecondaryReplicationInfo()

Election Algorithm

1. Any member that hasn't heard from primary within electionTimeoutMillis (10 s) calls an election
2. Candidate requests votes; wins if it has the most up-to-date oplog and majority of votes
3. Raft-inspired; uses term numbers to prevent stale leaders

Write Concern

db.orders.insertOne(doc, { writeConcern: { w: "majority", j: true, wtimeout: 3000 } })
// w:1        — primary ack only
// w:"majority" — majority of voting members
// w:"all"    — all replica set members
// j:true     — journaled to disk

Read Preference

7. Redis Replication

PSYNC2 Protocol

sequenceDiagram
    participant R as Replica
    participant P as Primary

    R->>P: PSYNC <replid> <offset>
    alt offset still covered by repl-backlog (partial resync possible)
        P-->>R: +CONTINUE
        P->>R: stream only the commands missing since <offset>
    else replid mismatch, first connection, or offset fell off the backlog
        P-->>R: +FULLRESYNC <replid> <offset>
        P->>R: RDB snapshot (entire dataset)
        P->>R: stream commands received while the snapshot was transferring
    end

• repl-backlog-size (default 1 MB): ring buffer on primary. If replica lag exceeds backlog, full resync required.
• repl-backlog-ttl: how long primary keeps backlog after all replicas disconnect.

# Check replication state
redis-cli INFO replication

Sentinel (Automatic Failover)

sentinel monitor mymaster 127.0.0.1 6379 2   # quorum = 2
sentinel down-after-milliseconds mymaster 5000
sentinel failover-timeout mymaster 10000

3+ Sentinel processes vote; when quorum agrees primary is down, one Sentinel orchestrates failover: promotes best replica, reconfigures others.

Redis Cluster (Gossip)

• 16384 hash slots distributed across masters
• Each master has 1+ replicas
• Nodes exchange gossip every second; CLUSTER FAILOVER or auto-failover when master is unreachable for cluster-node-timeout

graph LR
    classDef master fill:#2980b9,stroke:#1f618d,color:#fff
    classDef replica fill:#7f8c8d,stroke:#616a6b,color:#fff

    M1["Master A<br/>slots 0–5460"]:::master
    M2["Master B<br/>slots 5461–10922"]:::master
    M3["Master C<br/>slots 10923–16383"]:::master
    R1["Replica of A"]:::replica
    R2["Replica of B"]:::replica
    R3["Replica of C"]:::replica

    M1 -.->|"gossip"| M2
    M2 -.->|"gossip"| M3
    M3 -.->|"gossip"| M1
    M1 -->|"async replication"| R1
    M2 -->|"async replication"| R2
    M3 -->|"async replication"| R3

Gossip is how every node learns cluster shape and health without a central coordinator — each node's view of "who owns which slots" and "who's unreachable" converges through peer-to-peer chatter, which is also why cluster-node-timeout (how long a master must be unreachable before its replica is promoted) is a cluster-wide setting, not per-node.

8. Kafka Replication

Key Concepts

• ISR (In-Sync Replicas): set of replicas that are caught up to the leader within replica.lag.time.max.ms
• LEO (Log End Offset): next offset to be written on each replica
• HW (High Watermark): highest offset acknowledged by all ISR members — consumers only see up to HW

Leader LEO:   [0,1,2,3,4,5]
Replica1 LEO: [0,1,2,3,4]    ← in ISR (within lag threshold)
Replica2 LEO: [0,1,2]        ← lagging, removed from ISR

HW = 4  (min LEO across ISR)

Producer Acks

acks=0          # fire-and-forget, possible loss
acks=1          # leader ack only
acks=all        # all ISR must ack — use with min.insync.replicas
min.insync.replicas=2

Topic Configuration

kafka-topics.sh --create --topic orders \
  --replication-factor 3 \
  --partitions 12 \
  --config min.insync.replicas=2

9. Consensus Algorithms

Raft

Three roles: Leader, Follower, Candidate.
Term numbers act as logical clocks.

Leader election:

1. Follower times out (no heartbeat) → becomes Candidate, increments term
2. Requests votes from all nodes (includes last log index+term)
3. Node grants vote if: it hasn't voted this term AND candidate log is at least as up-to-date
4. Candidate wins majority → becomes Leader, starts sending heartbeats

Log replication:

1. Client sends command to Leader
2. Leader appends to local log, sends AppendEntries RPC to followers
3. Once majority acknowledge, Leader commits entry, applies to state machine, responds to client
4. Next heartbeat informs followers of commit index; they apply too

sequenceDiagram
    participant N1 as Node A
    participant N2 as Node B
    participant N3 as Node C (times out first)
    participant CL as Client

    rect rgb(50, 35, 60)
    Note over N1,N3: Phase 1 — Leader election (term 5)
    N3->>N3: Election timeout fires — becomes Candidate, term 4→5
    N3->>N1: RequestVote(term=5, lastLogIndex, lastLogTerm)
    N3->>N2: RequestVote(term=5, lastLogIndex, lastLogTerm)
    N1->>N1: not yet voted this term AND candidate log ≥ mine → grant
    N2->>N2: not yet voted this term AND candidate log ≥ mine → grant
    N1-->>N3: VoteGranted(term=5)
    N2-->>N3: VoteGranted(term=5)
    Note over N3: Majority (2 of 3) — becomes Leader, starts heartbeats
    end

    rect rgb(30, 50, 65)
    Note over CL,N3: Phase 2 — Log replication
    CL->>N3: Command: SET x=1
    N3->>N3: Append to local log (uncommitted)
    N3->>N1: AppendEntries(term=5, entry, leaderCommit)
    N3->>N2: AppendEntries(term=5, entry, leaderCommit)
    N1->>N1: Append entry to local log
    N2->>N2: Append entry to local log
    N1-->>N3: Success
    N2-->>N3: Success
    Note over N3: Majority acknowledged — commit entry, apply to state machine
    N3-->>CL: Result
    Note over N1,N2: Next heartbeat carries new commit index — followers apply too
    end

Try It Yourself: Live Leader Election

The stepper above walks through one scripted election. This one's live — kill the current leader as many times as you like and watch a new one get elected, term by term. Two simplifications versus real Raft, both to keep the demo focused on the election mechanic itself: there's no log here, so the "candidate log is at least as up-to-date" check from the stepper above is skipped (a vote is granted purely on term); and majority is computed over currently-alive nodes, not the fixed 5-node configuration — real Raft requires a majority of the full configured cluster so a minority partition can never elect its own leader.

Paxos (Classic)

Two phases:

sequenceDiagram
    participant PR as Proposer
    participant A1 as Acceptor 1
    participant A2 as Acceptor 2
    participant L as Learner

    rect rgb(50, 35, 60)
    Note over PR,A2: Phase 1 — Prepare / Promise
    PR->>A1: Prepare(n=7)
    PR->>A2: Prepare(n=7)
    A1->>A1: n=7 higher than any promised so far → promise
    A2->>A2: n=7 higher than any promised so far → promise
    A1-->>PR: Promise(7, no prior accepted value)
    A2-->>PR: Promise(7, no prior accepted value)
    end

    rect rgb(30, 50, 65)
    Note over PR,L: Phase 2 — Accept / Accepted
    PR->>A1: Accept(n=7, v="X")
    PR->>A2: Accept(n=7, v="X")
    A1->>A1: n=7 still highest promised → accept
    A2->>A2: n=7 still highest promised → accept
    A1-->>PR: Accepted(7, "X")
    A2-->>PR: Accepted(7, "X")
    A1--)L: Accepted(7, "X")
    A2--)L: Accepted(7, "X")
    Note over L: Majority accepted the same value — "X" is chosen
    end

Raft vs Paxos: Raft is easier to understand (strong leader, sequential log); Paxos is more general but leaves log ordering to implementation.

10. Multi-Master / Active-Active

Conflict Resolution Strategies

Galera Cluster (MySQL/MariaDB)

• Synchronous multi-master via wsrep (write-set replication)
• Every node certifies each transaction against other nodes' write sets before committing
• No replication lag; any node can take writes
• Cost: all writes pay network RTT; large transactions are expensive

CockroachDB

• Distributed SQL; each range (64 MB data shard) is a Raft group
• Writes go through Raft leader for the range
• Geo-partitioning pins data to regions; follower reads from nearest replica
• Serializable isolation via MVCC + HLC (Hybrid Logical Clocks)

11. Cross-Region Replication

Latency Math

Write latency with sync cross-region =
  local disk write + RTT to remote region + remote disk write

Example:
  us-east-1 → eu-west-1 RTT ≈ 85ms
  Sync write adds ~85ms minimum

Use async for cross-region writes unless RPO=0 is required.

Managed Services

Aurora Global Database

• One primary region, up to 5 secondary regions
• Async replication at storage layer (~1 s lag typical)
• Failover: promote secondary in < 1 min

DynamoDB Global Tables

• Multi-master active-active across regions
• LWW conflict resolution using _ab_hash (internal timestamp)
• ~1 s typical lag; eventual consistency across regions

Cloud SQL Read Replicas (GCP)

• Standard MySQL/Postgres streaming replication to another region
• No automatic failover; manual promote

Kafka MirrorMaker 2

# mm2.properties
clusters = source, target
source.bootstrap.servers = kafka-source:9092
target.bootstrap.servers = kafka-target:9092
source->target.enabled = true
source->target.topics = orders.*
replication.factor = 3

MirrorMaker 2 (Kafka Connect-based) replicates topics, consumer group offsets, and ACLs. Offset translation handles the gap between source and target offsets.

12. Replication Lag

Measuring Lag

PostgreSQL

-- On primary
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- On replica
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

MySQL

SHOW REPLICA STATUS\G
-- Look for: Seconds_Behind_Source (formerly Seconds_Behind_Master)
-- 0 = caught up; NULL = replica not running

MongoDB

rs.printReplicationInfo()        // oplog window size
rs.printSecondaryReplicationInfo() // lag per secondary

Redis

redis-cli INFO replication
# master_repl_offset vs replica slave_repl_offset difference = lag bytes

Kafka

kafka-consumer-groups.sh --bootstrap-server kafka:9092 \
  --describe --group my-consumer-group
# LAG column = messages behind per partition

Causes

• Replica under-provisioned (CPU/IO can't keep up with replay)
• Large transactions hold replica apply lock
• Network congestion between primary and replica
• Parallel apply disabled (single-threaded replay)

Mitigations

-- PostgreSQL: enable parallel apply on replica
ALTER SYSTEM SET max_parallel_apply_workers_per_subscription = 4;

-- MySQL: enable parallel replication
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_parallel_type = LOGICAL_CLOCK;

13. Comparison Table

14. Scenarios

Scenario 1: Read Replica Lag Causing Stale Reads

Symptom: User updates profile, refreshes page, sees old data.

Cause: App routes all reads to replica; replica is 2 s behind.

Fix options:

1. Read-your-writes: Route reads to primary for the same session immediately after a write.
2. Monotonic reads: Always route a given user's reads to the same replica.
3. Synchronous commit: Use synchronous_commit=remote_apply for critical writes.

-- PostgreSQL: check if replica is applying writes fast enough
SELECT now() - pg_last_xact_replay_timestamp() AS lag FROM pg_stat_replication;

Scenario 2: Split-Brain in Network Partition

Symptom: Two nodes both think they are primary and accept writes. Data diverges.

Cause: Network partition isolates primary from replicas; replicas elect a new primary; old primary keeps accepting writes.

Prevention:

• Quorum/majority writes: Old primary can't reach majority, so it should step down or reject writes.
• PostgreSQL with Patroni: uses etcd/ZooKeeper for distributed lock; old primary loses lock during partition.
• MongoDB: write concern w:majority will block if primary is isolated.
• Redis Sentinel: requires quorum of sentinels before failover.

# Patroni: pause DCS TTL to force primary to step down
patronictl -c patroni.yml pause

Scenario 3: Cascading Replica

Setup: Primary → Replica1 → Replica2 (cascading / chain replication)

graph LR
    classDef primary fill:#2c3e50,stroke:#1a252f,color:#fff
    classDef replica fill:#3498db,stroke:#2471a3,color:#fff

    P["Primary"]:::primary -->|"WAL stream<br/>lag: L1"| R1["Replica 1<br/>relays WAL onward"]:::replica
    R1 -->|"WAL stream<br/>lag: L2 (additional)"| R2["Replica 2<br/>total lag ≈ L1 + L2"]:::replica

PostgreSQL:

# On Replica2's postgresql.conf
primary_conninfo = 'host=replica1 ...'
recovery_target_timeline = 'latest'

Trade-off: Reduces load on primary (fewer WAL sender connections); Replica2 lag = Replica1 lag + additional lag. Replica2 is further behind in a failover.

Scenario 4: Promoting a Standby

PostgreSQL (manual):

# On the replica
pg_ctl promote -D /var/lib/postgresql/data
# or
touch /var/lib/postgresql/data/promote_trigger_file

-- Verify it became primary
SELECT pg_is_in_recovery();  -- should return false

With Patroni:

patronictl -c patroni.yml failover --master old-primary --candidate replica1 --force

MySQL (GTID):

-- Stop replica thread on the promoted node
STOP REPLICA;
RESET REPLICA ALL;

-- Other replicas point to new primary
CHANGE MASTER TO MASTER_HOST='new-primary', MASTER_AUTO_POSITION=1;
START REPLICA;

MongoDB:

// Force stepdown of primary
rs.stepDown(60)   // 60s cooldown before it can be re-elected

// Or in emergency, force specific node to become primary
cfg = rs.conf()
cfg.members[1].priority = 10
rs.reconfig(cfg)