Backup and Disaster Recovery Questions

Direct answer

Write-ahead log (WAL) based point-in-time recovery (PITR) works by treating a full base backup as the anchor and the continuously-archived WAL, the record of every change written before it is applied to the actual data files, as the fine-grained delta on top of that anchor: to recover to any target moment T, restore the base backup taken at or before T, then replay WAL segments forward from that point and stop at the exact transaction boundary at or just before T. This gives recovery granularity down to roughly the archiving interval or better, typically seconds to low minutes, instead of the coarse granularity of "whichever full backup happened to run last," which is the entire value of PITR over full-backup-only recovery.

Role of the base (full) backup

A base backup is a complete, consistent snapshot of the database at a known log sequence number (LSN) or timestamp. It is the starting point every replay begins from; WAL records are deltas relative to an existing state, not a self-contained copy of the data, so WAL alone is meaningless without a base backup to apply it on top of. The base backup's age directly determines how much WAL has to be replayed during a restore: a base backup taken once a week means a worst-case replay of up to a week's worth of WAL, while a nightly base backup bounds replay to at most a day, a real trade-off between base-backup storage and cost versus restore-time speed.

Role of ongoing log shipping and archiving

WAL is generated continuously as the database processes transactions. Archiving it, shipping each completed segment to durable storage, to a standby, or to an archive location, as it is generated rather than on a fixed daily schedule, is what closes the gap between "the last base backup" and "right now." This continuous archiving is what actually determines the achievable recovery point objective (RPO): if WAL segments are shipped every few seconds to a couple of minutes under normal load, RPO is on that order, essentially independent of how old the last base backup is. It is important not to conflate the two cadences: base backup frequency controls restore time (how much WAL must be replayed), while WAL archiving frequency controls RPO (how much data could be lost); understating either by quoting the other's number is a real basis error.

Replay process to a target time

To recover to a target timestamp T: (1) restore the most recent base backup taken at or before T, (2) sequentially apply every WAL segment generated after that base backup's starting LSN, in order, up to but not past T, and (3) stop at the precise transaction boundary at or just before T rather than replaying an entire segment past the target. Most systems that implement this (PostgreSQL's recovery_target_time, for example) handle step 3 automatically, halting replay at the correct WAL record. The recovery granularity this achieves is effectively per-transaction, not per-backup: any committed transaction before T is present, and nothing after T is, which is the specific guarantee "point-in-time recovery" refers to.

Direct answer

Restoring PostgreSQL 12 to a specific timestamp means restoring a base backup into a fresh data directory, configuring it to replay archived write-ahead log (WAL) files up to that exact moment, and letting PostgreSQL's recovery process stop and promote once it reaches the target, rather than replaying every WAL file all the way to the present.

Structured elaboration

Obtaining the base backup. Point-in-time recovery starts from a base backup, a full physical copy of the database's data directory taken while the database was running, typically produced with pg_basebackup. If backups run on a schedule, use the most recent base backup taken before the target timestamp of 2026-02-15T13:47:20Z; restoring the wrong base backup, one taken after the target time, would make reaching an earlier target impossible. Extract or copy that base backup into a fresh, empty data directory that will become the restored instance.

Preparing configuration (recovery settings). PostgreSQL 12 changed how recovery is configured compared to earlier versions: the old recovery.conf file no longer exists. Recovery-related settings are now regular configuration parameters set in postgresql.conf (or an included configuration file), and recovery mode is triggered by the presence of a specific signal file in the data directory. For this scenario (recovering to a point in time using archived WAL, not starting a streaming replica), the two settings that matter most are restore_command, which tells PostgreSQL how to fetch a given WAL file from the S3-based archive, for example a command that copies the requested file from the archive bucket to the location PostgreSQL asks for, and recovery_target_time, set to the target timestamp 2026-02-15T13:47:20Z. It's also worth explicitly setting recovery_target_action to control what happens once the target is reached: promote will automatically bring the database up read-write at that point, while pause will stop recovery right at the target and let you verify the data before manually promoting, which is the safer choice when you want to confirm you landed on the right point before opening the database to writes. Finally, create an empty file named recovery.signal in the data directory; its presence (and the absence of standby.signal, which is for streaming replicas rather than archive recovery) is what tells PostgreSQL 12 to start in archive-recovery mode using the settings above.

