Database Replication and High Availability Strategies

Database replication is the foundation of high availability systems, ensuring data remains accessible even during hardware failures, network outages, or maintenance windows. This comprehensive guide explores replication strategies, failover mechanisms, and best practices for building resilient database architectures.

High availability infrastructure — Database replication and high availability

Understanding Database Replication

Database replication involves maintaining multiple copies of data across different servers or geographic locations. The primary goals are high availability, disaster recovery, and read scalability^[1].

Core Replication Concepts

Primary (Master): The authoritative database instance that accepts writes Replica (Slave/Secondary): Copies that receive updates from the primary Replication Lag: Time delay between write on primary and appearance on replica Synchronous Replication: Writes confirmed only after replica acknowledgment Asynchronous Replication: Writes confirmed immediately, replicas update eventually

The choice between synchronous and asynchronous replication involves fundamental trade-offs between consistency and performance.

Replication Topologies

Different topologies suit different requirements for availability, performance, and complexity.

Primary-Replica (Master-Slave)

The most common topology: one primary accepts writes, multiple replicas handle reads.

-- PostgreSQL streaming replication configuration
-- On primary server (postgresql.conf)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

-- On replica server (postgresql.conf)
hot_standby = on

-- Connection configuration (pg_hba.conf on primary)
host replication replicator 192.168.1.0/24 md5

Advantages:

Simple to understand and implement
Good read scalability (distribute reads across replicas)
Clear separation of write and read workloads

Disadvantages:

Primary is single point of failure for writes
Replica lag can cause inconsistent reads
Manual intervention often required for failover

Topology	Write Availability	Read Scalability	Consistency	Complexity
Primary-Replica	Single point	High	Eventual	Low
Multi-Primary	Very High	High	Complex	High
Primary-Primary	High	Medium	Requires coordination	Medium
Chain Replication	Single point	Medium	Strong	Medium

Multi-Primary (Multi-Master)

Multiple nodes accept writes simultaneously, replicating changes bidirectionally.

// MongoDB replica set with multiple primaries
// Note: MongoDB uses single primary by default
// For multi-region writes, use sharding with zone-aware distribution

// MySQL Group Replication multi-primary mode
-- On each server
plugin-load-add=group_replication.so
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

Conflict resolution strategies:

Last Write Wins (LWW): Most recent timestamp wins
Application-defined: Custom business logic resolves conflicts
CRDT (Conflict-free Replicated Data Types): Mathematical guarantees prevent conflicts
Manual resolution: Flag conflicts for human review

Multi-primary replication is powerful but introduces conflict resolution complexity that must be carefully managed^[2].

Synchronous vs Asynchronous Replication

The synchronization model fundamentally affects system behavior under failure conditions.

Synchronous Replication

Transactions commit only after at least one replica confirms receipt.

-- PostgreSQL synchronous replication
-- In postgresql.conf on primary
synchronous_commit = on
synchronous_standby_names = 'replica1,replica2'

-- In application
BEGIN;
INSERT INTO orders (customer_id, amount) VALUES (123, 99.99);
COMMIT;  -- Blocks until replica1 or replica2 confirms

Characteristics:

Zero data loss on primary failure (if replica confirms)
Higher latency for writes (network round-trip to replica)
Reduced availability (write failure if replica unreachable)
Strong consistency guarantees

Synchronous replication is appropriate for financial transactions or other use cases where data loss is unacceptable.

Asynchronous Replication

Transactions commit immediately; replicas update independently.

-- PostgreSQL asynchronous replication (default)
synchronous_commit = off

-- MySQL asynchronous replication
-- On primary server (my.cnf)
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW

-- On replica server
CHANGE MASTER TO
  MASTER_HOST='primary.example.com',
  MASTER_USER='replicator',
  MASTER_PASSWORD='secret',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;
START SLAVE;

Characteristics:

Lower latency for writes (no replica acknowledgment needed)
Higher availability (write success independent of replica state)
Risk of data loss (uncommitted changes lost if primary fails)
Eventual consistency (replicas may lag behind primary)

For most web applications, asynchronous replication provides the best balance of performance and availability.

Semi-Synchronous Replication

A middle ground: wait for replica to receive (but not necessarily commit) the transaction.

-- MySQL semi-synchronous replication
-- On primary
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000; -- 10 seconds

-- On replica
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

This provides better durability than asynchronous replication with lower latency impact than synchronous replication.

