Point-in-Time Recovery: How WAL Archiving Saves You After a DROP TABLE

A migration runs at 3:07pm and takes a table with it. Someone notices at 3:20. The first question isn't whether you have a backup, because you do — it ran at 2am like it does every night. The question is what happened to the database between 2am and 3:07pm, and whether any of it is coming back.

That gap is the difference between having backups and having recovery. Point-in-time recovery closes it, and it works by reusing something PostgreSQL already does on every single write for an entirely different reason. Below: what the write-ahead log really is, how a restore replays it to rebuild your database as it stood at a specific second, and the limits nobody mentions until you hit one.

Why a nightly backup isn't the answer on its own

A backup taken once a day sets a floor on how much data you can lose: everything written since it ran. Restore last night's dump after an afternoon incident and you get the database as it was at 2am, minus a full day of orders and edits that nobody has a copy of anymore. The backup worked perfectly. It just wasn't a recovery plan.

Disaster recovery has a term for that ceiling: the recovery point objective, or RPO. It's how much data you're willing to lose, measured in time. Nightly dumps give you an RPO of up to 24 hours. Its sibling, the recovery time objective (RTO), is how long you're willing to be down while you put things back. Most teams have never written either number down, and then discover both of them on the worst possible afternoon.

Point-in-time recovery drops the RPO from a day to seconds without asking you to take a snapshot every second. It does that by keeping the record of every change the database made in between.

What the write-ahead log actually is

PostgreSQL doesn't write your change straight into the data files. Before a modification touches a table's pages on disk, it's appended to the write-ahead log: a sequential record of what changed, flushed to durable storage before the transaction is allowed to report success, at least under the default commit settings. The data files catch up later, in the background, at a checkpoint.

This exists for durability, not for you. If the server loses power mid-transaction, the data files may be half-updated, but the WAL is a complete account of what was supposed to happen. On restart, Postgres replays the log against the files and arrives at a consistent state. That's crash recovery, and every PostgreSQL install does it whether or not anyone is thinking about backups.

Continuous archiving takes that same log and copies each completed segment somewhere durable instead of letting it be recycled. Once the log is being kept rather than discarded, the mechanism that recovers from a crash also recovers from a mistake. A snapshot is a photograph of the database at one moment. The archived WAL is the recording of everything that happened after the shutter closed.

One caveat on how fine-grained that recording is. WAL is archived a segment at a time, 16 MB by default, so a plain archive setup on a quiet database ships a segment only when it fills up. Getting close to a seconds-level worst case depends on the log being streamed continuously or forced to rotate on a timer, which is a detail managed platforms handle and self-hosted setups often miss — one of several lines in the real cost of self-hosting Postgres versus managed.

How point-in-time recovery works

PITR needs two ingredients: a base backup, which is a physical copy of the database files taken at some point in the past, and an unbroken stream of archived WAL covering the time since. Restoring means starting from the base backup and replaying archived log records on top of it, one after another, until you reach the moment you asked for. Then replay stops. By default Postgres pauses there rather than opening straight away, so you get a chance to look at the state and confirm you landed where you meant to before promoting it.

The thing worth internalising is that recovery runs forward, not backward. Nothing is being undone. The database is being rebuilt from an older state by replaying real history up to a chosen cut-off, which is why it can land on any second inside the window and why the result is a consistent database rather than a table with a hole patched in it. That also shapes the standard procedure, and what managed platforms do by default: the restore goes into a new instance sitting at that timestamp, and your damaged one stays untouched. Which is exactly what you want when you're not yet sure what went wrong.

Recovery targets: time isn't the only one

"Point in time" is the common case, not the only option. PostgreSQL lets you tell recovery where to stop in several ways, and the alternatives matter when a wall-clock timestamp isn't precise enough:

• A timestamp (`recovery_target_time`) — what you reach for when you know roughly when the damage happened. Note the default: recovery stops just *after* the target commit, and `recovery_target_inclusive = off` is what makes it stop before. If the target is the bad transaction itself, you want it off.
• A transaction ID (`recovery_target_xid`) — pin the cut-off to one transaction when a timestamp would sweep up innocent neighbours that committed in the same second. Transaction IDs are handed out when a transaction starts, so numeric order isn't always commit order.
• An LSN (`recovery_target_lsn`) — a precise position in the log stream, for when you're working from log analysis rather than a clock.
• A named restore point (`recovery_target_name`) — a label created with `pg_create_restore_point()` before doing something risky, which gives you a bookmark you can say out loud during an incident. It's superuser-only by default, so on a managed platform check whether it's available to you before planning around it.

Left unset, recovery simply replays everything available and ends at the newest record in the archive. That default is what you want for hardware failure, where you're recovering from a dead machine and want every committed transaction back. It's the wrong default for a logical mistake, where the mistake itself is faithfully recorded in the log and will be replayed right along with everything else.

# The shape of a recovery target in postgresql.conf.
# Managed platforms set this for you when you pick a timestamp.
recovery_target_time = '2026-07-22 15:07:00+02'
recovery_target_inclusive = off   # default is 'on' — stop *after* the target

