Database Replication Explained: Primary, Replica, and Failover

Database replication is the process of copying data from one database server to one or more others in near real-time. You need it because a single database server is both a performance bottleneck and a single point of failure: if it goes down, your app goes down. Replication gives you redundancy and horizontal read scaling at the same time.

This guide covers everything from topology choices to a concrete PostgreSQL streaming replication setup, with practical code for read/write splitting, lag detection, and failover handling.

What Is Database Replication?

Replication continuously copies data from one database server (the primary, also called the leader) to one or more other servers (replicas, also called followers or standbys). The primary is the source of truth. Replicas stay in sync by consuming a stream of changes from the primary.

Without replication:
App → Database (single point of failure, single server handles all load)
 
With replication:
App writes → Primary ──→ Replica 1 (reads)
                    └──→ Replica 2 (reads)
                    └──→ Replica 3 (failover target)

Why replicate:

• High availability: If the primary fails, promote a replica — downtime measured in seconds, not hours
• Read scaling: Distribute SELECT queries across multiple replicas — 3 replicas means roughly 3× read throughput
• Backups without load: Run pg_dump or snapshots on a replica so the primary is unaffected
• Geographic distribution: A replica in another region reduces read latency for users there

Replication Topologies

Topology determines how many nodes accept writes and how changes flow between nodes.

Primary-Replica (Leader-Follower)

The most common setup. One primary accepts all writes. One or more replicas receive the change stream from the primary and apply it. Reads can be routed to replicas.

         ┌──────────────┐
         │   Primary     │  ← All writes land here
         └──────┬───────┘
                │ WAL stream
        ┌───────┴────────┐
        ▼                ▼
  ┌──────────┐    ┌──────────┐
  │ Replica 1│    │ Replica 2│   ← Read traffic
  └──────────┘    └──────────┘

Simple to operate. Conflict-free because only one node writes. Works for the vast majority of applications.

Multi-Primary (Multi-Master)

Two or more nodes accept writes and replicate to each other. Used for multi-region write availability — each region writes to its local primary.

  Region A                Region B
┌──────────┐   ←sync→   ┌──────────┐
│ Primary A│             │ Primary B│
└──────────┘             └──────────┘
     │                        │
  Replica A               Replica B

The hard problem: two primaries can update the same row simultaneously. You need a conflict resolution policy — last-write-wins (based on timestamp), application-level merge, or rejecting the conflicting write. Most applications do not need this. Reach for multi-primary only when you have genuine multi-region write requirements and have thought through conflict handling.

Chain Replication

Replicas form a chain: Primary → Replica A → Replica B → Replica C. Each node replicates to the next.

Primary → Replica A → Replica B → Replica C
  (writes)   (reads)    (reads)    (backups/DR)

Useful when you want to offload replication work from the primary — the primary only ships the stream to Replica A, and the rest of the chain handles propagation. PostgreSQL supports this via cascading replication. Trade-off: a failure in the middle of the chain breaks replication for all downstream nodes.

Synchronous vs Asynchronous Replication

The single most important configuration decision in replication. It controls the trade-off between write performance and data durability.

Asynchronous Replication (Default)

Primary writes to its own disk, confirms success to the application, then ships the change to replicas in the background. The application does not wait for replicas.

Client write
    │
    ▼
Primary commits ──► Returns "success" to app (fast)
    │
    ▼ (background, milliseconds to seconds later)
Replica receives & applies change

Pros: Low write latency. Primary never stalls waiting for a slow or distant replica. Suitable for most workloads.

Cons: If the primary crashes between its commit and the replica receiving the change, that transaction is lost. Replicas may serve stale data.

RPO/RTO implications: RPO (Recovery Point Objective) is non-zero with async replication — you can lose the last N milliseconds of writes. RTO is low because failover can happen quickly without waiting for replica confirmation.

Synchronous Replication

Primary does not confirm success to the application until at least one replica has acknowledged (written to its WAL, or fully applied — configurable). The application waits.

Client write
    │
    ▼
Primary commits
    │
    ▼
Waits for replica ACK ──► Returns "success" to app (slower)
    │
    ▼ (already done — replica confirmed)
Replica has the data

Pros: Zero data loss on primary failure (RPO = 0). Reads from a synchronous replica are guaranteed fresh.

Cons: Write latency includes a network round-trip to the replica. If the replica is unavailable or slow, writes on the primary stall. In cross-region setups, this can add 50–200ms per write.

RPO/RTO implications: RPO = 0 (no data loss). RTO may be slightly higher because the failover promotion process needs to verify replica state.

PostgreSQL synchronous_commit Setting

PostgreSQL gives you fine-grained control via synchronous_commit:

-- postgresql.conf on primary
synchronous_standby_names = 'ANY 1 (replica1, replica2)'
-- '' = async (default)
-- 'replica1' = sync with this specific replica
-- 'ANY 1 (replica1, replica2)' = sync with whichever replica responds first
-- 'ALL (replica1, replica2)' = sync with ALL listed replicas

You can also control sync behavior per transaction:

-- Per-transaction override — useful for mixing policies
BEGIN;
SET LOCAL synchronous_commit = 'on';   -- Wait for replica WAL flush
INSERT INTO payments (amount, user_id) VALUES (9900, 42);
COMMIT;
 
-- For less critical writes in the same app:
BEGIN;
SET LOCAL synchronous_commit = 'local'; -- Only wait for local disk, not replica
INSERT INTO analytics_events ...;
COMMIT;

synchronous_commit levels from fastest to safest:

• off — write to OS buffer only, return success (most dangerous)
• local — write to local disk WAL, don't wait for replica
• remote_write — replica received the data (not yet flushed to disk)
• on — replica flushed to disk
• remote_apply — replica applied the change (readable on replica immediately)

Most applications use local or on. Financial transactions that cannot tolerate loss use remote_apply.

Replication Topologies

Replication Lag: Detection and Handling

Replication lag is the delay between when a write commits on the primary and when that write is visible on the replica. Under normal load it's milliseconds. Under heavy write load or a slow network, it can grow to seconds or minutes.

Measuring Lag with pg_stat_replication

On the primary, this view shows the replication state for every connected standby:

SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

 application_name | state  | write_lag | flush_lag | replay_lag
------------------+--------+-----------+-----------+------------
 replica1         | streaming | 00:00:00 | 00:00:00 | 00:00:00.002
 replica2         | streaming | 00:00:00 | 00:00:00 | 00:00:01.3

replay_lag is the most important column — it's how far behind the replica is in applying changes. Alert when it exceeds your acceptable threshold (commonly 30s for non-critical reads, near-zero for financial data).

On the replica, check its own lag:

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

Routing Reads to Avoid Stale Data

Three practical strategies:

Strategy 1: Always read from primary after a write (simplest)

def update_username(user_id, name):
    with primary.connect() as conn:
        conn.execute(
            "UPDATE users SET name = %s WHERE id = %s", (name, user_id)
        )
    # Return data directly — don't read from replica
    return {"id": user_id, "name": name}

Strategy 2: Sticky primary reads for a short window after writes

import time
from threading import local
 
_state = local()
 
def mark_wrote(user_id):
    if not hasattr(_state, 'wrote_at'):
        _state.wrote_at = {}
    _state.wrote_at[user_id] = time.time()
 
def should_use_primary(user_id, window_seconds=5):
    wrote_at = getattr(_state, 'wrote_at', {}).get(user_id)
    if wrote_at and time.time() - wrote_at < window_seconds:
        return True
    return False
 
def get_user(user_id):
    db = primary if should_use_primary(user_id) else replica
    with db.connect() as conn:
        return conn.execute(
            "SELECT * FROM users WHERE id = %s", (user_id,)
        ).fetchone()

