Replication Fundamentals
Database replication copies data from one server to another for redundancy, read scaling, and disaster recovery.
Synchronous Replication
The primary waits for replicas to acknowledge writes:
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- PostgreSQL synchronous replication
synchronous_commit = on
synchronous_standby_names = '2 (standby1, standby2, standby3)'
SELECT application_name, state, sync_state, sync_priority
FROM pg_stat_replication;
Synchronous replication guarantees no data loss but increases latency.
Asynchronous Replication
The primary does not wait for replicas:
def check_replication_lag():
cur.execute("""
SELECT client_addr, application_name,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds
FROM pg_stat_replication;
""")
for row in cur.fetchall():
if row[3] > 60:
alert(f"Replication lag critical on {row[1]}")
Conflict Resolution
Multi-primary replication requires conflict resolution:
class ConflictResolver:
strategies = {
"last_write_wins": lambda a, b: a if a["timestamp"] > b["timestamp"] else b,
"majority_wins": lambda versions: Counter(versions).most_common(1)[0][0]
}
Replication Topologies
| Topology | Pros | Cons | |----------|------|------| | Primary-replica | Simple | Single point of failure | | Multi-primary | HA writes | Conflict resolution needed | | Cascading | Reduced primary load | Increased lag |
Conclusion
Choose synchronous for zero data loss, asynchronous for performance. Monitor replication lag closely. Test failover procedures regularly.