Database Replication Patterns

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.