Database Replication
Copying data from one database server to another in real-time or near-real-time to provide redundancy, failover capability, and read scaling.
Description
Database replication continuously copies data changes from a primary (source) database to one or more replicas (targets). The primary use cases are high availability (if the primary fails, a replica can be promoted), disaster recovery (replicas in different regions survive regional outages), and read scaling (distribute read queries across replicas). Replication can be physical (copying WAL segments byte-for-byte) or logical (replicating row-level changes that can be selectively filtered).
Synchronous replication waits for at least one replica to confirm receipt of each transaction before the primary commits, guaranteeing zero data loss (RPO = 0) at the cost of increased write latency. Asynchronous replication lets the primary commit immediately and streams changes to replicas in the background, providing lower write latency but risking data loss if the primary crashes before changes reach a replica. Semi-synchronous replication (available in MySQL) offers a middle ground.
PostgreSQL supports both physical streaming replication (replicating the entire cluster) and logical replication (selectively replicating specific tables). Logical replication enables more complex topologies: multi-directional replication for active-active setups, replicating a subset of tables to an analytics database, or upgrading PostgreSQL versions with minimal downtime by replicating to a new-version replica and then switching over.
Prompt Snippet
Configure PostgreSQL physical streaming replication with at least one synchronous replica (synchronous_standby_names = 'replica1') for critical data and asynchronous replicas for read scaling. Set wal_level = replica, max_wal_senders = 10, and wal_keep_size = 2GB. Monitor replication status via pg_stat_replication and set up automated failover using Patroni with etcd for consensus. For cross-region disaster recovery, use asynchronous replication to a standby in a secondary region and test failover procedures quarterly with documented runbooks.
Tags
Related Terms
Read Replicas
Secondary database instances that asynchronously replicate data from the primary, offloading read queries to scale read throughput.
Database Backup Strategies
Systematic approaches to creating, storing, and verifying database backups to protect against data loss from hardware failure, human error, or security incidents.
Point-in-Time Recovery
Restoring a database to its exact state at any specific moment in time by replaying write-ahead log (WAL) segments on top of a base backup.