Backup and Disaster Recovery Questions

Direct answer

The rationale has to make three things explicit and quantified: what the current periodic-backup design actually costs in downtime and data-loss risk today, what synchronous replication would cost in ongoing infrastructure spend and write latency, and how those two numbers compare against what an hour of downtime actually costs the business. Below is a template for that comparison with illustrative figures (labeled as such throughout, meant to be replaced with real numbers before this goes to a decision-maker), plus the implementation steps, risks and mitigations, and the KPIs that close the loop after deployment.

Structured elaboration

Quantifying cost vs availability benefit. Current state: periodic backups (e.g. nightly full plus hourly incremental) give an RPO (Recovery Point Objective, maximum acceptable data loss) of roughly 1 hour and an RTO (Recovery Time Objective, maximum acceptable downtime) of several hours, since restore means loading a backup and replaying logs since it. Proposed state: synchronous replication drops RPO toward zero, bounded by the commit protocol rather than the backup interval, and drops RTO to minutes, since failover means promoting an already-caught-up standby instead of restoring from backup. Cost: a synchronous standby is a continuously running instance, a real ongoing cost, not the episodic storage cost of periodic backups, plus a write-latency tax on every commit from waiting on the standby's acknowledgment, plus cross-region network charges. As an illustrative example only: if the current database instance costs roughly $400/month, a same-sized synchronous standby running continuously roughly doubles compute spend to about $800/month, plus incremental replication network cost, figures to be replaced with the real instance sizing before this goes into a proposal. On the benefit side, translate the RTO improvement into an annualized downtime-reduction estimate: if the current design implies, say, 2 unplanned outages per year (illustrative) at several hours of downtime each, and the new design cuts that to minutes each, multiply the hours saved per year by whatever the business's actual cost-per-hour-of-downtime figure is, and compare that annualized benefit against the added monthly infrastructure cost times 12. That comparison, cost of downtime avoided versus cost of the standby, is the actual business case, not just "replication is more resilient."

Implementation steps. 1. Choose the replication topology (single standby vs. a quorum, a majority, of standbys) and target region or AZ. 2. Stand up the standby infrastructure and enable synchronous commit mode on the database (e.g. Postgres synchronous_commit=remote_apply with synchronous_standby_names configured). 3. Load-test before committing fully, since the write-latency tax is workload-dependent and needs to be measured under realistic load, not assumed from documentation. 4. Build or update the failover automation and runbook (promote standby, repoint endpoint). 5. Roll out gradually, keeping the existing periodic backups running in parallel rather than retiring them, since synchronous replication protects against infrastructure failure but not against logical or human error, like a bad write, which periodic backups (and point-in-time recovery) remain the safety net for. 6. Run a DR drill validating the new promote-based failover actually meets the target RTO before declaring the migration complete.

Risks and mitigations. Write-latency risk: every commit now waits on the standby's acknowledgment, degrading throughput and tail latency; mitigate by choosing a nearby region or AZ pair to minimize round-trip time, and by load-testing before rollout to catch the impact before it surprises production. Availability risk: if the standby becomes unreachable and the database is configured to block writes until synchronous acknowledgment succeeds, synchronous replication can trade durability for availability; mitigate by explicitly defining and testing the degraded-mode behavior (e.g. a deliberate, alerted, temporary fallback to asynchronous mode if the standby is down) rather than leaving that failure mode undefined. Cost-overrun risk: real infrastructure cost could exceed the estimate used to justify the proposal; mitigate by re-measuring actual cost after a short pilot period and reporting it back against the original business case. Cutover risk: a botched enablement could stall or corrupt the primary; mitigate with a staged rollout during a low-traffic window and a documented rollback plan (disable synchronous mode, fall back to async and periodic backups) if problems surface.

