Database Replication — Primary-Replica, Multi-Primary

Overview

Replication keeps copies of your data on several nodes: one primary accepts writes, replicas apply the stream and serve reads. It’s how you survive a dead primary at 3 AM and how you scale reads past what a single box can do. The cost is complexity — lag, conflicts, split-brain, and consistency trade-offs you’ll have to design around.

This guide covers the strategies that actually run in production: primary-replica topologies (still widely called master-slave), multi-primary setups, sync vs async modes, failover mechanics, and conflict resolution. For the operational snippets, see the database replication recipe. If you want the PostgreSQL-specific deep dive, there’s a complete PostgreSQL replication guide too.

When to Use

• Read traffic outgrows a single node and vertical scaling stopped paying off
• You need automatic failover, not a 2-hour restore from backups
• Off-site copies for disaster recovery
• Heavy analytics that shouldn’t fight transactional queries for resources

Primary-Replica Replication

One primary handles writes; replicas handle reads. Still the most common topology — the terms “master-slave” and “primary-replica” refer to the same thing, and you’ll see both in older docs and tools.

Asynchronous Replication

The primary commits locally, then ships changes to replicas. Lowest latency, but you can lose the last few seconds of writes if the primary dies before replicas catch up.

-- MySQL 8.0.22+ (older versions: CHANGE MASTER TO / START SLAVE)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary_host',
  SOURCE_USER='replica',
  SOURCE_PASSWORD='password';
START REPLICA;

-- PostgreSQL (streaming replication)
-- Primary: wal_level = replica, max_wal_senders = 3
-- Standby: primary_conninfo = 'host=primary_host port=5432'

Semi-Synchronous Replication

The primary waits for at least one replica to acknowledge receipt before committing. Middle ground between safety and speed.

-- MySQL
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 10000;  -- 10 seconds

Synchronous Replication

The primary waits for all replicas (or a configured quorum) to confirm. Zero data loss, at the cost of write latency.

-- PostgreSQL synchronous_commit
SET synchronous_commit = 'remote_apply';
SET synchronous_standby_names = 'replica1, replica2';

Replication Lag

Lag is the delay between a write on the primary and its appearance on replicas. What causes it and what to do:

Cause	Mitigation
Network latency	Use nearby regions, wal_compression
High write volume	Shard writes, add replicas
Large transactions	Break into smaller batches
Slow replica hardware	Match replica specs to primary
Reads competing with apply	Dedicated read replicas

Detecting Lag

-- PostgreSQL (run on the replica)
SELECT
  now() - pg_last_xact_replay_timestamp() AS lag;

-- MySQL 8.0.22+
SHOW REPLICA STATUS\G
-- watch Seconds_Behind_Source (Seconds_Behind_Master on older versions)

Multi-Primary Replication

Two or more nodes accept writes — traditionally called multi-master. It unlocks write scaling and geographic distribution, and it’s where replication gets genuinely hard.

Conflict Scenarios

Scenario	Conflict
Same key inserted	Primary key violation
Same row updated	Last-write-wins or merge
Row deleted on A, updated on B	Update wins or conflict flag
Auto-increment IDs	Duplicate IDs across primaries

Conflict Resolution Strategies

1. Last-write-wins — timestamp or vector clock decides; simple, can silently drop writes
2. Merge — application logic combines both changes
3. Manual resolution — flag conflicts for human review
4. Avoidance — partition data so each row has exactly one writer; simplest and usually the right answer

Failover

Switching to a replica when the primary fails. The two flavors:

Manual Failover

# PostgreSQL: promote standby
pg_ctl promote -D /var/lib/postgresql/data

# MySQL 8.0.22+: stop replica, reset, promote
STOP REPLICA;
RESET REPLICA ALL;

Manual failover is simple until it’s 3 AM and nobody’s around — that’s when “we’ll promote it by hand” turns into an hour of downtime.

Automatic Failover (Patroni)

# patroni.yml
scope: mycluster
restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.0.1:8008
etcd:
  hosts: 10.0.0.10:2379,10.0.0.11:2379,10.0.0.12:2379
postgresql:
  data_dir: /var/lib/postgresql/data
  pg_hba:
    - host replication replicator 10.0.0.0/24 md5

Read Replicas for Scaling

Route reads to replicas, writes to the primary. The application owns the routing — and owns dealing with lag.

class DatabaseRouter {
  private primary: Pool;   // pg.Pool for writes
  private replicas: Pool[];
  private currentReplica = 0;

