Postgres Point-in-Time Recovery on Your Own Server

If your only Postgres backup is a nightly pg_dump, your worst-case data loss is a full day. Point-in-time recovery, PITR, closes that gap. It combines a base backup with a continuous stream of the write-ahead log, so you can restore the database to any moment: five minutes before the bad migration, one second before someone dropped the wrong table. For any database that holds customer data, PITR is the difference between losing an afternoon and losing almost nothing. I run it on the Postgres box under most of my portfolio, and it is not exotic. It ships with Postgres.

Why nightly dumps are not enough

A dump is a snapshot in time. It is fine for a database that rarely changes. It is a liability for one that takes writes all day. The math is brutal: if the dump runs at 2am and the server dies at 6pm, everything between 2am and 6pm is gone. Sixteen hours of orders, signups, and edits, wiped, because your recovery point was set by a cron schedule instead of by what the data is worth.

You could run dumps every hour to shrink the window, but dumps are heavy. They lock nothing on modern Postgres but they still cost CPU and IO, and running one every hour on a busy database is wasteful. Worse, an hourly dump still leaves you losing up to an hour. PITR gets your data loss down to seconds with far less overhead, because it captures change continuously instead of re-reading the whole database on a timer.

How point-in-time recovery works

Two pieces make PITR work.

• A base backup: a full physical copy of the data directory, taken with pg_basebackup, that acts as your starting point.
• The write-ahead log: Postgres writes every change to the WAL before it touches the data files. If you archive every WAL segment as it fills, you have a complete record of every change since the base backup.

To restore, you lay down the base backup, then replay the WAL forward up to the exact time you choose. Postgres calls this the recovery target. You point it at a timestamp and it stops replaying there, giving you the database as it existed at that second. That is the magic: you are not restoring to when a backup ran, you are restoring to when you want.

Setting it up on a self-hosted box

The configuration is a handful of settings. Turn on WAL archiving with archive_mode = on and set an archive_command that copies each finished WAL segment somewhere safe. That "somewhere safe" is the important part. Do not archive to the same disk as the database, because a disk failure would take both. Push the WAL offsite to object storage you control, the same place your base backups live.

Take a base backup on a schedule, weekly is common, and keep the WAL that has accumulated since. Your restore window is base-backup age plus every WAL segment since. Prune old base backups and their WAL together so storage does not grow forever.

Encrypt the archive before it leaves the box. WAL contains your actual data, so treating it like ciphertext offsite is the same discipline I apply to every backup, and it fits how I already manage secrets without a cloud vendor: nothing sensitive leaves the server in the clear.

PITR is only real if you restore it

This is the part people skip. A PITR setup that has never been exercised is a stack of WAL files you hope replay cleanly. They do not always. An archive gap, a missing base backup, a permissions problem on the storage bucket, any of these turns your elegant recovery into a dead end at the worst possible time.

So drill it. Provision a clean box, pull the base backup and the WAL, and restore to a timestamp you pick. Confirm the data is right and time how long it took. That number is your real recovery time, and running the drill is the only honest way to know your RPO and RTO targets are met rather than assumed. I fold this into the same restore drills I run across the portfolio.

I run Postgres and its WAL archive on plain VPS boxes through HostSSH. PITR is the single feature that lets me keep customer data on my own hardware without losing sleep. It ships with the database you already run. Turn on archiving, take a base backup, and practice the restore before you need it.