Database replication ensures your data survives failures and scales to handle read-heavy workloads. Here's how to implement replication effectively.
Replication Patterns#
Primary-Replica (Master-Slave)#
┌──────────────┐
│ Primary │ ← All writes
│ (Master) │
└──────┬───────┘
│ Replication
├──────────────┬──────────────┐
▼ ▼ ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Replica 1 │ │ Replica 2 │ │ Replica 3 │
│ (Read) │ │ (Read) │ │ (Read) │
└──────────────┘ └──────────────┘ └──────────────┘
Benefits:
- Read scaling
- Backup without impacting primary
- Geographic distribution
Drawbacks:
- Write bottleneck
- Replication lag
- Failover complexity
Multi-Primary (Master-Master)#
┌──────────────┐ ┌──────────────┐
│ Primary 1 │◄───►│ Primary 2 │
│ (R/W) │ │ (R/W) │
└──────────────┘ └──────────────┘
Benefits:
- Write scaling
- No single point of failure
Drawbacks:
- Conflict resolution needed
- More complex
- Higher latency for consistency
PostgreSQL Replication#
Streaming Replication Setup#
Loading code block...
Loading code block...
Application Configuration#
Loading code block...
Synchronous vs Asynchronous#
Asynchronous Replication#
Primary commits → Returns to client → Replicates later
Pros:
- Lower latency
- Primary doesn't wait
Cons:
- Data loss possible
- Replication lag
Synchronous Replication#
Loading code block...
Primary commits → Waits for replica → Returns to client
Pros:
- Zero data loss
- Strong consistency
Cons:
- Higher latency
- Primary blocked if replica fails
Failover Strategies#
Automatic Failover with Patroni#
Loading code block...
Connection Pooling with PgBouncer#
Loading code block...
Multi-Region Replication#
US-East (Primary)
│
├──► US-West (Async Replica)
│
└──► EU-West (Async Replica)
Considerations:
- Network latency (50-200ms cross-region)
- Conflict resolution strategy
- Data sovereignty requirements
- Failover and failback procedures
AWS RDS Multi-AZ#
Loading code block...
Monitoring Replication#
Loading code block...
Loading code block...
Conclusion#
Database replication is essential for high availability and read scaling. Start with primary-replica for most cases, add synchronous replication for zero data loss requirements, and implement automatic failover for production reliability.
Monitor replication lag continuously and test failover procedures regularly. The best replication setup is one you've practiced recovering from.