Replication, Backup & High Availability

RPO is how much recent data you can afford to lose, measured in time before the failure. RTO is how long the system may stay unavailable before it is serving again. RPO is about data loss; RTO is about downtime.

A full backup copies everything. An incremental copies only what changed since the previous backup of any type. A differential copies everything changed since the last full. Incrementals are smallest to take but slowest to restore, because the whole chain must be replayed.

Split-brain is two nodes both believing they are primary and accepting writes, usually after a network partition hid a still-running primary. Their histories diverge, and reconciling them means discarding writes the application already saw committed.

A switchover is a controlled role swap: writes are stopped, the replica catches up fully, then roles change with no data loss and the old primary can rejoin as a replica. A failover is reactive after a crash, so unreplicated commits can be lost.

Replication lag is how far a replica trails its primary. It is measured in bytes — how much of the primary's change log the replica has not consumed yet — and in seconds — how old the newest change the replica has applied is.

A read replica is a read-only copy that continuously applies the primary's change stream, so it trails the primary slightly. Send it reads that tolerate slightly old data (listings, reports, exports). Keep writes, and any read that must reflect a just-made write, on the primary.

Asynchronous: the primary confirms the commit as soon as it is durable locally and ships the change afterwards, so a sudden primary loss can lose recently committed transactions. Synchronous: the primary waits for a standby to acknowledge before confirming, so no acknowledged transaction is lost, at the cost of extra latency on every commit.

Point-in-time recovery restores a database to any chosen moment in the past. It needs a base backup of the data files plus an unbroken chain of archived transaction-log records covering everything from the start of that backup onward. You restore the backup, then replay the log and stop at the chosen point.

Your recovery point equals the newest data that survived the primary. Synchronous commit to another node gives near-zero loss; asynchronous gives loss equal to replication lag; log archiving gives loss up to the archive interval. Tighter costs write latency and couples your availability to the standby.

A logical backup exports data as SQL statements or rows that must be re-executed to restore. A physical backup copies the database's data files byte for byte. Logical is portable and selective but slow to restore; physical is fast but version- and platform-bound.

Replicas lag, so a read right after a write may not see it, and successive reads can go backwards in time. Fixes: keep reads inside a transaction on the primary, pin a session to the primary for a short window after it writes, or route only when the replica has caught up to the write's log position.

A floating IP moves in seconds but needs the nodes on one network segment and kills existing connections. DNS works anywhere but is hostage to TTL and client-side caching. A proxy decides per connection and can health-check and drain, but adds a hop and must itself be made highly available.

Only a majority of a fixed member set may elect a primary, because at most one majority can exist, so a minority partition can never promote. A witness is a cheap vote-only member that makes the member count odd without storing data.

Promotion ends recovery on that node: it finishes applying what it already received, opens for writes, and starts its own branch of the change stream (a new PostgreSQL timeline or a new binlog source). Every other replica must be repointed at it, and any replica ahead of it must be rewound or rebuilt.

Stages: the primary generates log records, sends them, the replica writes them, flushes them to disk, then replays them. pg_stat_replication exposes sent/write/flush/replay positions and the matching lag intervals per stage. MySQL's Seconds_Behind_Source covers only the apply stage — receive lag is separate.

Physical replication ships the low-level change log describing byte and page edits, so the replica is a block-for-block clone — same version, all objects, read-only. Logical replication decodes changes into row-level events (insert/update/delete with column values) that the target executes, so it can differ in version, schema and contents.

The primary acknowledged the commit before the replica applied it, so the follow-up read hit a copy that does not yet contain the change. Fixes: read that user's data from the primary for a short window after their write, or make the read wait until the replica has applied that write's position.

Each level survives a different failure. Received in memory survives losing only the primary; flushed to the standby's disk also survives the standby restarting; replayed additionally makes the change visible to readers on the standby. Later levels add latency, so pick the earliest level that covers the failure you care about.

Stop further writes and preserve the current data and log archive. Identify the exact target just before the statement. Restore the latest base backup taken before 14:32 onto a separate host, replay archived log up to that target, pause and verify, then either cut over to the restored copy or extract the damaged rows back into production.

Detect, decide, fence the old primary, promote or restore, replay logs, repoint clients, warm caches, verify. Detection and human decision usually dominate, then restore and replay if there is no standby. Automation removes detection and decision time; a warm standby removes restore and replay.

In-flight transactions cannot be moved; they are rolled back. The proxy stops issuing the old primary, lets open transactions finish for a bounded time, then closes them, optionally queueing new connections. Clients must discard pooled connections and retry — with backoff, jitter, and only where the operation is safe to repeat.

Fencing means guaranteeing the old primary can no longer commit writes or be reached by clients before the new primary opens. It is done by killing the node (STONITH via power or hypervisor), revoking its storage or network access, or removing it from the routing layer.

You can lose everything the primary committed but had not yet shipped to the promoted replica. Measure it as replication lag in bytes and seconds at the p99, per replica, continuously. Levers: reduce lag, promote the furthest replica, refuse promotion past a lag threshold, or make commits synchronous.

Usual causes: apply is far less parallel than the primary's concurrent writers; long or huge transactions arriving as one burst; row changes on the replica lacking a usable index so each change scans; replica hardware or I/O weaker; and replay blocked by conflicting long-running queries on the replica. Narrow it down by finding which stage and which transaction stalls.

Logical replication does not carry schema changes, sequence values, or (by default) everything the cluster contains; apply is far slower per change; the writable target can hit conflicts that stall the whole subscription; the initial snapshot is expensive; and an unconsumed change stream retains log on the source until its disk fills.

Synchronous commit makes the primary wait for a standby's acknowledgement before returning; with the only such standby gone, every commit waits forever. Fixes: use a quorum of any k of n with n greater than k, configure an automatic timeout that degrades to asynchronous, and monitor and alert on degraded mode.

Keep three copies of the data, on two different media or storage systems, with at least one off-site. For a database that means the live cluster, a local or same-region backup repository, and a copy in another region or account - ideally write-once so it cannot be deleted or encrypted by an attacker.

Four layers: metadata checks (file present, size, expected members), checksum and manifest verification of the stored bytes, a restore that starts the engine and replays logs, and application-level assertions on the restored data. Cheap layers catch corruption; only a restore proves recoverability.

Logical replication. Physical copies the entire cluster byte-for-byte and keeps the target read-only, so neither selective tables nor local tables and extra indexes are possible. Logical costs you: manual DDL coordination, row identity on the three tables, conflict handling, and slot/log retention on the source.