Handling Replication Lag

Replication lag is inevitable with asynchronous replication. Understanding and managing it is crucial for application correctness.

Monitoring Replication Lag

-- PostgreSQL: Check replication lag
SELECT 
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS pending_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- MySQL: Check replication lag
SHOW SLAVE STATUS\G
-- Look at: Seconds_Behind_Master

-- MongoDB: Check replication lag
rs.printSlaveReplicationInfo()

Acceptable lag thresholds:

Real-time applications: < 1 second
Reporting/analytics: < 60 seconds
Disaster recovery standby: < 300 seconds

Application Strategies for Handling Lag

Read-Your-Writes Consistency:

import time
import redis

# Use session stickiness or versioning
def get_user_profile(user_id, last_write_timestamp=None):
    if last_write_timestamp:
        # Ensure we read after the write completed
        max_wait = 5  # seconds
        start = time.time()
        
        while time.time() - start < max_wait:
            profile = fetch_from_replica(user_id)
            if profile['updated_at'] >= last_write_timestamp:
                return profile
            time.sleep(0.1)
        
        # Fallback to primary if lag too high
        return fetch_from_primary(user_id)
    
    return fetch_from_replica(user_id)

Critical Reads from Primary:

def get_account_balance(account_id, force_primary=False):
    if force_primary:
        # Financial data always from primary
        return fetch_from_primary(account_id)
    else:
        # Other queries can use replica
        return fetch_from_replica(account_id)

Automatic Failover Mechanisms

Automatic failover ensures high availability by promoting a replica to primary when the current primary fails.

PostgreSQL Automatic Failover

Using Patroni for automatic failover:

## Patroni configuration (patroni.yml)
scope: postgres-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: node1.example.com:8008

etcd:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: on
        max_wal_senders: 5

postgresql:
  listen: 0.0.0.0:5432
  connect_address: node1.example.com:5432
  data_dir: /var/lib/postgresql/13/main
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: secret
    superuser:
      username: postgres
      password: secret

Patroni features:

Automatic leader election using etcd/Consul/Zookeeper
Health checking and automatic failover (typically < 30 seconds)
Prevents split-brain scenarios
Integrates with HAProxy for connection routing

MySQL/MariaDB Automatic Failover

Using MHA (Master High Availability):

## MHA Manager configuration (app1.cnf)
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
remote_workdir=/var/log/mha/app1
ssh_user=mha
repl_user=replicator
repl_password=secret
ping_interval=3
master_ip_failover_script=/usr/local/bin/master_ip_failover

[server1]
hostname=db1.example.com
candidate_master=1
check_repl_delay=0

[server2]
hostname=db2.example.com
candidate_master=1
check_repl_delay=0

[server3]
hostname=db3.example.com
no_master=1

MHA monitors MySQL replication and automatically promotes the most up-to-date replica during primary failure.

MongoDB Replica Set Automatic Failover

MongoDB has built-in automatic failover:

// Configure replica set with proper priorities
rs.reconfig({
  _id: "myReplicaSet",
  members: [
    { 
      _id: 0, 
      host: "mongo1.example.com:27017",
      priority: 2,  // Higher priority = preferred primary
      votes: 1 
    },
    { 
      _id: 1, 
      host: "mongo2.example.com:27017",
      priority: 1,
      votes: 1 
    },
    { 
      _id: 2, 
      host: "mongo3.example.com:27017",
      priority: 0,  // Never becomes primary
      votes: 1,
      hidden: true  // Hidden from application
    }
  ]
})

MongoDB’s election process typically completes in 10-12 seconds, minimizing downtime.

Geographic Distribution and Multi-Region Replication

Global network infrastructure — Multi-region database replication

Geographic distribution improves disaster recovery and reduces latency for global users^[3].

Cross-Region Replication Architecture

Region 1 (US-East)          Region 2 (US-West)         Region 3 (EU)
    Primary                      Replica                   Replica
       ↓                           ↑                         ↑
   WAN Replication ---------------+-------------------------+

Considerations for multi-region replication:

Network latency: WAN latency (50-200ms) affects synchronous replication
Bandwidth costs: Replication traffic between regions can be expensive
Split-brain prevention: Need reliable leader election mechanism
Regional failover: Balance between data durability and availability

PostgreSQL Physical vs Logical Replication

Physical Replication (Streaming):