Strategy 3: Route based on real-time lag measurement

def get_safe_replica():
    with monitoring_db.connect() as conn:
        lag = conn.execute(
            "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))"
        ).scalar()
    if lag is None or lag > 5.0:  # >5 seconds lag: fall back to primary
        return primary
    return replica

PostgreSQL Replication Setup

A concrete walkthrough of setting up streaming replication between a primary and a replica.

Step 1: Configure the Primary

# postgresql.conf
wal_level = replica          # Minimum for streaming replication
max_wal_senders = 5          # Max concurrent replica connections
wal_keep_size = 1GB          # Keep enough WAL for replicas that fall behind
listen_addresses = '*'

-- pg_hba.conf: Allow the replica to connect for replication
host  replication  replicator  192.168.1.0/24  scram-sha-256

-- Create a dedicated replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong-password';

Reload PostgreSQL:

pg_ctl reload -D /var/lib/postgresql/data

Step 2: Base Backup with pg_basebackup

On the replica server, take an initial copy of the primary's data directory:

pg_basebackup \
  --host=primary-host \
  --username=replicator \
  --pgdata=/var/lib/postgresql/data \
  --wal-method=stream \
  --checkpoint=fast \
  --progress \
  --verbose

--wal-method=stream streams WAL during the backup so the replica starts in a consistent state. --checkpoint=fast forces a checkpoint immediately rather than waiting.

Step 3: Configure the Replica

PostgreSQL 12+ uses postgresql.conf and standby.signal instead of the old recovery.conf:

# On the replica: create standby.signal (empty file signals standby mode)
touch /var/lib/postgresql/data/standby.signal

# postgresql.conf on replica
primary_conninfo = 'host=primary-host port=5432 user=replicator password=strong-password application_name=replica1'
hot_standby = on            # Allow read queries on the replica

Start the replica:

pg_ctl start -D /var/lib/postgresql/data

Step 4: Verify Replication

On the primary:

SELECT application_name, state, replay_lag
FROM pg_stat_replication;
-- Should show replica1 in 'streaming' state

On the replica:

SELECT pg_is_in_recovery();  -- Should return true
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

Cascading Replication

To set up Replica B to replicate from Replica A instead of the primary:

# postgresql.conf on Replica B
primary_conninfo = 'host=replica-a-host port=5432 user=replicator ...'

# Allow replica A to have wal_senders for downstream replicas
# (set max_wal_senders on replica A as well)

Read Scaling with Replicas

Read replicas are the most cost-effective way to scale read-heavy workloads. The goal is to route SELECTs to replicas and keep writes on the primary.

Connection Pooling with PgBouncer

Connecting directly to PostgreSQL from every application process is expensive — each connection costs ~5–10MB of RAM on the server. PgBouncer pools connections and multiplexes many application connections through a smaller set of server connections.

# pgbouncer.ini
[databases]
mydb_primary = host=primary-host port=5432 dbname=mydb
mydb_replica = host=replica-host port=5432 dbname=mydb
 
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction       # Most efficient — connection returned after each transaction
max_client_conn = 1000        # Total app connections PgBouncer accepts
default_pool_size = 20        # Actual server connections per database

Your application connects to PgBouncer on port 6432, not directly to PostgreSQL on 5432. PgBouncer manages the actual server connections.

Read/Write Splitting in Application Code

SQLAlchemy example with explicit routing:

from sqlalchemy import create_engine, text
 
primary_engine = create_engine(
    "postgresql://user:pass@pgbouncer:6432/mydb_primary",
    pool_size=5,
    max_overflow=10,
)
replica_engine = create_engine(
    "postgresql://user:pass@pgbouncer:6432/mydb_replica",
    pool_size=10,   # More connections — replicas handle more reads
    max_overflow=20,
)
 
def get_db_for_operation(write: bool = False):
    return primary_engine if write else replica_engine
 