  getWritePool(): Pool {
    return this.primary;
  }

  getReadPool(): Pool {
    // Round-robin across replicas
    const pool = this.replicas[this.currentReplica];
    this.currentReplica = (this.currentReplica + 1) % this.replicas.length;
    return pool;
  }
}

// Usage
const db = new DatabaseRouter();
await db.getWritePool().query('INSERT INTO ...');
const result = await db.getReadPool().query('SELECT ...');

Advanced Topics

Scenario: High Availability with Patroni + HAProxy

System: PostgreSQL cluster with 3 nodes
  - 1 primary (us-east-1a)
  - 2 replicas (us-east-1b, us-east-1c)
  - etcd cluster (3 nodes) for consensus
  - HAProxy as load balancer

Topology:
  Clients -> HAProxy -> Primary (writes)
                      -> Replica1 (reads)
                      -> Replica2 (reads)

  etcd <- Patroni (health checks, leader election)
  Patroni manages: automatic failover, restart, reconfiguration

Patroni config:
  scope: production
  restapi:
    listen: 0.0.0.0:8008
  etcd:
    hosts: 10.0.0.10:2379,10.0.0.11:2379,10.0.0.12:2379
  postgresql:
    data_dir: /var/lib/postgresql/data
    parameters:
      wal_level: replica
      max_wal_senders: 10
      hot_standby: on
      synchronous_commit: on
      synchronous_standby_names: "FIRST 1 (replica1, replica2)"
    recovery_conf:
      primary_conninfo: "host=10.0.0.1 port=5432 user=replicator"

HAProxy config:
  listen pg_write
    bind *:5432
    mode tcp
    option tcp-check
    tcp-check send GET /master HTTP/1.0\r\n\r\n
    tcp-check expect status 200
    server pg1 10.0.0.1:5432 check port 8008
    server pg2 10.0.0.2:5432 check port 8008 backup
    server pg3 10.0.0.3:5432 check port 8008 backup

  listen pg_read
    bind *:5433
    mode tcp
    balance roundrobin
    option tcp-check
    tcp-check send GET /replica HTTP/1.0\r\n\r\n
    tcp-check expect status 200
    server pg2 10.0.0.2:5432 check port 8008
    server pg3 10.0.0.3:5432 check port 8008

Automatic failover:
  1. Patroni detects primary down (health check fails)
  2. etcd performs leader election among replicas
  3. Winning replica promotes to primary
  4. HAProxy re-routes traffic automatically
  5. Other replicas reconnect to new primary
  Total time: 10-30 seconds

Monitoring:
  | Metric | Alert |
  |---------|--------|
  | Replication lag | > 5s |
  | Patroni health | non-200 |
  | etcd leader changes | > 1/h |
  | Active connections | > 80% of max |
  | WAL backlog | > 1GB |

Lessons:
  - Patroni + etcd gives solid automatic failover
  - HAProxy distinguishes primary/replica via Patroni REST API
  - Synchronous commit guarantees zero data loss
  - 3 nodes is minimum for quorum (never use 2)
  - Test failover regularly in staging

Common Mistakes

• Ignoring replication lag — a read right after a write can hit a replica that hasn’t applied it yet
• Writing to replicas — creates split-brain and divergent data
• No failover automation — minutes of manual work become an hour of downtime
• Watching only Seconds_Behind_Source — it reads zero while the replica is still applying a big transaction
• Under-provisioned replicas — if replicas can’t keep up with primary write throughput, lag grows forever

Troubleshooting

• Lag keeps growing: find the bottleneck first — network, disk I/O on the replica, or a long-running transaction. Split bulk writes into batches and check pg_stat_replication for where the stream stalls.
• Replica can’t catch up: if WAL is generated faster than the replica applies it, single-threaded apply is the limit. Enable parallel apply where supported, or reduce write bursts.
• Replication slot filling the disk: a dead replica keeps its slot pinning WAL on the primary. Drop it with pg_drop_replication_slot or it will fill the volume and crash the primary.
• Split-brain after failover: two nodes believe they’re primary. Stop one immediately, resolve divergence with pg_rewind, and reattach it as a replica. Never run two writable primaries outside a real multi-primary setup.
• Replica won’t start after promotion: check primary_conninfo and replication credentials — a repointed replica fails silently in the logs.

See Also

• PostgreSQL streaming replication docs
• MySQL replication docs
• Patroni documentation
• Read replica guide