Replication, Backup & High Availability

Different replicas are at different positions, so a user can see time go backwards between two requests, see an effect without its cause, or get inconsistent halves of one page. Prevent it by pinning a session (or a page render) to one replica, and by ejecting replicas whose lag exceeds a threshold.

Use a log position when you can identify the exact transaction, a named marker when you planned ahead of a risky change, and a timestamp only to narrow a search. Timestamps rely on commit-time clocks, have coarse practical resolution, and cannot separate transactions committing in the same instant.

Promotion starts a new timeline: new log records get a new branch identifier so they do not collide with the abandoned records at the same positions. The old log stays valid for recovering to points on the old branch, replicas that followed the old branch are divergent and must be rebuilt or rewound, and you should take a fresh base backup immediately.

An encrypted backup is only as recoverable as its key. Keys stored on the lost host, rotated away, region-pinned, or reachable only through the failed environment make backups unrecoverable. Keep keys in a separate, replicated, escrowed key store, retain old key versions for the backup's lifetime, and prove it by restoring.

Tier it: dense recent backups plus retained log for a fine-grained recovery window (say 7-14 days), then weekly and monthly copies kept for months or years for compliance and slow-burn corruption. Expire the base backup and its dependent log together, and never expire a backup another one depends on.

Measure, do not assume. Run timed restore and failover drills to get real recovery times, and monitor replication byte lag, archive backlog, and backup age continuously as live proxies for the recovery point. Alert when either exceeds the promised number, and trend restore times against data growth.

A dump is consistent only if it reads inside one long transaction snapshot; otherwise tables are copied at different times. That snapshot blocks cleanup of old row versions, so the database bloats, and the dump also holds locks that block DDL and pollutes the buffer cache.

Check role, not just liveness: open a real connection as a real user and run a query that reports whether the node is writable. TCP-connect checks pass on a demoted or wedged node. Add timeouts, rise/fall thresholds against flapping, a reserved connection slot, and fail closed for writes.

Leadership is a short-lived key in a quorum-backed store (etcd) that the primary's agent must keep renewing. If it cannot renew before the TTL expires, it demotes its own database; only after the key expires can another agent create it and promote. One key, one writer.

The old primary may have committed transactions the new one never received, so its history diverged at the failover point and it cannot follow the new stream. A rewind finds the divergence point and undoes only the blocks changed after it, copying those from the new primary, which is far cheaper than a full rebuild.

Time-based lag is derived from the newest change record the replica applied. With no new records — an idle primary, or a dead receiver whose backlog has drained — there is nothing to compare against, so it reads zero or null. Trustworthy measurement combines byte distance from the primary, thread/connection state, and a heartbeat write.

The change event carries an identifying value set — the row's replica identity, by default its primary key — and the receiver looks the row up by it. With no primary key, either the operation is rejected outright, or the full old row must be logged and matched column-by-column, forcing a scan per event and collapsing throughput.

After committing, read the write's position in the change stream (an LSN or GTID) and carry it with the session or request. Before serving a read, compare it with the replica's applied position: if the replica is behind, wait briefly for it to catch up, or fall back to the primary.

With one named synchronous standby, that standby is a single point of failure for writes and the only node guaranteed to hold every acknowledged commit. Quorum commit waits for any k of n standbys, so any n minus k can be slow or dead without blocking writes, and commit latency follows the k-th fastest rather than one fixed node.

Point-in-time recovery rolls back the whole instance, so do not roll production back. Restore a copy to a scratch instance targeted just before the script, extract the 200 rows logically, and merge them into live data. This costs a full restore, so backup cadence, archive retention and spare restore capacity define what is feasible.

Derive the numbers from business impact, not engineering taste: what a minute of downtime and a minute of lost data cost, and whether the data is reconstructible elsewhere. Then define three or four standard tiers with fixed architectures and prices, and make owners choose a tier and fund it.

Physical backups are the base: a weekly full plus daily block-level incrementals, because a 4 TB logical dump will not fit a two-hour window. Add a periodic logical dump for portability and object-level recovery, keep copies off-site, and size retention around how far back you must be able to go.

Driver-based routing has no extra hop but is per-language and cannot drain. A sidecar limits blast radius but gives no global connection cap. A central tier gives one control point and true pooling but needs redundancy plus its own front-end routing. Most large systems combine a sidecar pooler with a redundant central router.

Two sites cannot give symmetric automatic failover: whichever side holds the majority survives, the other cannot promote. Either add a third independent site or region for the tiebreaking vote, or accept an asymmetric design where one site is primary-capable and the other requires a manual, fenced promotion.

Usually yes, because humans are slower than the outage budget, but only with quorum-gated promotion, confirmed fencing, a maximum-lag rule, hysteresis against flapping, and automated rejoin. Failback should be a separate, scheduled switchover, never automatic.

Derive thresholds from what each replica is for: a staleness budget in seconds for read traffic, a recovery-point budget for failover candidates, and a hard byte threshold from log retention. Alert on sustained growth, not single spikes. Above the staleness budget, drain the replica from the read pool automatically, with hysteresis before re-adding.

Decide from the cost of losing the last second of writes and whether they can be reconstructed upstream. A ledger cannot reconstruct them, so it pays a same-region synchronous quorum plus asynchronous cross-region copies. Clickstream data is replayable from the producer, so asynchronous replication and relaxed local commit are the right trade.

A delayed replica receives the change stream immediately but deliberately withholds applying it for a fixed interval, so it holds a live copy of the database as it was an hour ago. That gives you a fast rewind after a destructive human or application error, which normal standbys replicate instantly.

Logical replication ships decoded row changes, which a target on a newer major version can apply as ordinary writes — so you build the new-version database alongside, let it catch up, and cut over in seconds. Physical replication replays version-specific page-level records, so both nodes must run the identical major version.