Menu
Comprehensive database replication management with streaming replication,...
The review-first prompt is selected by default. You can switch to a direct command or download a local copy.
Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
npx skills add https://github.com/tools-only/X-Skills --skill replicationThe command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
Index of Build Systems Skills
Coordination patterns for distributed dataflow systems including barriers, epochs, and distributed snapshots
Windowing, sessionization, time-series aggregation, and late data handling for streaming systems
Based on SOC occupation classification
Showing SKILL.md
| name | replication |
| description | Comprehensive database replication management with streaming replication,... |
| shortcut | repl |
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.
Use /replication when you need to:
DON'T use this when:
This command implements automated replication with failover because:
Alternative considered: Application-level read/write splitting
Alternative considered: Database clustering (Patroni, Galera)
Before running this command:
Enable WAL archiving, set max_wal_senders, and create replication user.
Use pg_basebackup to clone primary database to replica server.
Set primary_conninfo and start replica in standby mode.
Check replication lag and ensure WAL streaming is active.
Deploy replication lag alerts and automatic failover scripts.
The command generates:
replication/primary_setup.sql - Primary configuration and replication userreplication/replica_setup.sh - Automated replica initialization scriptreplication/failover.py - Automatic failover orchestrationreplication/monitoring.yml - Prometheus/Grafana replication metricsreplication/recovery.conf - Replica recovery configuration#!/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
;;
#!/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 | 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 |
Replication Modes
synchronous_commit=on): Zero data loss, slower writessynchronous_commit=remote_write): Balanced approachsynchronous_commit=remote_apply): Strongest consistencyReplication Methods
Failover Strategies
DO:
DON'T:
sslmode=require)/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