| name | managing-database-replication |
| description | Process use when you need to work with database scalability.
This skill provides replication and sharding with comprehensive guidance and automation.
Trigger with phrases like "set up replication", "implement sharding",
or "scale database".
|
| allowed-tools | Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*) |
| version | 1.0.0 |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| license | MIT |
| compatible-with | claude-code, codex, openclaw |
| tags | ["database","scaling","database-replication"] |
Database Replication Manager
Overview
Configure and manage database replication topologies for PostgreSQL (streaming replication, logical replication), MySQL (source-replica, group replication), and MongoDB (replica sets). This skill covers primary-replica setup, read scaling through replica routing, failover automation, replication lag monitoring, and conflict resolution for multi-primary configurations.
Prerequisites
- Superuser or replication-role credentials on primary and replica servers
- Network connectivity between all replication nodes (verify with
pg_isready or mysqladmin ping)
psql, mysql, or mongosh CLI tools installed on all nodes- Matching major database versions across all replication nodes
- Sufficient disk space on replicas (equal to or greater than primary)
- SSH access to replica servers for initial base backup transfer
Instructions
-
Choose the replication topology based on requirements:
- Single primary + read replicas: Best for read-heavy workloads. All writes go to primary; reads distributed across replicas.
- Multi-primary (active-active): Best for geographic distribution. Requires conflict resolution. Use PostgreSQL logical replication or MySQL Group Replication.
- Cascading replication: Replica A replicates from primary, Replica B replicates from Replica A. Reduces primary load for many replicas.
-
For PostgreSQL streaming replication, configure the primary:
- Set
wal_level = replica, max_wal_senders = 10, max_replication_slots = 10
- Create replication user:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password'
- Add replication entry to
pg_hba.conf: host replication replicator replica_ip/32 scram-sha-256
- Reload configuration:
SELECT pg_reload_conf()
-
Initialize the replica with a base backup: pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/data -Fp -Xs -P -R. The -R flag creates standby.signal and configures primary_conninfo automatically.
-
For MySQL source-replica replication, configure the source:
