Database Server Replication Guide

Introduction to database replication

Database replication copies and keeps data in sync between two or more database servers. It helps with high availability, read scaling, and disaster recovery. Good replication reduces downtime and makes systems more resilient to failures.

This article explains core ideas, common architectures, how to pick a strategy, and practical steps for setup, security, monitoring, tuning, and recovery.

Core concepts and terminology

Replication node: any server that holds a copy of the data.

Primary (or master): the node that accepts writes.

Replica (or secondary): a node that receives and applies changes from the primary.

Synchronous vs asynchronous: synchronous replication waits for replicas to confirm writes before returning success; asynchronous returns success before replicas confirm.

Replication lag: the delay between a write on the primary and that write appearing on a replica.

WAL / binlog / oplog: write-ahead log types used by different databases to record changes for replication.

Snapshot / initial sync: the initial copy of data sent to a new replica to match the primary.

Conflict: when two nodes try to change the same data in incompatible ways. Conflicts matter most in multi-master setups.

Durability: the guarantee that once a write is acknowledged, it will survive crashes. Durability depends on replication configuration.

Consistency: how closely the replica state matches the primary. Strong consistency means reads see the latest writes; eventual consistency means replicas may lag temporarily.

Replication topologies and architectures

Single primary, multiple replicas

• One server accepts writes; many replicas handle reads.
• Simple to operate and common for read scaling.

Primary-primary (multi-master)

• Multiple nodes accept writes and replicate to each other.
• Offers write locality but requires conflict resolution.

Cascading (hierarchical)

• Replicas replicate from an intermediate replica rather than the primary.
• Useful to reduce load on the primary and for geographical distribution.

Sharded replication

• Data is split across multiple primaries by key range or hash.
• Each shard can have its own replicas. Good for write scaling.

Synchronous vs asynchronous

• Synchronous: stronger durability, higher write latency.
• Asynchronous: lower write latency, potential data loss on primary failure.

Geo-replication

• Replicas in different regions for disaster recovery and lower latency to local users.
• Often uses asynchronous replication with careful failover planning.

Choose a topology based on read/write patterns, failover needs, and geographic constraints.

Selecting the right replication strategy

Start with your goals:

• High availability: prefer at least one hot standby and an automated failover tool.
• Read scaling: add read replicas and route read queries to them.
• Low latency writes: avoid synchronous replication across large distances.
• Disaster recovery: maintain off-site replicas and tested failover plans.

Match data patterns:

• Mostly reads, rarely writes: simple primary + many replicas works well.
• Writes from multiple locations: consider multi-master or local primaries per shard, but plan conflict resolution.
• Large dataset and heavy writes: consider sharding plus replication per shard.

Consider operational complexity:

• Multi-master and sharding increase complexity and risk.
• Simple primary/replica setups are easier to manage and debug.

Plan for failure modes:

• Decide acceptable data loss for primary failure.
• Choose synchronous replicas when you cannot accept data loss.
• Use asynchronous replicas for scalability and lower latency.

Installation and initial configuration

Prepare the network and OS:

• Use reliable, low-latency links between nodes.
• Configure firewalls and open only needed ports.

Set up the database software:

• Install the same major version across nodes when possible.
• Apply OS and database tuning for consistent behavior.

Create a replication user with least privilege:

• Grant only the permissions needed for reading the log and applying changes.

Take an initial snapshot:

• Lock or use a consistent backup method to capture a point-in-time snapshot.
• For PostgreSQL: use pg_basebackup or take a filesystem snapshot while archiving WAL.
• For MySQL: use mysqldump with –single-transaction or LVM snapshot; capture binlog position.
• For MongoDB: use mongodump or filesystem snapshot while recording the oplog position.

Configure replication settings:

• Point replicas to the primary’s replication endpoint and authentication.
• Configure WAL/binlog retention so replicas can catch up after outages.
• Tune commit behavior (sync_commit, sync_binlog, etc.) according to durability needs.

Start initial sync:

