Replication keeps copies of a database on several servers. It’s the foundation of database high availability, read scaling and near-real-time disaster recovery. It also brings new failure modes: lag, conflicting writes, split-brain. Here’s how replication works conceptually, before you configure a specific database.

Why replicate?

  • High availability: if the primary fails, a replica takes over.
  • Read scaling: spread read queries (reports, search, API reads) over replicas.
  • Disaster recovery: a replica in another location survives the loss of a data centre.
  • Zero-downtime maintenance: upgrade or rebuild nodes one at a time.
  • Backups without load: run backups on a replica.

Replication is not a backup. A DELETE or corrupted write is replicated instantly. You still need point-in-time backups. See database backup automation.

Topologies

Primary–replica (leader–follower). One primary accepts writes, and replicas apply its changes and serve reads. This is by far the most common setup: simple, with predictable consistency.

           writes
 app ───────────────▶ primary ──changes──▶ replica 1 (reads)
   └──────── reads ──────────────────────▶ replica 2 (reads)

Multi-primary (multi-master). Several nodes accept writes, such as Galera Cluster, MySQL Group Replication in multi-primary mode, or CockroachDB. It needs conflict handling or certification. It’s great for availability, with trade-offs in write latency and conflict errors. See MariaDB Galera and CockroachDB.

Cascading. Replicas replicate from other replicas, to reduce load on the primary or to feed remote regions.

Synchronous vs asynchronous

Asynchronous Semi-synchronous Synchronous
Primary waits for Nothing At least one replica to receive the change Replica(s) to commit the change
Write latency Lowest + one network round trip Highest
Data loss on primary failure Possible (recent transactions) Minimal None
Typical MySQL default, PostgreSQL default MySQL semi-sync plugin PostgreSQL synchronous_commit with sync standbys, Galera

Across long distances, synchronous replication adds the round-trip time to every commit. Most multi-region setups use asynchronous replication, and accept a small window of possible loss. See latency vs bandwidth.

Replication lag

With asynchronous replicas, data arrives a little later, usually milliseconds, but seconds or minutes under heavy writes or long transactions. Effects:

  • read-your-own-writes problems: a user saves something, the next page reads from a lagging replica, and the change seems missing;
  • stale reports and search results.

Mitigations: read from the primary right after writes (or for that user’s session), monitor lag and remove lagging replicas from read pools, and keep transactions small.

-- MySQL
SHOW REPLICA STATUS\G     -- Seconds_Behind_Source
-- PostgreSQL (on primary)
SELECT client_addr, state, replay_lag FROM pg_stat_replication;

Failover and split-brain

When the primary dies, a replica must be promoted, and applications must switch to it.

  • Manual failover: simple and safe, but slower (someone must react).
  • Automatic failover: Patroni (PostgreSQL), Orchestrator or MySQL InnoDB Cluster, using consensus (etcd, Raft) to agree on the new primary.

The big danger is split-brain: two nodes both believing they’re the primary, and accepting conflicting writes. Usually that’s after a network partition. Proper failover systems use quorum (a majority of nodes must agree), and fence the old primary so it can’t accept writes. That’s why HA clusters use three nodes (or two plus a witness), not two.

Applications need a stable way to find the primary: a virtual IP, a proxy (HAProxy, ProxySQL, PgBouncer) or DNS updated on failover. See HAProxy TCP mode and keepalived.

Logical vs physical replication

  • Physical (PostgreSQL streaming replication): copies the exact data file changes (WAL). Replicas are byte-identical, read-only, and the same major version.
  • Logical / statement- or row-based (MySQL binlog, PostgreSQL logical replication): replicates row changes. That allows replicating selected tables, cross-version replication for upgrades, and feeding other systems (CDC to Kafka or search indexes).
Database Common approach Guide
MySQL / MariaDB Binlog replication with GTIDs, semi-sync, Group Replication, Galera MySQL replication
PostgreSQL Streaming (physical) replication, logical replication, Patroni for HA PostgreSQL replication
MongoDB Replica sets with automatic elections MongoDB replica set
Redis Replicas + Sentinel, or Redis Cluster Redis persistence

When to scale differently

Replication scales reads, not writes. When the write volume outgrows one primary, the next steps are optimising queries, caching, bigger hardware (vertical scaling), splitting by service, and finally sharding. See database sharding.

Quick reference

Concept Key point
Primary–replica One writer, many readers
Async Fast, may lose the last transactions on failover
Sync No loss, slower writes
Lag Monitor it, and read-your-writes from the primary
Failover Quorum and fencing prevent split-brain
Not a backup Keep point-in-time backups too

Related: high availability on VPS and database connection pooling.

  • Databases
  • High Availability
  • Architecture