Database Replication Patterns

Production engineering guide for database replication patterns covering patterns, implementation strategies, and operational best practices.

Database replication patterns describe how data is copied and kept consistent across multiple database instances. They are essential when a system needs to scale reads, improve availability, or achieve disaster recovery. Use replication when your database becomes a bottleneck under concurrent queries, when downtime must be minimized, or when data must be accessible locally to distributed users.

Read-Heavy Workloads: Master-Replica (Active-Passive) Replication

When most database operations are reads—queries, analytics, reporting—replication to dedicated read replicas reduces load on the master. This pattern uses one master database that handles all writes, and one or more replicas that serve reads. The master logs changes (write-ahead log or WAL) and forwards them to replicas.

The master applies writes and ships WAL records to replicas. Replicas apply these records in sequence, maintaining a lag that defines how fresh the data is. Replication lag is measured as the difference between the master’s current WAL position and the replica’s latest applied position.

To monitor replication lag in PostgreSQL:

SELECT
  pg_current_wal_lsn() AS master_lsn,
  pg_last_wal_replay_lsn() AS replica_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn()) AS lag_bytes
FROM pg_stat_replication;

Replication is configured via primary_conninfo in the recovery.conf file on each replica:

standby_mode = 'on'
primary_conninfo = 'host=master.example.com port=5432 user=replica password=secret'
primary_slot_name = 'replica_slot'

The primary_slot_name enables logical replication with a persistent slot, which tracks the WAL position up to which the replica has consumed data. If the replica crashes or reconnects, it resumes from the last known position.

Replication fails silently when the network between master and replica drops. The replica continues to replay WAL records but eventually stalls. If the master fails and the replica cannot reconnect, the replica stops applying changes until the connection resumes. To detect lag, applications can query a replication_lag view and route reads accordingly:

CREATE VIEW replication_lag AS
  SELECT
    application_name,
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
  FROM pg_stat_replication;

A replica is considered “up to date” when the lag is less than 100 MB. For real-time analytics, a lag of less than 1 MB is required. Applications use this to route queries: critical reads go to the master; less time-sensitive reads go to replicas.

High Availability: Synchronous Replication

When a single master failure cannot be tolerated, synchronous replication ensures that writes commit only after the data has been written to the master and at least one replica. This pattern reduces data loss during master failure.

In synchronous replication, the master waits for acknowledgments from replicas before returning success to the client. The master uses a synchronous_standby_names list in postgresql.conf to define which replicas must confirm each write.

synchronous_commit = on
synchronous_standby_names = 'replica1, replica2'

With synchronous_commit = on, every COMMIT waits for at least one synchronous standby to write the transaction to disk and acknowledge it. The master records the commit in the WAL and sends a SYNC message to each replica in the list.

If a replica in synchronous_standby_names becomes unreachable, the master can promote it to become a synchronous standby. If the primary replica (first in the list) fails, the master switches to the next replica in the list and begins using it for synchronous replication.

The protocol uses a replication message type in PostgreSQL’s binary protocol. The master sends a Sync message to the replica, and the replica responds with a StandbyStatusUpdate message containing its replay_lsn and flush_lsn. The master uses this to track progress.

Failure modes: if the network between master and a synchronous replica breaks, the master waits indefinitely for the replica’s acknowledgment. If the replica fails while being a synchronous standby, the master promotes a new replica and waits for the new one to become active. The master can be configured to fail over to the next replica in the list when the primary synchronous replica is unavailable.

A common mistake is setting synchronous_standby_names to include replicas that are not always available. A replica that is down for hours appears in the list but never confirms writes. The master blocks on COMMIT for long periods. To mitigate this, use synchronous_standby_names with a list of replicas and a minimum number of confirmations.

synchronous_standby_names = 'replica1, replica2, replica3'
num_synchronous_standby = 2

With num_synchronous_standby = 2, the master waits for two replicas to acknowledge the write. If only one replica is available, the master waits for two confirmations, possibly from the same replica. If both replicas fail, the master can fall back to asynchronous replication.

Multi-Master Replication: Active-Active with Conflict Resolution

When multiple databases must accept writes independently, and consistency across all nodes is desired, active-active replication is used. Each node is both a master and a replica. Writes occur locally, and changes propagate to all other nodes.

This pattern is common in distributed applications where users are geographically dispersed. Each region runs a database instance that accepts local writes and replicates changes globally.

In active-active replication, each node maintains a replication slot and applies changes received from other nodes. To avoid write conflicts—when two nodes write to the same row—conflict resolution is required.

PostgreSQL’s logical replication enables multi-master setups. Each node publishes changes via a publication, and each node subscribes to changes from others.

-- On each node, create a publication for all tables
CREATE PUBLICATION app_publication FOR ALL TABLES;

-- On each node, create a subscription to other nodes
CREATE SUBSCRIPTION app_subscription
  CONNECTION 'host=us-east.example.com port=5432 user=replica password=secret'
  PUBLICATION app_publication
  WITH (copy_data = true, slot_name = 'app_slot');

Each subscription uses a slot to track which changes have been applied. The copy_data = true flag ensures that the initial data is copied from the source node to the subscriber.

Conflict resolution occurs when two nodes write to the same row. The replica applies changes in the order of their lsn (Log Sequence Number), but if two nodes write to the same row at the same time, conflicts arise. The system must decide which write wins.

Common strategies include:

A typical conflict-handling function in PostgreSQL:

CREATE OR REPLACE FUNCTION resolve_conflict()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.updated_at > OLD.updated_at THEN
    UPDATE my_table SET value = NEW.value, updated_at = NEW.updated_at
    WHERE id = NEW.id;
    RETURN NEW;
  ELSE
    UPDATE my_table SET value = OLD.value, updated_at = OLD.updated_at
    WHERE id = NEW.id;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

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

Replication can fail silently when a node loses connectivity. The node continues writing locally but fails to propagate changes. When it reconnects, it applies a backlog of changes, but may miss earlier writes due to network delays. To detect this, nodes emit replication_lag metrics and trigger alerts when lag exceeds 1 minute.

The most common failure is replication loop: a node sends changes to another node, which applies them and sends them back, creating a feedback loop. This occurs when all nodes are in the same publication list and each node subscribes to the same publication. The solution is to use replication_filter or publication exclusions.

-- Exclude the local node’s own changes from being propagated
CREATE PUBLICATION app_publication
  FOR TABLE my_table
  WITH (publish = 'insert, update, delete', publish_via_partition = true);

-- On each node, exclude its own changes
ALTER PUBLICATION app_publication SET (publish = 'insert, update, delete');

Cross-Region Replication: Global Read Scalability and Disaster Recovery

When data must be available across multiple geographic regions, cross-region replication uses asynchronous replication with long-lived connections and network-optimized transfer.

Each region hosts a replica of the master database. Replicas are updated asynchronously, with a lag of minutes to hours. The master is usually in a central region, and replicas in edge regions serve local queries.

To reduce network latency, replicas are configured with wal_sender_timeout and wal_receiver_timeout to handle variable network conditions.

wal_sender_timeout = 30s
wal_receiver_timeout = 60s

Replicas use pg_basebackup to perform initial full backups and then stream WAL continuously.

pg_basebackup -h master.example.com -D /var/lib/postgresql/14/main \
  -U replica -P -X stream -R -v

The -R flag writes a postgresql.auto.conf file with connection settings, so the replica can auto-configure itself.

To promote a replica to master in case of failure, use pg_ctl promote:

pg_ctl promote -D /var/lib/postgresql/14/main

This command sets primary_conninfo to standby_mode = on, updates the recovery.conf file, and starts the database in primary mode.

Failure modes:

A common mistake is not configuring recovery.conf correctly. If standby_mode is missing, the replica remains in standby mode but does not apply WAL updates. If primary_conninfo is incorrect, the replica cannot reconnect after a restart.

To ensure consistency across regions, applications use a region column in the users table and route queries based on user location. When a user in Tokyo writes to the database, the write is sent to the Tokyo replica, which applies it locally and sends it to the master. The master then propagates the change to other regions.

Replication Health: Monitoring and Diagnosis

Replication health is monitored through a combination of metrics and logs. Key metrics include:

Use pg_stat_replication to gather replication status:

SELECT
  application_name,
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
  pg_wal_lsn_diff(replay_lsn, pg_current_wal_lsn()) AS replay_lag
FROM pg_stat_replication;

A replica is considered “catching up” when replay_lsn is behind sent_lsn. It is “idle” when replay_lsn is equal to sent_lsn.

Log messages from pg_log or journalctl reveal replication issues:

To diagnose replication lag:

  1. Check pg_stat_replication for current lag.
  2. Query pg_wal_lsn_diff to compute lag in bytes.
  3. Use pg_current_wal_lsn() and pg_last_wal_replay_lsn() to track progress.
  4. Monitor pg_stat_wal_receiver for receive rate and errors.

Failure is silent when the replica applies WAL but the master never receives confirmation. The master assumes the replica is up, but the replica is stuck. Use pg_stat_wal_receiver to detect this:

SELECT
  pid,
  wal_receiver_start_lsn,
  wal_receiver_lsn,
  wal_receiver_state,
  wal_receiver_lsn_diff
FROM pg_stat_wal_receiver;

A wal_receiver_state of streaming indicates active WAL reception. A state of catching up means the replica is applying a backlog of WAL records. A state of idle means the replica is waiting for new WAL.

Replication is a foundational pattern for modern databases. It enables scalability, availability, and resilience. Master-replica, synchronous, multi-master, and cross-region setups each solve a core problem. But success depends on understanding the mechanics of replication, not just the configuration.

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.