KPIs to evaluate success post-deploy. Measured RPO (actual observed data-loss window during any real or drilled failover) against target. Measured RTO (actual wall-clock failover time in drills) against target. p99 (99th-percentile: the latency below which 99% of writes fall, a standard way to track the worst typical case rather than the average) write latency before versus after enabling synchronous replication, to confirm the throughput cost is within tolerance. Actual infrastructure cost delta, measured, not estimated, against the projected cost used in the original rationale, closing the loop on the business case. Count of standby-unreachable degraded-mode incidents, tracking how often the availability risk above actually materializes in practice.

Direct answer

At 5 TB with an RTO (Recovery Time Objective) of 2 hours and an RPO (Recovery Point Objective) of 15 minutes, the arithmetic itself rules out a naive cold restore as the primary recovery mechanism: restoring 5 TB from cold object storage at an illustrative sustained throughput of 500 MB/s takes 5,000,000 MB / 500 MB/s ≈ 10,000 s ≈ 2.8 hours, already over the 2-hour budget before any transaction-log replay even starts. The design therefore has to lean on an already-replicating warm standby for the failover path (promotion, not bulk data movement), with continuous log shipping meeting the 15-minute RPO and periodic base backups existing for a different purpose: bounding worst-case recovery time and retention, not for being the thing you restore from during a real incident.

Structured elaboration

Backup cadence. A daily full or base backup taken during a low-traffic window, as the retention and point-in-time-recovery anchor, plus continuous transaction-log shipping running independently of that schedule.

Storage format. Use the database engine's native physical backup format for the base backup (e.g. a physical, binary-level backup rather than a logical SQL dump), since physical backups both create and restore dramatically faster at multi-terabyte scale, a logical dump serializes to individual statements or rows and is far slower on both ends. Store the continuously shipped logs in their native, compressed log format in object storage for point-in-time recovery (PITR). Reserve logical/SQL-dump exports for small, targeted extracts, not as the primary DR mechanism for the full 5 TB database.

Incremental vs. continuous approaches, and why both are needed for different reasons. "Incremental" here means periodic delta captures on a fixed schedule (e.g. every few hours), which bounds how much has to be combined during a restore, that's an RTO lever. "Continuous" means the transaction-log stream, shipped as it's generated rather than on a schedule, that's the RPO lever. These solve different problems and one doesn't substitute for the other: hourly incrementals alone would only give an RPO of roughly 1 hour, four times looser than the 15-minute target, while continuous log shipping with lag kept well under 15 minutes (e.g. shipping every few seconds, alerting if observed lag exceeds roughly 5 minutes, a third of the budget) is what actually delivers the RPO commitment. Conflating the two, assuming a periodic incremental schedule alone satisfies a tight RPO, is exactly the kind of mismatch to avoid.

Meeting RTO = 2 hours. As shown above, a cold restore of the full 5 TB dataset can already consume the entire 2-hour budget on data movement alone, before log replay. The primary failover mechanism should instead be promoting a standby built from the same continuous log stream and already caught up, which requires no bulk data transfer at failover time, just a promotion and endpoint cutover, the same pattern a warm-standby design uses generally. Restore-from-backup-and-replay remains as the fallback for the case where the standby itself is also lost, and its slower, more fragile worst-case time is accepted explicitly as a fallback, not relied on as the primary RTO-meeting path.

Meeting RPO = 15 minutes. Continuous log shipping with observed lag kept comfortably under the 15-minute target, monitored and alerted well inside that budget (e.g. at 5 minutes) so there's real time to react to a shipping delay before the RPO commitment is actually at risk.

Testing recoverability. Scheduled, automated restore drills, e.g. monthly, that actually promote or restore into an isolated environment and time the result end to end against both the 2-hour and 15-minute targets, not just confirm the backup file exists. Verify data integrity via checksums or row-count comparisons against the live primary at drill time. Re-run the drill periodically as the dataset grows past 5 TB, since both the RTO math (data-movement time in a cold-restore fallback) and the RPO math (log volume and replay time) scale with size, so a drill that passed today doesn't guarantee it still passes after meaningful growth.