Database Replication Cheat Sheet
Explains synchronous vs asynchronous replication, leader-follower topologies, failover, and replication lag for building highly available databases.
2 PagesIntermediateMar 10, 2026
Replication Topologies
Ways nodes can be arranged to copy data.
- Leader-follower (primary-replica)- One node accepts writes; one or more replicas apply the same changes and can serve reads
- Multi-leader (multi-master)- Multiple nodes accept writes and replicate to each other; requires conflict resolution for concurrent writes
- Leaderless replication- Clients write to multiple replicas directly (e.g., Cassandra, DynamoDB) and use quorum reads/writes for consistency
- Synchronous replication- The primary waits for a replica to acknowledge the write before confirming to the client; strongest durability, higher latency
- Asynchronous replication- The primary confirms the write immediately and streams changes to replicas afterward; low latency, risk of data loss on failover
- Semi-synchronous replication- At least one replica must acknowledge before commit, others replicate asynchronously; balances durability and latency
PostgreSQL Streaming Replication
Set up a streaming replica from a primary.
bash
# On primary: enable WAL archiving in postgresql.conf# wal_level = replica# max_wal_senders = 10# wal_keep_size = 1GB# Create a replication-only rolepsql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';"# On replica: take a base backup from the primarypg_basebackup -h primary.host -U replicator -D /var/lib/postgresql/data -P -R# -R writes standby.signal and primary_conninfo automatically# Start the replica; it streams WAL from the primarypg_ctl start -D /var/lib/postgresql/data
MySQL Replication Setup
Point a replica at its source and check status.
sql
-- On the replica, point it at the source and start replicationCHANGE REPLICATION SOURCE TO SOURCE_HOST='primary.host', SOURCE_USER='repl_user', SOURCE_PASSWORD='secret', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=1547;START REPLICA;-- Check lag and statusSHOW REPLICA STATUS\G-- Key fields: Seconds_Behind_Source, Replica_IO_Running, Replica_SQL_Running
Key Concepts
Terminology you'll run into when operating replicas.
- Replication lag- Delay between a write on the primary and its appearance on a replica; stale reads increase under load
- WAL / binlog- Write-Ahead Log (Postgres) or binary log (MySQL) records every change and is streamed to replicas
- Failover- Promoting a replica to primary when the original primary fails; can be manual or automated (e.g., Patroni, Orchestrator)
- Split-brain- Two nodes both believe they are primary after a network partition, causing conflicting writes
- Read replica- A replica used to offload read traffic from the primary, common for read-heavy workloads
- Quorum (W+R>N)- In leaderless systems, requiring writes/reads to reach a majority of replicas guarantees overlap and consistency
PostgreSQL Logical Replication
Replicate a subset of tables (not the whole cluster) using publications and subscriptions.
sql
-- On the publisher: expose specific tables as a publicationCREATE PUBLICATION orders_pub FOR TABLE orders, order_items;-- Requires wal_level = logical in postgresql.conf (restart needed)-- On the subscriber: create a matching schema, then subscribeCREATE SUBSCRIPTION orders_sub CONNECTION 'host=publisher.host dbname=app user=repl_user password=secret' PUBLICATION orders_pub;-- Check subscription status and lagSELECT subname, received_lsn, latest_end_lsn, latest_end_timeFROM pg_stat_subscription;-- Logical replication supports selective tables, cross-version upgrades,-- and row filters (WHERE clause on publication, PG 15+), but does NOT-- replicate DDL, sequences, or large objects automatically.
MySQL GTID-Based Replication
Use global transaction identifiers so replicas can auto-locate their position after failover.
sql
-- On both source and replicas (my.cnf):-- gtid_mode = ON-- enforce_gtid_consistency = ON-- log_bin = ON-- log_slave_updates = ON-- Point the replica at the source using GTID auto-positioningCHANGE REPLICATION SOURCE TO SOURCE_HOST='primary.host', SOURCE_USER='repl_user', SOURCE_PASSWORD='secret', SOURCE_AUTO_POSITION=1;START REPLICA;-- Inspect executed transaction setsSELECT @@GLOBAL.gtid_executed;SHOW REPLICA STATUS\G-- Retrieved_Gtid_Set / Executed_Gtid_Set should converge over time-- GTIDs let you promote any replica during failover without hunting-- for the correct binlog file/position -- the new source's executed-- set is compared automatically to find the resume point.
Monitoring Replication Health
Query-level checks for lag and stuck replicas beyond the basic status commands.
sql
-- PostgreSQL: lag in bytes and time, per replica, from the primarySELECT application_name, client_addr, state, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes, pg_wal_lsn_diff(sent_lsn, flush_lsn) AS flush_lag_bytes, pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes, replay_lagFROM pg_stat_replication;-- PostgreSQL: measured from the replica itself (works even if the-- primary's pg_stat_replication row disappears)SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag_interval;-- MySQL: Seconds_Behind_Source can lie under network partitions;-- cross-check with heartbeat tables (pt-heartbeat) for ground truthSELECT TIMESTAMPDIFF(SECOND, ts, NOW()) AS true_lag_secondsFROM heartbeat.heartbeat ORDER BY ts DESC LIMIT 1;
Advanced Replication Patterns & Failure Modes
Concepts that show up once replication moves beyond a single primary/replica pair.
- Cascading replication- A replica streams WAL to further downstream replicas instead of all of them reading from the primary, reducing primary network load
- Replication slots- Postgres mechanism that pins WAL retention until a replica confirms it consumed it; prevents data loss but can bloat disk if a replica disconnects and never resumes
- synchronous_standby_names- Postgres GUC listing which replicas must ack before commit (e.g., ANY 2 (replica1,replica2,replica3)) for quorum-based durability
- Delayed / lagged replica- A replica deliberately configured (recovery_min_apply_delay) to stay N minutes behind, acting as a rewindable safety net against accidental DELETE/DROP
- Conflict resolution (multi-leader)- Strategies like last-write-wins by timestamp, per-field merge, or CRDTs to reconcile concurrent writes accepted on different leaders
- Fencing- Forcibly cutting off a demoted or unreachable former primary (STONITH, revoked credentials) so it can't accept writes and cause split-brain after failover
- Replica promotion- Tools like Patroni, pg_auto_failover, or Orchestrator watch primary health and automate leader election plus DNS/proxy re-pointing
Automated Failover with Patroni
Minimal Patroni config for a self-healing Postgres cluster backed by etcd.
yaml
scope: postgres-clusternamespace: /db/name: node1etcd3: hosts: etcd1:2379,etcd2:2379,etcd3:2379bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 # bytes; skip stale replicas as candidates synchronous_mode: true postgresql: parameters: wal_level: replica max_wal_senders: 10postgresql: listen: 0.0.0.0:5432 connect_address: node1:5432 data_dir: /var/lib/postgresql/data authentication: replication: { username: replicator, password: secret }# Patroni holds a leader lock in etcd/Consul/ZooKeeper; if the lock# expires (primary unresponsive), the healthiest replica is promoted# and a REST callback re-points HAProxy/PgBouncer at the new leader.
Pro Tip
Monitor replication lag continuously and route read-after-write queries (like 'show my new comment') back to the primary or a synchronous replica — an async replica can be seconds behind and make a just-completed write appear to have vanished.
Was this cheat sheet helpful?
Explore Topics
#DatabaseReplication#DatabaseReplicationCheatSheet#Database#Intermediate#ReplicationTopologies#PostgreSQLStreamingReplication#MySQLReplicationSetup#KeyConcepts#Databases#Concurrency#CheatSheet#SkillVeris
