Replication

Implement production-grade database replication for PostgreSQL and MySQL with streaming replication (physical), logical replication (selective tables), synchronous and asynchronous modes, automatic failover, lag monitoring, conflict resolution, and read scaling across multiple replicas. Achieve 99.99% availability with RPO <5 seconds and RTO <30 seconds for automated failover.

When to Use This Command

Use /replication when you need to:

• Implement high availability with automatic failover (99.99%+ uptime)
• Scale read workloads across multiple replicas (10x read capacity)
• Create disaster recovery instances in different regions
• Enable zero-downtime database migrations and upgrades
• Implement read-heavy application architectures
• Meet compliance requirements for data redundancy

DON'T use this when:

• Single server handles all load comfortably (<50% CPU)
• Database size is small (<10GB) and backup/restore is fast
• Application doesn't support read replica routing
• Network latency between regions is high (>100ms for sync replication)
• You lack monitoring infrastructure for replication lag
• Write workload is too heavy for replication to keep up

Design Decisions

This command implements automated replication with failover because:

• Streaming replication provides real-time data synchronization
• Automatic failover reduces RTO from hours to seconds
• Read replicas enable horizontal read scaling
• Logical replication allows selective table replication
• Synchronous mode ensures zero data loss for critical transactions

Alternative considered: Application-level read/write splitting

• No replication overhead at database level
• Requires application changes for every database interaction
• More complex error handling and retry logic
• Recommended when replication infrastructure unavailable

Alternative considered: Database clustering (Patroni, Galera)

• Multi-master with automatic failover
• More complex setup and maintenance
• Better for write-heavy workloads
• Recommended for high-write applications requiring HA

Prerequisites

Before running this command:

1. Primary and replica servers with network connectivity
2. Sufficient disk space for WAL archiving (30-50% of database size)
3. Monitoring system for replication lag alerts
4. Understanding of RPO (Recovery Point Objective) and RTO requirements
5. Tested failover procedures and runbooks

Implementation Process

Step 1: Configure Primary for Replication

Enable WAL archiving, set max_wal_senders, and create replication user.

Step 2: Initialize Replica with Base Backup

Use pg_basebackup to clone primary database to replica server.

Step 3: Configure Replica Connection

Set primary_conninfo and start replica in standby mode.

Step 4: Verify Replication Status

Check replication lag and ensure WAL streaming is active.

Step 5: Implement Monitoring and Failover

Deploy replication lag alerts and automatic failover scripts.

Output Format

The command generates:

• replication/primary_setup.sql - Primary configuration and replication user
• replication/replica_setup.sh - Automated replica initialization script
• replication/failover.py - Automatic failover orchestration
• replication/monitoring.yml - Prometheus/Grafana replication metrics
• replication/recovery.conf - Replica recovery configuration

Code Examples

Example 1: PostgreSQL Streaming Replication Setup

#!/bin/bash
#
# Production-ready PostgreSQL streaming replication setup
# with automatic failover and monitoring integration
#

set -e

# Configuration
PRIMARY_HOST="${PRIMARY_HOST:-primary.example.com}"
REPLICA_HOST="${REPLICA_HOST:-replica.example.com}"
REPLICATION_USER="${REPLICATION_USER:-replicator}"
REPLICATION_PASSWORD="${REPLICATION_PASSWORD:-changeme}"
POSTGRES_DATA_DIR="/var/lib/postgresql/14/main"

echo "========================================="
echo "PostgreSQL Streaming Replication Setup"
echo "========================================="
…

Example 2: Automated Failover Script with Prometheus Integration

#!/usr/bin/env python3
"""
Production-ready PostgreSQL automatic failover script with
health checks, monitoring integration, and rollback capability.
"""

import psycopg2
import time
import logging
import subprocess
from typing import Optional, Dict
from dataclasses import dataclass
from enum import Enum
import requests

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
…

Error Handling

Configuration Options

Replication Modes

• Asynchronous (default): Best performance, small data loss risk
• Synchronous (synchronous_commit=on): Zero data loss, slower writes