Point-in-time restore for managed Postgres

Short version: always-on PostgreSQL databases on Layerbase now archive their write-ahead log continuously and can be restored to any timestamp, which drops the worst-case recovery point from about an hour to seconds under write load, bounded by about a minute when idle. A restore never touches the source database: it replays archived WAL segments onto the newest base snapshot taken before your target and brings the result up as a separate instance with its own connection string, so you end up with both copies and decide afterwards. The boundary is exact, and we proved it rather than reasoned about it: in the drill, a row committed two seconds before the target came back and a row committed four seconds after it did not, and we then ran the same thing live against the production database this business depends on. This is PostgreSQL only, it covers the always-on class because archiving pauses while a database sleeps, and your existing scheduled dumps keep their schedule and retention unchanged.

There is a specific kind of loss that scheduled backups cannot help with. Your dumps run hourly. Someone ships a migration with a bad WHERE clause at 2:59. The most recent dump is from 2:00, so everything between 2:00 and 2:59 is simply gone. Not corrupted, not slow to recover. Gone.

We knew this about our own setup. When we moved layerbase.com's own database off Neon and onto our own cloud, the honest line in the internal audit read: worst-case recovery point of about 65 minutes, with no replay between dumps. Neon, which we migrated off, had WAL-based restore to any second. We gave that up in the move and wrote down that we owed ourselves a replacement.

This is the replacement. Always-on Postgres databases on Layerbase now archive their write-ahead log continuously, and can be restored to an arbitrary timestamp. The worst case drops from about an hour to seconds under write load, bounded by about a minute when idle.

What we built

PostgreSQL already journals every change into its write-ahead log before applying it. Point-in-time restore is what you get when you keep that journal: take a base snapshot occasionally, retain every WAL segment after it, and you can replay to any moment in between. This is the mechanism behind PITR everywhere, and Postgres has shipped it for two decades. The work is in operating it safely on a multi-tenant platform.

The heavyweight route is a tool like pgBackRest or WAL-G running next to every database. We went thinner, for one main reason: those tools want to run where the storage credentials are, and the inside of a customer's database container is the one place our object-storage credentials must never be. A tenant is the Postgres superuser in their own instance; anything readable in that container is theirs.

So the piece that runs inside the container is archive_command doing a plain file copy into a spool directory. No credentials, no network, no binaries beyond cp. A shipper on the host drains each spool continuously, compresses segments, and uploads them, using credentials that never leave the host. A base snapshot lands daily via pg_basebackup, and the scheduled logical dumps keep running untouched, which matters more than it sounds: dumps are a second, independent recovery path with no dependency on the WAL archive, and portable in a way a physical archive is not.

The failure mode we designed around hardest is disk, because a stuck archiver is how PITR takes down the very database it protects. Postgres cannot recycle a WAL segment until archive_command succeeds, so if shipping stalls, WAL piles up on a disk shared with other tenants until the whole host suffers. Our spool is bounded: past a soft threshold an operator gets paged, and past a hard ceiling the pipeline deliberately fails open, discarding segments and marking the archive chain broken, loudly. That trade is intentional. A broken chain is detectable and fully recoverable with a fresh base snapshot, and the system re-anchors itself automatically once shipping drains. A full disk on a shared host is neither detectable in advance by the customer nor recoverable by us after the fact.

What a restore looks like

A restore to a timestamp produces a new database. We take the newest base snapshot from before your target, verify its checksum, replay the archived segments onto it up to the target, and bring the result up as a separate instance with its own connection string. The source database is never written to at any point. That makes restore a safe operation to reach for early: you are not choosing between your current data and your recovered data, you get both, side by side, and decide what to do next.

The semantics at the boundary are exact and worth stating plainly: a transaction committed before the target time is present, and a transaction committed after it is absent.

The drill, because "it should work" is not a durability story

Last year we started holding ourselves to a rule: no recovery path counts until it has been executed, not reasoned about. Our dump-restore runbook was proven by a supervised drill that decompressed, checksum-verified, and restored the production database in three minutes flat. PITR got the same treatment before this post was written.

On our staging fleet, against a real database on the same pipeline customers run on:

Insert a sentinel row, force a WAL segment switch.
Read the clock from the database server itself: that instant is the target.
Two seconds later, insert a second sentinel row. Switch again.
Ask the platform to restore the database to the target timestamp.
Open the restored copy and look.