# Usage
def create_order(data: dict):
    with primary_engine.connect() as conn:
        result = conn.execute(
            text("INSERT INTO orders (user_id, total) VALUES (:user_id, :total) RETURNING id"),
            data
        )
        conn.commit()
        return result.fetchone()
 
def list_orders(user_id: int):
    with replica_engine.connect() as conn:
        return conn.execute(
            text("SELECT * FROM orders WHERE user_id = :user_id ORDER BY created_at DESC"),
            {"user_id": user_id}
        ).fetchall()

What to route to replicas:

• Product catalog reads
• User profile fetches (when stale reads are acceptable)
• Report generation and analytics
• Search queries
• Any read that doesn't immediately follow a write from the same user

What must stay on primary:

• All INSERT, UPDATE, DELETE
• Reads immediately after a write by the same user (read-your-own-writes)
• Any read where stale data would cause incorrect behavior (e.g., inventory checks before purchase)

Replication Failure Scenarios

Replica Lag During High Write Load

Under a sudden write spike, replicas can fall behind because they apply changes serially (one WAL record at a time). The primary keeps accepting writes, the replica queue builds up.

Detection:

-- Alert if replay_lag exceeds threshold
SELECT application_name, replay_lag
FROM pg_stat_replication
WHERE replay_lag > interval '30 seconds';

Mitigations:

• Enable parallel apply on PostgreSQL 16+: recovery_parallelism = 4 on the replica
• Increase wal_keep_size on the primary so replicas can catch up after a spike
• Use replication slots (so the primary never deletes WAL a replica hasn't consumed) — but monitor slot lag, as unconsumed slots can fill your disk

-- Create a replication slot (primary keeps WAL until slot consumer catches up)
SELECT pg_create_physical_replication_slot('replica1_slot');
 
-- Monitor slot lag — disk risk if consumer is far behind
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Network Partition

If the network between primary and replica is interrupted, the replica enters a "catchup" state when connectivity resumes and replays the buffered WAL. If wal_keep_size was too small and WAL was recycled, the replica cannot reconnect and must be rebuilt with pg_basebackup.

With a replication slot, the primary holds WAL until the slot consumer acknowledges it — but this means the primary's disk can fill if the partition is long. Set max_slot_wal_keep_size to cap disk usage:

# postgresql.conf
max_slot_wal_keep_size = 10GB  # Evict slot if it causes >10GB of retained WAL

Failover Promotion Process

When the primary is confirmed dead (not just a network glitch), you promote the most up-to-date replica:

Manual promotion:

# On the replica you want to promote
pg_ctl promote -D /var/lib/postgresql/data
 
# Verify it's now primary
psql -c "SELECT pg_is_in_recovery();"  -- Should return false

Automated promotion with Patroni:

Patroni uses etcd or Consul for distributed leader election. When the primary's TTL expires without a heartbeat, the replica with the highest LSN wins the election and promotes itself:

# patroni.yml (simplified)
bootstrap:
  dcs:
    ttl: 30                       # Primary heartbeat TTL
    loop_wait: 10                 # How often Patroni checks
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1MB — don't promote a very stale replica
 
postgresql:
  listen: 0.0.0.0:5432
  connect_address: this-host:5432
  pg_hba:
    - host replication replicator 0.0.0.0/0 scram-sha-256

After promotion, other replicas must be reconfigured to follow the new primary:

# On remaining replicas: update primary_conninfo to point to new primary
# Then signal reload
pg_ctl reload -D /var/lib/postgresql/data

Use a virtual IP or DNS failover (or HAProxy) so your application's connection string doesn't need updating:

# HAProxy config for automatic primary routing
frontend postgres
    bind *:5000
    default_backend postgres_primary
 
backend postgres_primary
    option httpchk GET /primary
    server primary-1 primary1:5432 check port 8008  # Patroni REST API
    server primary-2 primary2:5432 check port 8008

Patroni's REST API at :8008/primary returns HTTP 200 only on the current primary — HAProxy uses this to route connections without DNS changes.

Key Takeaways

• Replication copies data from primary to replicas — for availability and read scaling
• Topology: primary-replica for most cases; multi-primary only for genuine multi-region write requirements
• Async replication: fast writes, possible brief lag — good for most apps (RPO is near-zero milliseconds)
• Sync replication: zero data loss, slower writes — use synchronous_commit = remote_apply for financial data
• Lag: monitor via pg_stat_replication.replay_lag; route reads after writes to primary for consistency
• PostgreSQL setup: pg_basebackup + standby.signal + primary_conninfo — streaming replication in three steps
• Read scaling: PgBouncer for connection pooling; route SELECTs to replicas, writes to primary
• Failover: automate with Patroni + etcd; use HAProxy or virtual IP so apps don't need reconfiguration

Start with a single primary and add read replicas when read load grows. Add automated failover before you need it — after an incident is too late.

FAQ

What is database replication and why is it important?

Database replication continuously copies data from a primary server to one or more replica servers. It is important for two reasons: it eliminates the single point of failure (if the primary crashes, a replica can be promoted in seconds) and it scales read throughput (multiple replicas can serve SELECT queries in parallel). Without replication, one server handles all load and one failure takes down the entire application.

What is the difference between synchronous and asynchronous replication?

In asynchronous replication, the primary confirms a write to the application before the replica receives it. This is fast but creates a window where the primary can fail and the replica has not yet received the last few milliseconds of writes (data loss risk). In synchronous replication, the primary waits for at least one replica to acknowledge the write before confirming to the application. This guarantees zero data loss but adds network latency to every write. PostgreSQL controls this per-transaction via synchronous_commit.

What is replication lag and how do I handle it?

Replication lag is the delay between when a write commits on the primary and when it becomes visible on the replica. It is usually milliseconds but can grow under heavy write load. Measure it on the primary with SELECT replay_lag FROM pg_stat_replication or on the replica with SELECT now() - pg_last_xact_replay_timestamp(). Handle it by routing reads that immediately follow writes back to the primary, or by using a short sticky-primary window after each write.

How do I set up PostgreSQL streaming replication?

Four steps: (1) Set wal_level = replica and max_wal_senders = 5 on the primary, create a replication role, and allow it in pg_hba.conf. (2) Run pg_basebackup --host=primary --wal-method=stream --pgdata=/data on the replica. (3) Create standby.signal in the replica's data directory and set primary_conninfo in postgresql.conf. (4) Start the replica and verify with SELECT pg_is_in_recovery() on the replica and SELECT * FROM pg_stat_replication on the primary.

How do read replicas improve database performance?

Read replicas let you distribute SELECT queries across multiple servers. If your workload is 80% reads, three read replicas can handle three times the read throughput of a single server. The primary processes only writes plus any reads that require immediate consistency. You add more replicas as read load grows without changing the primary at all. Connection pooling with PgBouncer further multiplies this — it allows hundreds of application connections to share a small pool of actual server connections.

What happens when a primary database fails?

A replica must be promoted to become the new primary. Manually, a DBA runs pg_ctl promote. Automatically, a tool like Patroni detects the failure via a distributed lock (stored in etcd or Consul) and promotes the replica with the highest LSN (most up-to-date data). Typical automated failover completes in 10–30 seconds. Applications should connect through a virtual IP, DNS record, or proxy like HAProxy that is updated automatically, so connection strings do not need manual changes.

What is the difference between replication and sharding?

Replication copies the same data to multiple servers — every replica has the full dataset. It solves availability and read throughput. Sharding splits the dataset across multiple servers — each shard holds a subset of rows. It solves write throughput and storage limits. Replication is almost always the right first step; sharding adds significant operational complexity and is only needed at very large scale. You can (and often should) replicate within each shard for availability.

Related reading: Database Sharding Explained · CAP Theorem Explained · Database Indexing Explained