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

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?

Database Sharding Cheat Sheet

SQL Joins Deep Dive Cheat Sheet

Explore Topics

#DatabaseReplication#DatabaseReplicationCheatSheet#Database#Intermediate#ReplicationTopologies#PostgreSQLStreamingReplication#MySQLReplicationSetup#KeyConcepts#Databases#Concurrency#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services