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
wallevel = replica, maxwalsenders = 10, maxreplication_slots = 10
- Create replication user:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password'
- Add replication entry to
pghba.conf: host replication replicator replicaip/32 scram-sha-256
- Reload configuration:
SELECT pgreloadconf()
- Initialize the replica with a base backup:
pgbasebackup -h primaryhost -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:
- Set
server-id = 1, logbin = mysql-bin, binlogformat = ROW
- Create replication user:
CREATE USER 'replicator'@'replicaip' IDENTIFIED BY 'securepassword'; GRANT REPLICATION SLAVE ON . TO 'replicator'@'replica_ip'
- Record binary log position:
SHOW MASTER STATUS
- Configure the MySQL replica:
CHANGE REPLICATION SOURCE TO S