-- Replicates entire cluster, binary-level
-- Fast but inflexible
-- On replica: automatic streaming from primary

Logical Replication:

-- Selective replication of tables/schemas
-- On primary
CREATE PUBLICATION users_publication FOR TABLE users, user_profiles;

-- On replica
CREATE SUBSCRIPTION users_subscription
  CONNECTION 'host=primary.example.com dbname=mydb user=replicator password=secret'
  PUBLICATION users_publication;

Logical replication enables:

Selective replication of specific tables
Cross-version replication (e.g., PostgreSQL 12 → 13)
Data aggregation from multiple sources
Zero-downtime upgrades using switchover

Monitoring and Alerting

Comprehensive monitoring prevents surprise outages and enables proactive problem resolution.

Critical Metrics to Monitor

-- Replication health dashboard queries

-- 1. Replication lag (PostgreSQL)
SELECT 
    application_name,
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS lag_mb,
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds
FROM pg_stat_replication;

-- 2. Replication errors (MySQL)
SELECT * FROM performance_schema.replication_connection_status
WHERE SERVICE_STATE != 'ON' OR LAST_ERROR_NUMBER != 0;

-- 3. Replication throughput
SELECT 
    datname,
    numbackends,
    xact_commit + xact_rollback AS transactions,
    blks_read,
    blks_hit,
    round(blks_hit::numeric / (blks_hit + blks_read + 0.001), 4) AS cache_hit_ratio
FROM pg_stat_database;

Alert thresholds:

Replication lag > 10 seconds: Warning
Replication lag > 60 seconds: Critical
Replication stopped: Critical
Replica disk > 85% full: Warning
Primary-replica connection errors: Critical

Backup Strategies with Replication

Replication is not a backup. Deletes and corruption replicate just like normal operations. You need separate backup strategy^[4].

Backup from Replica

Taking backups from replicas reduces load on primary:

## PostgreSQL backup from replica (using pg_basebackup)
pg_basebackup -h replica.example.com -D /backup/postgres-$(date +%Y%m%d) \
  -U replicator -P -v --wal-method=stream

## MySQL backup from replica (using mysqldump)
mysqldump -h replica.example.com -u backup_user -p \
  --single-transaction --routines --triggers --all-databases \
  > backup-$(date +%Y%m%d).sql

## MongoDB backup from secondary
mongodump --host=replica.example.com:27017 --out=/backup/mongo-$(date +%Y%m%d)

Best practices:

Use dedicated backup replica with no application traffic
Schedule during low-traffic periods
Verify backup integrity regularly (monthly restores)
Retain multiple generations (daily for 7 days, weekly for 4 weeks, monthly for 12 months)
Store backups in different geographic region

Conclusion

Database replication and high availability require careful planning and ongoing maintenance. The right strategy depends on your specific requirements for consistency, availability, and performance.

Key recommendations:

Start with primary-replica topology for simplicity
Use asynchronous replication for performance, synchronous for critical data
Implement automatic failover with tools like Patroni, MHA, or native capabilities
Monitor replication lag continuously and set appropriate alerts
Design applications to handle replica lag gracefully
Implement separate backup strategy - replication is not backup
Test failover procedures regularly (quarterly minimum)
Consider geographic distribution for disaster recovery

Properly implemented replication can achieve 99.95% availability (< 5 hours downtime per year) while providing read scalability and disaster recovery capabilities. The investment in robust replication architecture pays dividends in reliability and user trust.

References

[1] Kleppmann, M. (2017). Designing Data-Intensive Applications. O’Reilly Media. Available at: https://dataintensive.net/ (Accessed: November 2025)

[2] Helland, P. (2012). Building on Quicksand. ACM Queue. Available at: https://queue.acm.org/detail.cfm?id=2462076 (Accessed: November 2025)

[3] PostgreSQL Global Development Group. (2024). High Availability, Load Balancing, and Replication. Available at: https://www.postgresql.org/docs/current/high-availability.html (Accessed: November 2025)

[4] Percona. (2024). MySQL Replication: Best Practices. Available at: https://www.percona.com/blog/mysql-replication-best-practices/ (Accessed: November 2025)

[5] Kreps, J. (2013). The Log: What every software engineer should know about real-time data. Available at: https://engineering.linkedin.com/distributed-systems/log-what-every-software-engineer-should-know-about-real-time-datas-unifying (Accessed: November 2025)