• Restore the snapshot on the replica.
• Start replication and verify it connects and begins applying events.

Verify replication health:

• Check replication status commands (e.g., pg_stat_replication, SHOW SLAVE STATUS).
• Confirm the replica reaches the primary’s current position.

Data consistency, durability, and conflict resolution

Consistency models:

• Strong consistency: reads reflect all acknowledged writes. Usually requires synchronous replication or read-from-primary.
• Eventual consistency: replicas may lag but will converge. Common with asynchronous replication.

Durability trade-offs:

• Faster commits may disable waiting for replicas; this increases risk of data loss.
• Strong durability requires waiting for at least one replica to confirm writes.

Conflict resolution approaches:

• Last-write-wins: simple but can lose updates.
• Application-level resolution: app tracks versions and resolves conflicts.
• CRDTs (Conflict-free Replicated Data Types): enable automatic merge for specific data types.
• Custom merge logic in the database or middleware.

Schema changes:

• Apply schema migrations carefully. Use rolling migrations when possible.
• Backfill data on replicas where supported.
• Avoid schema changes that break replication event formats.

Test consistency assumptions:

• Inject writes, simulate failover, and verify no data loss beyond acceptable bounds.
• Regularly run checksums between primary and replicas to detect silent divergence.

Security, authentication, and access control

Secure communication:

• Use TLS/SSL for replication traffic to prevent eavesdropping.
• Restrict replication traffic to private networks or VPNs.

Authentication and least privilege:

• Create a dedicated replication user with the minimum required permissions.
• Rotate replication credentials regularly.

Network controls:

• Use firewall rules or security groups to allow replication only between known hosts.
• Disable public access to database ports.

Audit and logging:

• Enable replication-related auditing where available.
• Monitor for unusual connection attempts or credential failures.

Secrets management:

• Store replication credentials in a secrets manager, not in plain config files.
• Automate credential updates and deployments where possible.

Isolation and multi-tenant security:

• When serving multiple customers, ensure replicas do not unintentionally expose data.
• Use row-level security or separate instances when needed.

Monitoring, alerting, and maintenance

Key metrics to monitor:

• Replication lag (time and bytes).
• Replica health status and last applied position.
• Disk usage and WAL/binlog retention.
• Connection counts and CPU/IO utilization.
• Error counts and failed transactions.

Useful checks:

• Verify that replicas are streaming and not stuck.
• Check for large unapplied transaction batches.
• Watch for excessive slot usage or WAL growth.

Alerting guidelines:

• Alert on sustained replication lag that exceeds your SLA.
• Alert on replica disconnections or authentication failures.
• Alert on low disk space and excessive WAL growth.

Tools and integrations:

• Use built-in views (pg_stat_replication, SHOW SLAVE STATUS) with exporters.
• Collect metrics with Prometheus and visualize in Grafana.
• Use managed monitoring or vendor tools for easier setup.

Maintenance tasks:

• Rotate replicas periodically to test restores and failover.
• Rebuild replicas when replication falls too far behind.
• Vacuum, compact, or truncate logs as per database recommendations.

Backup, failover, and disaster recovery

Backup strategy:

• Combine logical and physical backups where useful.
• Use point-in-time recovery (PITR) if supported and needed.
• Store backups off-site and verify they are restorable.

Backup from replicas:

• Prefer taking backups from replicas to reduce primary load.
• Ensure backups are consistent with replication position.

Planned failover:

• Use a tested orchestration tool (repmgr, Patroni, Orchestrator) to promote a replica.
• Ensure clients can re-route traffic or use a virtual IP/DNS switch.

Unplanned failover and disaster recovery:

• Define RTO (recovery time objective) and RPO (recovery point objective).
• Use asynchronous replicas for geographic resilience but plan for data loss higher than with synchronous replication.
• Regularly rehearse DR scenarios and document steps.

Recovery testing:

• Automate game-day drills and restore tests.
• Check application behavior after failover: session handling, caches, and write routing.

Avoid split-brain:

• Use quorum or fencing to prevent two primaries from accepting writes.
• Use external consensus systems (etcd, ZooKeeper) when automatic leader election is needed.

Performance tuning and scaling

Reduce replication latency:

• Use faster networking and lower network jitter.
• Place replicas closer to the primary for synchronous setups.
• Batch writes or increase apply parallelism when supported.

Tune database settings:

• Adjust checkpoint and WAL thresholds to balance IO and latency.
• Increase apply worker threads if the replica can parallelize apply.

Scaling reads:

• Add read replicas and route read-only traffic to them.
• Use connection pools or read-routing proxies to distribute queries.

Scaling writes:

• Scale writes by sharding or partitioning the dataset.
• Consider queueing heavy write bursts or batching writes.

Avoid replication bottlenecks:

• Ensure primary has enough IO capacity to handle both normal operations and the replication workload.
• Monitor CPU, memory, and IO on replicas; they must be sized for applying changes and serving reads.

Cache and application-level optimizations:

• Use caches to reduce read pressure on replicas.
• Reduce chatty queries and N+1 query patterns.

Measure and iterate:

• Benchmark under realistic load before and after changes.
• Monitor the system continuously and adjust settings slowly.

Troubleshooting common issues

Replication lag

• Cause: slow disk, network, large transactions.
• Steps: check IO and network; identify long-running transactions; increase apply workers or tune checkpointing.

Replica not connecting

• Cause: auth failure, firewall, wrong config.
• Steps: examine logs for authentication or SSL errors; test network connectivity; verify replication credentials.

Diverged data (inconsistent replicas)

• Cause: manual writes to a replica, missing events, or bugs.
• Steps: compare checksums, restore from a consistent backup and resync, enforce read-only mode on replicas.

WAL/binlog retention issues

• Cause: replicas offline cause WAL growth.
• Steps: bring replicas up or create a new replica; increase storage for WAL; configure retention wisely.

Schema migration breakage

• Cause: incompatible DDL applied in wrong order.
• Steps: roll back if possible; apply migrations in rolling-safe ways; use feature flags and backfills.

Split-brain after network partition

• Cause: multiple nodes promoted independently.
• Steps: stop writes, pick the correct primary based on most recent data and quorum, re-sync others, prevent recurrence with fencing and quorum.

Always read database logs first. Logs often show the replication error and the exact point of failure.

Operational best practices and checklist

Design and planning

• Define RTO and RPO before choosing replication modes.
• Choose topology that fits read/write patterns and geographic needs.

Security

• Encrypt replication traffic.
• Use least-privilege replication users.
• Store secrets in a manager and rotate them.

Deployment and configuration

• Keep software versions compatible across nodes.
• Automate setup and configuration with scripts or IaC.

Testing

• Test failover, restores, and schema migrations regularly.
• Run consistency checks and checksum comparisons.

Monitoring and alerting

• Monitor replication lag, WAL retention, and disk usage.
• Alert on sustained lag, replica down, or failed authentication.

Backups and DR

• Keep regular backups and test restores.
• Maintain at least one off-site replica.

Maintenance

• Rebuild replicas when lag is excessive.
• Patch and upgrade with rolling strategies.

Documentation and runbooks

• Maintain clear runbooks for failover and recovery.
• Document topology, roles, and contact points.

Checklist (quick)

• Define RTO/RPO
• Set up a dedicated replication user
• Take an initial consistent snapshot
• Configure WAL/binlog retention
• Enable TLS for replication
• Add monitoring and alerts for lag and replication status
• Create automated failover/runbook
• Test backups and restore procedures
• Run periodic consistency checks
• Rehearse disaster recovery scenarios

Conclusion

Replication is essential for availability, scaling, and recovery. Pick the simplest topology that meets your goals. Secure replication, monitor it continuously, and practice failover and restore regularly. Small, repeatable steps—backups, tests, and clear runbooks—prevent the majority of replication problems.