The restored database contained the first sentinel, committed about two seconds before the target, and did not contain the second, committed about four seconds after it. Enabling archiving on the database, including its initial base snapshot, had taken 21.8 seconds. Every WAL segment reached object storage within 15 seconds of its switch. The restore itself, from API call to a promoted, routed, connectable database, took 38.3 seconds.

The first attempt failed, which is the part worth writing down. Postgres refuses an ISO-8601 timestamp with the T separator and Z suffix in recovery_target_time; the restored instance died at startup with invalid value for parameter. The same instant formatted as 2026-08-26 10:11:34.737+00 replayed cleanly. We fixed the format, and we also made the platform capture the restored instance's Postgres log before rolling a failed restore back, because debugging that failure blind was the worst ten minutes of the day. You only learn these things by running the drill.

The production receipts

Later the same day we did it for real, on the database this business runs on: the layerbase.com production Postgres, supervised live. No synthetic sentinels this time - production traffic wrote the evidence for us.

Enabling archiving on the live database, including the engine restart and the initial base snapshot (5.26MB compressed, captured in 3.6 seconds), took 21.1 seconds end to end. Real WAL segments started landing in object storage within seconds, and kept landing within one 15-second shipping cycle of every switch.

We picked a target of 15:19:30 UTC, sitting between two archived segments, and asked for a restore:

text

targetTime          2026-08-26T15:19:30.000Z
restore duration    32.3s (API call to promoted, connectable database)
segments replayed   6

Then we opened the restored copy and checked it the same way our dump-restore runbook checks a recovery, plus the timestamp semantics:

text

tables              46        (matches production)
user rows           823       (sane against the live count)
migrations          123       (no dev sentinel rows)
rows after target   0         (across every table)
newest replayed row rate_limit_event at 15:19:13.781 UTC

That last line is the whole feature in one row: it was written by production traffic two minutes after the base snapshot was taken and sixteen seconds before the restore target, which means the only way it could exist in the restored copy is WAL replay. Everything committed before the target is there. Nothing committed after it is. The live database was never touched.

FAQ

How far back can I restore, and how precise is the target?

To any timestamp covered by the archive, and the boundary is exact rather than approximate: a transaction committed before your target is present in the restored copy, and a transaction committed after it is absent. Under write load the worst-case recovery point is seconds, bounded by about a minute when the database is idle, because a segment still has to switch before it can ship. Every segment in our drill reached object storage within 15 seconds of its switch.

Does a restore overwrite my live database?

No, and this is deliberate. The restore produces a new instance with its own connection string, and the source database is never written to at any point during the operation. That makes it something you can reach for early, while you are still working out what happened, instead of a decision you have to be sure about first. You get both copies side by side and choose what to do next.

How long does a restore actually take?

Both times we measured it end to end, from API call to a promoted, routed, connectable database, it took well under a minute: 38.3 seconds in the staging drill and 32.3 seconds on the production database, replaying six segments. Enabling archiving on a database in the first place, including the engine restart and the initial base snapshot, took about 21 seconds in both runs.

Which databases does this cover?

PostgreSQL only, and within Postgres, the always-on class. WAL replay is a Postgres mechanism, so our other engines keep their scheduled and manual backups rather than gaining PITR. The always-on restriction is not a licensing decision: archiving pauses while a database sleeps, and a continuous history needs a continuously running database.

Does this replace my scheduled dumps?

No, it sits between them, and we kept the dumps on purpose. A logical dump is a second, independent recovery path with no dependency on the WAL archive, and it is portable in a way a physical archive is not. If the archive chain ever breaks, which our bounded spool will deliberately do rather than fill a shared disk, the dumps are still there and the system re-anchors itself with a fresh base snapshot once shipping drains.

Scope, stated honestly

This is PostgreSQL only. WAL replay is a Postgres mechanism; our other engines keep their scheduled and manual backups. It covers the always-on class, because archiving pauses while a database sleeps, and a continuous history needs a continuously running database. And it is rolling out starting with our own production database, the same way we validate everything in the durability story: we run it on the database our business depends on first, drill it there, and only then widen it.

The dumps you already have keep their schedule and their retention. Nothing about the existing backup behavior changed. PITR is the layer between them.

If you want the operational details of how backups behave on Layerbase today, the backups documentation and the new point-in-time restore page cover it.