On a managed database you generally don't write these lines. You pick a moment in a dashboard and the platform assembles the base backup, the WAL, and the target for you. Knowing what's underneath is still worth it, because it tells you what to ask for when the clock matters.

Snapshot, WAL, and a logical dump do different jobs

People use "backup" for all three of these, which is how you end up with a recovery plan that has a hole in it. They're separate objects with separate strengths.

The portability row is the one that catches people. A base backup is a byte-level copy of a PostgreSQL cluster and expects to be restored by the same major version on compatible hardware. A plain-format `pg_dump` file is SQL text, so it loads into the same or a newer major version, on a laptop or another provider entirely. That's why a second, portable copy is worth keeping alongside the platform's snapshots, and why automating a dump to your own S3 bucket solves a different problem from the one PITR solves.

What point-in-time recovery can't do

It isn't an undo button for one table

Recovery works at the level of the whole cluster, every database in the instance, not the row. Rewinding to 3:06pm gives you every table as it stood at 3:06pm, including the twenty minutes of legitimate writes that landed elsewhere before anyone noticed. If you restore over production, you've traded a dropped table for lost orders. Worth knowing too if staging and production share an instance, because they rewind together.

The workable version is a side restore: bring up a copy at the target timestamp, pull the table you lost out of it with `pg_dump -t`, and load it into the live database. If you'd rather reconcile in SQL than move files around, `postgres_fdw` can bridge the two instances so you can query the old state directly. Slower than a one-click rollback, and much easier to explain afterwards.

The retention window is a real deadline

PITR reaches back as far as the oldest base backup with an unbroken WAL archive running forward from it, typically a rolling window of days or weeks. Inside the window you can go to any second. Outside it there's nothing to replay. This is fine for the incidents you notice in minutes and useless for the ones you notice in quarters: a subtly wrong `UPDATE` from six weeks ago, a background job that has been writing bad values since a deploy nobody connected to it. By the time the report looks wrong, the correct state has aged out.

It lives in the same account as everything else

The snapshots and the WAL archive sit inside your provider. That's the right place for them, and it means they share a fate with the account they live in. Losing access, whether through a billing dispute or a lockout, takes the recovery option with it. That's the case for keeping one copy somewhere you control, which is the whole argument in automating PostgreSQL backups to S3.

The first ten minutes after a bad write

Recovery goes better when the first few decisions are already made. A rough order of operations:

1. Stop the writes. Pause the job, revert the deploy, or take the affected feature offline. Every additional write makes the eventual reconciliation harder.
2. Write down the time. The last known-good moment and the moment of damage, in a specific timezone. You'll be asked for one of them by a dashboard shortly, and memory is unreliable twenty minutes into an incident.
3. Work out the blast radius. One table or several? Deleted rows or overwritten values? This decides whether you extract one table from a copy or roll the whole database back.
4. Restore to a new instance, not over the old one. Target a timestamp a little before the damage. The damaged database stays available for comparison.
5. Verify before you migrate anything. Count rows, spot-check the records people complained about, confirm you landed before the bad transaction rather than after it.
6. Move the good data across, then write down what happened while it's fresh. The postmortem is where a retention window or a missing alert gets fixed.

None of this requires special tooling. It requires that continuous archiving was already running before the incident, which is the part you can't arrange retroactively.

How Runsite handles it

Setting up continuous archiving yourself means configuring an archive command, storing segments somewhere durable, monitoring for gaps in the stream, and periodically proving that a restore actually works. It's a real piece of infrastructure, and its failure mode is silence.

Managed PostgreSQL on Runsite runs it as part of the service. WAL segments stream continuously to durable storage, a full snapshot is taken daily during quiet hours, and recovery points are kept for 7 to 30 days depending on plan. Restoring means picking a timestamp in the dashboard, and Runsite provisions a new database holding your data as it was at that second — the side restore described above, without assembling it by hand.

A WAL archive and a snapshot are complete copies of your data, so where they sit matters as much as where the database does. Both stay in the Frankfurt, Germany region, which means a restore never pulls data across a non-EU boundary, and it's the half of the residency question that where to store EU user data covers in full. The pillar on managed PostgreSQL lists durability as one line in a longer inventory of operational work; this is what that one line actually contains.

What to remember

PostgreSQL writes every change to the write-ahead log before it touches the data files, for crash safety. Archive that log instead of recycling it and the same mechanism becomes a time machine: take a base backup, replay the archived WAL on top of it, and restore to a specific time by stopping at the second you name. That's point-in-time recovery, and it takes your worst-case data loss from "since last night's backup" down to seconds.

It rebuilds a database, not a row, so recovering one table means restoring a copy and lifting the data out. It reaches back only as far as the retention window. And it lives in your provider's account, which is why a portable dump in a bucket you own is still worth keeping. Start a managed PostgreSQL database with continuous WAL archiving already running, and the option to rewind is there before you need it rather than after.