replication

name	replication
description	Comprehensive database replication management with streaming replication,...
shortcut	repl

Database Replication Manager

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 "========================================="
echo ""

# ===== PRIMARY SERVER CONFIGURATION =====

setup_primary() {
    echo "Configuring PRIMARY server: $PRIMARY_HOST"
    echo ""

    # 1. Configure postgresql.conf for replication
    cat >> /etc/postgresql/14/main/postgresql.conf <<EOF

# ========== REPLICATION SETTINGS ==========
# Added by replication setup script

# Write-Ahead Log (WAL) settings
wal_level = replica                    # Enable WAL for replication
max_wal_senders = 10                   # Max concurrent replication connections
wal_keep_size = 1024                   # Keep 1GB of WAL segments (PostgreSQL 13+)
max_replication_slots = 10             # For replication slots (recommended)

# Synchronous replication (optional - for zero data loss)
# synchronous_standby_names = 'replica1'  # Uncomment for sync replication
synchronous_commit = local             # Options: off, local, remote_write, remote_apply, on

# Archive WAL for point-in-time recovery (optional)
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300                  # Force WAL switch every 5 minutes

# Hot standby settings
hot_standby = on                       # Allow reads on replica
hot_standby_feedback = on              # Prevent query conflicts

# ========================================
EOF

    
     -p /var/lib/postgresql/wal_archive
     postgres:postgres /var/lib/postgresql/wal_archive
     700 /var/lib/postgresql/wal_archive

    
     >> /etc/postgresql/14/main/pg_hba.conf <<

    
     -u postgres psql <<

    
    systemctl restart postgresql@14-main

     
     
     
     
     -u postgres psql -c 
     -u postgres psql -c 
}



() {
     
     

    
    systemctl stop postgresql@14-main

    
     [ -d  ]; 
          
    

    
     
     -u postgres pg_basebackup \
        -h  \
        -D  \
        -U  \
        -P \
        -v \
        -R \
        -X stream \
        -C \
        -S replica1_slot

    
    
    
    

    
     >> /postgresql.auto.conf <<

    
     -R postgres:postgres 
     700 

    
    systemctl start postgresql@14-main

     
     
     
     
     -u postgres psql -c   
     -u postgres psql -c 
}



() {
     
     

    
     
     -u postgres psql -h  -U  postgres <<

     
     
     -u postgres psql -h  postgres <<

     
     
    
    LAG=$( -u postgres psql -h  -U postgres postgres -t -c \
        )

     (( $(echo " < " | bc -l) )); 
         
    
         
    
}



  
    primary)
        setup_primary
        ;;
    replica)
        setup_replica
        ;;
    verify)
        verify_replication
        ;;
    *)
         
         
         
         
         
         1
        ;;

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'
)
logger = logging.getLogger(__name__)


class ServerRole(Enum):
    """Database server role."""
    PRIMARY = "primary"
    REPLICA = "replica"
    UNKNOWN = "unknown"


@dataclass
class ReplicationStatus:
    """Replication status information."""
    is_primary: bool
    is_replica: bool
    replication_lag_seconds: Optional[float]
    wal_receive_lsn: Optional[str]
    wal_replay_lsn: Optional[str]
    connected_replicas: int


class PostgreSQLFailoverManager:
    """
    Manages automatic failover for PostgreSQL streaming replication.
    """

    def __init__(
        self,
        primary_host: str,
        replica_host: ,
        postgres_user:  = ,
        postgres_password:  = ,
        failover_threshold_seconds:  = ,
        alert_webhook: [] = 
    ):
        
        .primary_host = primary_host
        .replica_host = replica_host
        .postgres_user = postgres_user
        .postgres_password = postgres_password
        .failover_threshold = failover_threshold_seconds
        .alert_webhook = alert_webhook

        .primary_down_since: [] = 

     () -> :
        
        :
            conn = psycopg2.connect(
                host=host,
                user=.postgres_user,
                password=.postgres_password,
                database=,
                connect_timeout=
            )
            conn.close()
             

         Exception  e:
            logger.error()
             

     () -> [ReplicationStatus]:
        
        :
            conn = psycopg2.connect(
                host=host,
                user=.postgres_user,
                password=.postgres_password,
                database=,
                connect_timeout=
            )

             conn.cursor()  cur:
                
                cur.execute()
                is_replica = cur.fetchone()[]
                is_primary =  is_replica

                
                replication_lag = 
                wal_receive_lsn = 
                wal_replay_lsn = 

                 is_replica:
                    cur.execute()
                    row = cur.fetchone()
                    replication_lag = row[]
                    wal_receive_lsn = row[]
                    wal_replay_lsn = row[]

                
                connected_replicas = 
                 is_primary:
                    cur.execute()
                    connected_replicas = cur.fetchone()[]

            conn.close()

             ReplicationStatus(
                is_primary=is_primary,
                is_replica=is_replica,
                replication_lag_seconds=replication_lag,
                wal_receive_lsn=wal_receive_lsn,
                wal_replay_lsn=wal_replay_lsn,
                connected_replicas=connected_replicas
            )

         Exception  e:
            logger.error()
             

     () -> :
        
        logger.info()

        :
            
            
            conn = psycopg2.connect(
                host=replica_host,
                user=.postgres_user,
                password=.postgres_password,
                database=
            )

             conn.cursor()  cur:
                
                cur.execute()
                conn.commit()

            conn.close()

            
            time.sleep()

            
            status = .get_replication_status(replica_host)
             status  status.is_primary:
                logger.info()
                 
            :
                logger.error()
                 

         Exception  e:
            logger.error()
             

     () -> :
        
          .alert_webhook:
            

        emoji_map = {
            : ,
            : ,
            : ,
            : 
        }

        payload = {
            : ,
            : [{
                :   severity  [, ]  ,
                : message,
                : ,
                : (time.time())
            }]
        }

        :
            requests.post(.alert_webhook, json=payload, timeout=)
         Exception  e:
            logger.error()

     () -> :
        
        logger.info()

         :
            :
                
                primary_healthy = .check_server_health(.primary_host)

                  primary_healthy:
                     .primary_down_since  :
                        
                        .primary_down_since = time.time()
                        logger.warning()
                        .send_alert(
                            
                            ,
                            severity=
                        )

                    
                    down_duration = time.time() - .primary_down_since

                     down_duration >= .failover_threshold:
                        logger.critical(
                            
                            
                        )

                        .send_alert(
                            
                            
                            ,
                            severity=
                        )

                        
                        success = .promote_replica_to_primary(.replica_host)

                         success:
                            .send_alert(
                                
                                ,
                                severity=  
                            )
                            
                            
                        :
                            .send_alert(
                                
                                ,
                                severity=
                            )
                            

                :
                    
                     .primary_down_since   :
                        
                        logger.info()
                        .send_alert(
                            ,
                            severity=
                        )
                        .primary_down_since = 

                    
                    replica_status = .get_replication_status(.replica_host)

                     replica_status:
                        lag = replica_status.replication_lag_seconds  

                         lag > :
                            logger.warning()
                            .send_alert(
                                ,
                                severity=
                            )
                        :
                            logger.info(
                                
                                
                            )

                
                time.sleep()

             KeyboardInterrupt:
                logger.info()
                
             Exception  e:
                logger.error()
                time.sleep()



 __name__ == :
     argparse

    parser = argparse.ArgumentParser(description=)
    parser.add_argument(, required=, =)
    parser.add_argument(, required=, =)
    parser.add_argument(, default=, =)
    parser.add_argument(, default=, =)
    parser.add_argument(, =, default=, =)
    parser.add_argument(, =)

    args = parser.parse_args()

    manager = PostgreSQLFailoverManager(
        primary_host=args.primary,
        replica_host=args.replica,
        postgres_user=args.user,
        postgres_password=args.password,
        failover_threshold_seconds=args.threshold,
        alert_webhook=args.webhook
    )

    manager.monitor_and_failover()

Error Handling

Error	Cause	Solution
"could not connect to server"	Replica cannot reach primary	Check network connectivity, firewall rules, pg_hba.conf
"requested WAL segment already removed"	WAL files deleted before replica could receive them	Increase wal_keep_size or use replication slots
"replication slot does not exist"	Replica trying to use non-existent slot	Create slot on primary: SELECT pg_create_physical_replication_slot('slot_name')
"hot standby conflict"	Query on replica conflicts with recovery	Increase max_standby_streaming_delay or tune query cancellation
"timeline history file missing"	Replica and primary have diverged after failover	Rebuild replica from new primary using pg_basebackup

Configuration Options

Replication Modes

• Asynchronous (default): Best performance, small data loss risk
• Synchronous (synchronous_commit=on): Zero data loss, slower writes
• Remote write (synchronous_commit=remote_write): Balanced approach
• Remote apply (synchronous_commit=remote_apply): Strongest consistency

Replication Methods

• Streaming replication: Binary WAL streaming (physical replication)
• Logical replication: Selective table replication (PostgreSQL 10+)
• WAL shipping: Archive-based replication (for backups)

Failover Strategies

• Manual failover: DBA triggers promotion (safest)
• Automatic failover: Scripted promotion after health check failure
• Patroni/repmgr: Cluster management with automatic failover
• Cloud-managed: RDS/CloudSQL automatic failover

Best Practices

DO:

• Use replication slots to prevent WAL deletion before replica receives it
• Monitor replication lag continuously (alert at >10 seconds)
• Test failover procedures quarterly (disaster recovery drills)
• Use synchronous replication for critical write transactions only
• Implement connection pooling to handle failover reconnections
• Document failover runbooks with step-by-step procedures
• Keep replicas on same PostgreSQL major version as primary

DON'T:

• Run long-running queries on replicas without tuning conflict resolution
• Forget to update application connection strings after failover
• Use synchronous replication over high-latency networks (>50ms)
• Skip monitoring replication lag (leads to split-brain scenarios)
• Promote replica without verifying it's up-to-date
• Delete replication slots without stopping replicas first
• Ignore hot standby conflicts (causes query cancellations)

Performance Considerations

• Replication lag: <1s for local replicas, <5s for cross-region
• Write overhead: 5-10% for asynchronous, 20-50% for synchronous
• Network bandwidth: 10-50 Mbps per active replica
• Disk I/O: Replica writes same data as primary (similar load)
• Failover time (RTO): 10-60 seconds for automatic, 5-15 minutes for manual
• Data loss (RPO): 0 seconds (sync), 0-5 seconds (async)

Security Considerations

• Use strong passwords for replication user (20+ characters)
• Encrypt replication traffic with SSL/TLS (sslmode=require)
• Restrict replication connections in pg_hba.conf to specific IPs
• Audit all failover operations for compliance
• Rotate replication credentials quarterly
• Use dedicated replication user (not superuser)
• Enable connection logging for replication connections

Related Commands

• /database-backup-automator - Backup before major replication changes
• /database-health-monitor - Monitor replication lag and health
• /database-recovery-manager - PITR using WAL archives from replication
• /database-connection-pooler - Handle connection routing after failover

Version History

• v1.0.0 (2024-10): Initial implementation with streaming replication and automatic failover
• Planned v1.1.0: Add logical replication support, Patroni integration