Database Replication Topologies

Design and operate database replication for high availability, disaster recovery, and read scaling. Covers single-leader, multi-leader, and leaderless replication, replication lag, conflict resolution, and the patterns that keep data consistent across replicas.

Database replication topologies define how data flows between primary and secondary database instances to ensure high availability, scalability, and durability. They matter when a single database instance cannot meet the demands of read-heavy workloads, failover requirements, or geographical distribution. This page details the practical patterns, configurations, and pitfalls of designing replication topologies using widely adopted standards—SQL, HTTP, and system-level primitives—without tying the mechanics to any one platform.

Core Patterns: When to Choose a Topology

Scaling Read Workloads with Master-Slave Replication

Use master-slave replication when read traffic exceeds write traffic and data consistency can tolerate some delay between write and read visibility.

The primary database (master) accepts all writes. Secondary databases (slaves) apply changes from the master’s write-ahead log (WAL) to maintain a copy of the data. Clients route reads to slaves and writes to the master. This topology reduces load on the master and scales reads independently.

To set up replication in PostgreSQL, configure the master to archive WAL files and enable streaming replication:


wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archives/%f'

On each slave, create a standby.signal file and set up recovery.conf:

# recovery.conf on slave
primary_conninfo = 'host=master.example.com port=5432 user=replication password=secret'
restore_command = 'cp /var/lib/postgresql/archives/%f %p'

After starting the slave, the system connects to the master and begins streaming WAL records. The slave applies changes continuously and becomes a live replica.

Common failure modes:

Use pg_isready to monitor replication lag:

pg_isready -h master.example.com -p 5432 -d postgres -t 10

If pg_isready returns 1, the master is not ready. A 0 indicates readiness. A script can use this to fail over to a slave.

Ensuring High Availability with Failover

Failover ensures service continuity when the primary database becomes unavailable. The process involves detecting a failure and promoting a secondary to primary.

Detection relies on health checks. Use curl to test a database’s availability endpoint:

curl -f http://master.example.com:8080/health

If the response is 200 OK, the master is alive. A 503 Service Unavailable or timeout indicates failure.

Promotion is triggered when a health check fails for a configured duration. A common pattern is to use a pg_ctl command to promote a standby:

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

After promotion, the former slave becomes a master and starts accepting writes. Clients must update connection strings to point to the new master.

For automatic failover, use a process manager like systemd or supervisord. Define a systemd service for the failover script:

# /etc/systemd/system/failover.service
[Unit]
Description=Database Failover
After=network.target

[Service]
Type=oneshot
ExecStart=/opt/scripts/failover.sh
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target

The failover.sh script checks for master failure and promotes a slave:

#!/bin/bash
set -euo pipefail

MASTER_HOST="master.example.com"
SLAVE_HOST="slave-01.example.com"

if ! curl -f http://$MASTER_HOST:8080/health; then
    echo "Master is down. Promoting $SLAVE_HOST to master."
    ssh postgres@$SLAVE_HOST "pg_ctl promote -D /var/lib/postgresql/data"
    # Update DNS or load balancer
    curl -X POST http://lb.example.com/api/v1/failover \
         -d '{"new_master": "'$SLAVE_HOST'"}'
fi

Sharp edges:

Handling Write Scalability with Multi-Master Replication

When writes are frequent and require low latency, use multi-master replication to distribute writes across multiple nodes, each capable of handling updates.

Each node in a multi-master cluster acts as both a master and a slave. Changes are replicated to all other nodes. This topology enables geographic distribution and concurrent write operations.

To configure multi-master replication in PostgreSQL, set up streaming replication between each pair of nodes and enable logical replication for schema and data changes.

On each node, set wal_level = logical and create a publication for all tables:

CREATE PUBLICATION all_tables FOR ALL TABLES;

On each node, create a subscription to the other nodes:

CREATE SUBSCRIPTION node1_to_node2
  CONNECTION 'host=node1.example.com port=5432 dbname=app user=replication password=secret'
  PUBLICATION all_tables
  WITH (copy_data = true);

This ensures that each node receives changes from the others. Transactions are applied in the order of their WAL positions.

Failure modes:

Configuration pitfalls:

Reducing Latency with Cascading Replication

Use cascading replication to reduce network load on the master and extend replication depth in large-scale deployments.

In cascading replication, each slave serves as a master for one or more downstream slaves. The master sends WAL records to its immediate slaves, which then stream those logs to their own slaves.

To configure cascading replication, set up a chain of recovery.conf files. The first slave connects directly to the master; subsequent slaves connect to their upstream slaves.

For example, with three nodes: master → slave-01 → slave-02.

On slave-01:

primary_conninfo = 'host=master.example.com port=5432 user=replication password=secret'
restore_command = 'cp /var/lib/postgresql/archives/%f %p'

On slave-02:

primary_conninfo = 'host=slave-01.example.com port=5432 user=replication password=secret'
restore_command = 'cp /var/lib/postgresql/archives/%f %p'

The master sends WAL records to slave-01, which forwards them to slave-02. This reduces the number of connections to the master and enables deeper replication hierarchies.

Failure modes:

Configuration tips:

SELECT
  application_name,
  state,
  lag,
  last_msg_send_time,
  last_msg_receipt_time
FROM pg_stat_replication;

Ensuring Data Consistency with Active-Active Topologies

Use active-active replication when write traffic is high and data must be available across multiple data centers.

Each node accepts writes and replicates them to all other nodes. Data consistency is maintained through conflict resolution and eventual consistency models.

To implement active-active replication in PostgreSQL, use logical replication with bidirectional subscriptions and conflict resolution.

Define a publication on each node:

CREATE PUBLICATION active_active FOR ALL TABLES WITH (copy_data = true);

Create subscriptions from each node to every other node:

CREATE SUBSCRIPTION node1_to_node2
  CONNECTION 'host=node1.example.com port=5432 dbname=app user=replication password=secret'
  PUBLICATION active_active
  WITH (copy_data = true, create_slot = true, slot_name = 'node1_node2');

For conflict resolution, use a trigger function to detect and resolve conflicts:

CREATE OR REPLACE FUNCTION resolve_conflict()
RETURNS trigger AS $$
BEGIN
  IF NEW.id = OLD.id THEN
    -- Update with latest timestamp
    IF NEW.updated_at > OLD.updated_at THEN
      UPDATE table SET value = NEW.value, updated_at = NEW.updated_at
      WHERE id = NEW.id;
    ELSE
      UPDATE table SET value = OLD.value, updated_at = OLD.updated_at
      WHERE id = NEW.id;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER conflict_trigger
  AFTER INSERT OR UPDATE ON table
  FOR EACH ROW EXECUTE FUNCTION resolve_conflict();

Key challenges:

Configuration nuances:

When to Combine Topologies

Combine master-slave with cascading replication to balance load and reduce network pressure. Use multi-master with active-active to enable global data consistency. Use failover with cascading to ensure that a failure in one tier propagates through the chain.

The most common configuration is a master with multiple cascading slaves, where the master handles writes and the first layer of slaves handles reads. If the master fails, the top slave becomes the new master, and the chain rebuilds around it.

To promote a slave and rebuild the chain:

# Promote slave-01 to master
ssh [email protected] 'pg_ctl promote -D /var/lib/postgresql/data'

# Update all downstream slaves to connect to slave-01
for slave in slave-02 slave-03 slave-04; do
  ssh postgres@$slave "sed -i 's/old-master/slave-01/' /var/lib/postgresql/data/recovery.conf"
done

Each change must be tested with pg_isready and monitored with systemctl status and journalctl.

Common Configuration Pitfalls

Replication is not a one-time setup. It requires monitoring, health checks, and failover scripts. The choice of topology is not a design decision—it is a continuous operational practice.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.