Replaying WAL files from S3 until the target time. Start the PostgreSQL instance against the restored data directory. On startup, seeing recovery.signal, it enters recovery mode and begins requesting WAL files one at a time via the configured restore_command, fetching each from the S3 bucket where WAL archiving has been storing them, and replaying the changes they contain against the base backup. It continues fetching and replaying sequential WAL files until it reaches a transaction commit at or before the specified recovery_target_time; by default PostgreSQL stops at the first commit record that would put it past the target, so the restored database reflects everything committed up to and including the target moment and nothing committed after it. If recovery_target_action was set to pause, the database pauses at that point in recovery, still not accepting normal connections for writes, and you can connect to verify the data looks correct (for example checking pg_last_xact_replay_timestamp() to confirm the last replayed transaction's timestamp matches expectations) before manually promoting it to finish recovery and open for read-write traffic; with promote configured instead, this happens automatically and recovery.signal is removed once recovery completes.

Worked example

A team needs to recover a production PostgreSQL 12 database to 2026-02-15T13:47:20Z after a bad migration ran at 13:52 that day. They locate the most recent base backup taken before the incident, from earlier that same day, and extract it into a new data directory on a separate recovery host. In postgresql.conf for that instance, they set restore_command = 'aws s3 cp s3://example-wal-archive/%f %p' (fetching the WAL file PostgreSQL names via %f from the archive bucket to the path PostgreSQL expects via %p), recovery_target_time = '2026-02-15 13:47:20 UTC', and recovery_target_action = 'pause' so they can verify before opening the database for writes. They create an empty recovery.signal file in the data directory and start the instance. PostgreSQL fetches and replays WAL files in sequence from the archive, and recovery pauses once it reaches the target moment. They connect and confirm pg_last_xact_replay_timestamp() shows a time at or just before 13:47:20, and that the specific rows affected by the 13:52 migration are absent, confirming they landed before the bad change. Satisfied, they run SELECT pg_wal_replay_resume(); to complete the promotion and bring the instance up for normal use.

Trade-offs and pitfalls

• Restoring from a base backup taken after the target timestamp makes the restore impossible from that backup; always confirm the chosen base backup predates the target before starting.
• Using recovery_target_action = 'promote' is convenient for a fully automated restore but skips the chance to verify you landed on the correct point before the database opens for writes; for a manual, high-stakes recovery, pause followed by explicit verification is the safer default.
• If a WAL file the restore_command needs is missing from the S3 archive, for example because an earlier archiving gap or a retention policy pruned it, recovery halts with an error at that point rather than silently skipping ahead; that failure mode is a distinct scenario worth planning for separately from a clean recovery like this one.

Direct answer

Snapshotting and replication protect against different failure classes and are not substitutes for each other: a snapshot is a point-in-time copy that's good for rolling back a mistake on the same storage system, and replication is a continuously-updated copy that's good for surviving a whole site or region going down, and neither one alone is a substitute for an independent, retained backup.

Structured elaboration

Snapshotting: how it works. A snapshot captures the state of a volume, filesystem, or database at a specific instant, typically using copy-on-write or redirect-on-write: the storage system doesn't copy all the data up front, it starts tracking which blocks change after the snapshot was taken and preserves the pre-change version of any block that gets overwritten, so a snapshot is cheap to create and initially consumes little extra space.

Snapshotting: consistency guarantees. By default, a snapshot is crash-consistent: the captured state looks like what you'd see after an unexpected power loss, internally coherent at the storage layer but with no guarantee that an application's in-memory or in-flight work was flushed to disk first. Getting an application-consistent snapshot (one that also captures a coherent application-level state, such as a database with no half-written transaction) requires the application's cooperation: briefly pausing writes, flushing buffers, or using a storage-integrated hook the application supports.

Snapshotting: use cases. Fast, frequent recovery points for operational mistakes (a bad deployment, an accidental delete, a botched migration) where you want to roll back to "ten minutes ago" cheaply and quickly, all on the same storage system.

Snapshotting: limitations for retention and recovery. A snapshot usually depends on the same underlying storage volume it was taken from; if that storage system or site is destroyed, the snapshot is destroyed with it, so a snapshot alone offers no protection against a storage-system or site-level failure. Snapshot chains can also grow unwieldy over long retention periods: many storage systems either cap how many snapshots they'll retain gracefully or see rising overhead as the chain lengthens, so snapshots are usually not the right primary mechanism for months- or years-long retention.

Replication: how it works. Every write (or a stream of change records) is continuously copied from a primary to one or more replicas, either synchronously (the write isn't acknowledged to the client until the replica confirms it too) or asynchronously (the write is acknowledged immediately and the replica catches up shortly after).

Replication: consistency guarantees. Synchronous replication guarantees zero data loss for any write the primary acknowledged, at the cost of added write latency (every write waits on a round trip to the replica). Asynchronous replication has lower write latency but carries a small, real window of potential data loss equal to whatever the replication lag is at the moment of a primary failure.

Replication: use cases. High availability failover and near-real-time disaster recovery to a different site or region, where the goal is a very short recovery time and a very small recovery point gap if the primary goes down.

Replication: limitations for retention and recovery. Replication faithfully propagates everything, including mistakes: an accidental delete or logical corruption on the primary replicates to the replica within seconds, so replication provides no protection at all against a logical or application-level error, only against physical infrastructure loss. It also isn't a retention mechanism on its own; a replica reflects the current state, not a history of past states, so "restore to how things looked 90 days ago" isn't something replication by itself can do.

Worked example

An e-commerce platform's order database uses synchronous replication to a standby in a second availability zone (protecting against a rack or zone failure with effectively zero data loss for acknowledged writes) plus hourly application-consistent snapshots retained for 7 days (protecting against an operator accidentally truncating a table). One afternoon, a deploy script runs an unintended bulk update that corrupts several thousand order rows. Replication faithfully copies the corrupted rows to the standby within moments, so failing over to the replica does not help: the standby has the same corrupted data. The snapshot from an hour before the deploy, however, reflects the state before the mistake, and the team restores the affected tables from that snapshot. The two mechanisms solved two different problems: replication would have saved them from a zone outage; only the snapshot saved them from the logical error.

Trade-offs and pitfalls

• Treating replication as "our disaster recovery plan" without a separate retained backup or snapshot leaves an organization with no defense against logical corruption or accidental deletion, which is a more common cause of real data-loss incidents than a full site failure.
• Crash-consistent snapshots are cheaper and simpler but can leave a database in a state that requires its own crash-recovery process to become usable (replaying its transaction log from the crash-consistent point); application-consistent snapshots avoid that but cost more (briefly pausing or quiescing the application) to take.
• Synchronous replication's zero-data-loss guarantee comes with a real latency cost on every write, which is why it's typically used only for nearby sites (where the round-trip cost is small); at longer distances, most systems fall back to asynchronous replication and accept a small, monitored replication-lag window instead.

Direct answer

Verifying 100 TB of mixed databases and file stores needs layered checks at different costs and frequencies: cheap, automated checks run on every backup every day, genuine full restores run on a rotating sample because exhaustively restoring everything daily isn't feasible at that scale, alerts are severity-aware rather than uniform, and the workflow explicitly separates a real backup failure from a false positive in the testing process itself before treating either as an incident.

Structured elaboration

Daily lightweight checks. Run cheap, fast checks against every backup every day: confirm the backup job actually completed successfully, sanity-check the size (a backup that's dramatically smaller than the same system's recent history often indicates a partial or silently-truncated backup that still reported success), and verify a checksum of the stored backup file itself against what was written. This catches storage-layer corruption and obviously broken backups without the cost of an actual restore, and it's cheap enough to run on the full 100 TB estate every single day.

Periodic full-restore tests. On a rotating schedule rather than daily for everything, since fully restoring 100 TB of mixed systems every day is not feasible within any reasonable time or cost budget, actually perform a real, end-to-end restore into an isolated environment and validate at the application level, not just confirm files landed. For a database that means running integrity checks, row counts, or a representative smoke query; for a file store it means confirming a sample of files are actually readable and intact. This is the only check in the whole process that catches "the backup reports success and passes its checksum but is not actually restorable," a materially different and more dangerous failure mode than simple corruption, since it hides behind every cheaper check passing.

Automated sampling. Given the infeasibility of testing everything at full-restore depth constantly, use a statistically-informed sampling strategy: weight selection toward higher-risk or higher-value systems (production databases ahead of low-priority development scratch space), and rotate the sample deliberately so that over a defined period, a quarter for example, every system in the estate has actually been full-restore tested at least once, rather than the sampling process repeatedly and conveniently re-testing the same easy, already-known-good subset.

Alerting on failures. Any daily-check failure or restore-test failure notifies the owning team, with urgency tied to what actually failed: a checksum mismatch on a critical production system's backup is a different severity than a slow restore-test result on a low-priority archival dataset. A daily-check failure should trigger an automatic re-check before escalating loudly, since a single transient issue (a brief network error during a checksum read) is a genuinely different situation from a persistent, repeatable failure, and treating both identically either causes alert fatigue from noisy transients or under-reacts to a real, recurring problem.

False positives and remediation. A real false-positive class exists here: a restore test can fail because the isolated test environment itself had a problem, insufficient capacity, a misconfiguration, not because the underlying backup was actually bad. The workflow needs to distinguish "the backup is genuinely bad" from "the test harness had a problem" before escalating as a confirmed incident, typically through an automatic retry in a known-clean environment before declaring a real failure. For a confirmed genuine failure, the remediation workflow should immediately trigger an out-of-band fresh backup of the affected system rather than waiting for its next regularly scheduled run, plus open a ticket to root-cause why the prior backup was actually bad, so the same underlying defect doesn't quietly recur on the very next cycle.

Worked example

A 100 TB mixed environment runs daily checksum and size-sanity checks across all systems, which catch a partial backup on a file server the same night it happens (its size came in at a fraction of its typical daily volume, triggering an automatic re-check and then an alert when the re-check confirmed the shortfall). Separately, a rotating full-restore-test schedule covers roughly 25% of systems each quarter, weighted so every production database gets tested at least once a quarter while lower-priority systems rotate through over a full year; this quarter's sample restore-tests a production database into an isolated environment and runs a row-count and smoke-query check. The first attempt fails, but before escalating, the automated workflow retries in a freshly-provisioned isolated environment and the second attempt passes cleanly, correctly identifying the first failure as a test-harness capacity issue (the isolated environment ran low on disk mid-restore) rather than an actual backup defect, avoiding an unnecessary incident and a false alarm to the owning team.

Trade-offs and pitfalls

• Sampling that isn't deliberately rotated tends to drift toward re-testing whatever's easiest or already known-good, which quietly leaves some systems untested for a full-restore check indefinitely; the rotation needs an explicit, tracked guarantee that every system gets covered within a bounded period.
• Treating every restore-test failure as a confirmed incident without a retry step in a clean environment first risks flooding teams with false alarms caused by the test harness itself, which erodes trust in the whole verification process over time.
• Daily checks alone, however consistently they pass, cannot catch "restorable in theory but broken in practice," which is exactly the gap periodic full-restore testing exists to close; relying on daily checks alone is a common, dangerous shortcut at this scale.

Direct answer

The design splits into two layers that solve different problems: continuous replication (streaming or WAL shipping, WAL being the write-ahead log, an append-only record of every change Postgres makes before applying it) gets you a warm standby you can promote quickly, meeting RTO; and independent base backups plus archived WAL get you point-in-time recovery (PITR) and protection against the kind of failure replication doesn't cover, like a bad DROP TABLE that a synchronous or asynchronous standby would faithfully replicate right along with the primary. For illustration I'll target RPO (Recovery Point Objective, the maximum acceptable data loss measured in time) of roughly 30 seconds and RTO (Recovery Time Objective, the maximum acceptable downtime) of roughly 15 minutes, both stated as example targets since the question leaves them to the candidate, not as universal numbers.

Structured elaboration

RDS vs self-managed on EBS. On RDS (a managed Postgres service), automated backups continuously stream WAL to object storage, giving PITR out of the box with retention up to 35 days, and a cross-region read replica (asynchronous by default) can be promoted for regional DR. On self-managed Postgres on EBS (Elastic Block Store, cloud block storage volumes), the same capability has to be built: pg_basebackup for periodic base backups, continuous WAL archiving to object storage (commonly via wal-g or pgBackRest) for PITR, and physical streaming replication to a standby in another region. EBS snapshots can also back the base-backup layer, but need care: a snapshot taken mid-write without coordinating with Postgres (e.g. via pg_start_backup) can capture an inconsistent volume state, so either use a backup tool that handles this coordination or take the base backup through pg_basebackup instead of a raw disk snapshot.

Cross-region replication: sync vs async. Synchronous replication (the primary waits for the standby to acknowledge before committing) gives an RPO close to zero but adds the cross-region round-trip time to every write's latency, commonly tens to well over a hundred milliseconds depending on the region pair, and risks stalling the primary if the standby becomes unreachable. Asynchronous replication accepts a small replication lag (commonly seconds under normal load) in exchange for no write-latency tax; given a 30-second RPO target, async has ample headroom and is the more cost-effective default. Reserve synchronous replication for a stricter RPO tier if one is ever needed.

Failover procedure. Promote the standby (pg_ctl promote, or the managed failover API on RDS), update the client-facing endpoint (DNS or a connection proxy) to point at the new primary, and fence the old primary, revoking its ability to accept writes, so it can't diverge from the newly promoted primary if it later comes back online (a split-brain scenario, where two nodes both believe they're primary and accept conflicting writes).

Worked RTO breakdown (basis: wall-clock minutes from declared incident to first successful write, summed and checked against the 15-minute target, not each phase in isolation). Detection and decision to fail over: about 5 minutes (health-check failure threshold plus confirmation). Promotion of the standby: about 2 to 5 minutes. Endpoint cutover and client reconnect: about 5 minutes (DNS propagation and application retry/reconnect behavior). Smoke validation before declaring the incident resolved: about 3 to 5 minutes. Summed, that's roughly 15 to 20 minutes; if the total lands over the 15-minute target, the endpoint-cutover step (usually the least automatable) is the first place to look for savings, e.g. via a connection proxy that can be repointed faster than DNS TTL propagation allows.

Validation and automated DR testing. Run scheduled drills that promote the DR replica into an isolated network (not the live traffic path), run automated read/write smoke tests and a row-count or checksum comparison against the primary before the drill promotes, and time every phase against the RTO/RPO targets. Separately, test the PITR path itself (restore a base backup plus archived WAL to a specific past timestamp) on its own schedule, since it's a different recovery mechanism from replica promotion and can silently break (a missing WAL segment, an expired retention window) without the replication drills ever exercising it.

Controlled restore of production traffic. After promoting the DR replica and validating it, don't cut 100% of traffic over immediately: shift a small percentage first via weighted routing, watch error rates and replication lag on the newly promoted primary, then ramp to full traffic. Keep the old primary around, fenced but not destroyed, as a forensic copy in case anything needs to be recovered from it, and rebuild a fresh standby in a healthy region afterward so the system isn't left without DR coverage once the immediate incident is over.