Postgres Point-in-Time Recovery Kit

Who it is for

You run one PostgreSQL server, on a VPS or a box in a closet, and your backup is a nightly pg_dump. That protects you from losing the disk. It does not help when someone runs DELETE FROM orders without a WHERE clause at 10:15 and you notice at 10:40. A dump from last night puts you back 12 hours. Point-in-time recovery puts you back to 10:14.

pgBackRest and Barman are full-featured tools and take an afternoon to learn. For a single small server this kit is the short version: bash scripts, one archive, one cluster, nothing hidden.

What it does

• Continuous WAL archiving that is set up safely: it never overwrites a file, it fails loudly on a full or read-only disk, and a failing archive is detected before the disk fills.
• Base backups that are verified with pg_verifybackup after they are taken, packed, and checked against a checksum.
• Retention that works out what to delete from the oldest base backup you keep, so it cannot remove WAL that a kept backup still needs.
• Restore to a timestamp, an LSN, a named restore point, or the newest archived WAL, onto a new directory, with the timeline handling done for you.
• A cron monitor that checks the age of the last archived segment and the backlog, with Telegram or webhook alerts that do not repeat themselves and say when things recover.
• A drill that builds data, takes a backup, makes a deliberate mistake, destroys the server and restores it on a clean container, then compares row counts and checksums.

A look at everyday use

This is what the daily and emergency commands look like. The scripts are plain bash; you can read every one of them.

./backup-base.sh && ./prune.sh          # nightly
./check-archive.sh                      # every 5 minutes, exit code plus alert
./verify-archive.sh                     # daily: is the WAL chain complete?

# the bad DELETE happened around 10:15 UTC
./restore-to-time.sh --target-dir /srv/restore/r1 \
    --time "2026-11-09 10:14:59+00" --start --port 5433
psql -h /tmp -p 5433 -d postgres        # look around, then copy back what you need

Other targets: --name before_migration (a restore point you created), --lsn 0/5000060, and restore-latest.sh for plain disaster recovery. A --pause option stops recovery at the target so you can inspect the data read-only before deciding.

What it refuses to do

Most of the work went into the things that should not silently go wrong.

• A missing WAL segment in the middle of the archive. The restore stops, names the segment and exits non-zero. In our tests, forcing it through gave back 200 of 410 rows with no error, which is exactly why it refuses by default.
• A target time earlier than the first base backup, restoring over a data directory that is not empty, or over the live one, and running as root.
• Overwriting an archived file, including when a restored server restarts its timeline numbering.
• Deleting WAL that a kept base backup needs.
• Tablespaces. The backup script stops instead of producing something that will not restore.

Measured

One run on a laptop-class ARM64 machine, PostgreSQL with default settings.

Time to restore (destroy the server, restore on a clean container)

How much you can lose

20 inserts per second, then kill -9 and a restore from the archive alone. "Lost" means commits that were acknowledged but missing afterwards.

That last row is the one to remember. With the default archive_timeout and a quiet server, a lost disk loses everything since the last base backup. The guide explains the setting and its cost: a forced switch archives a full 16 MiB file unless you compress.

Overhead

pgbench, five interleaved rounds: median 5,156 tps with archiving off, 5,112 with gzip and 5,167 without. The differences are within the 2 percent spread between rounds: no archiving overhead showed up on this machine.

How it was tested

122 automated checks on PostgreSQL 15 (Debian package) and again on PostgreSQL 17 (PGDG), in clean Debian 12 containers, plus the two-container destroy-and-restore drill on each. They cover a failing archive stopping WAL recycling, the archive command on local and SFTP targets, base backups with a corrupted file, every restore target, a second recovery on a new timeline, retention, and all the refusals above.

Requirements

Debian 12 with the Debian PostgreSQL packaging, one cluster, bash, curl for alerts, and openssh-client if you archive over SFTP. You need root for setup and the postgres user for everything else.

FAQ

I already use pgBackRest. Do I need this?

Not necessarily. pgBackRest is a full-featured tool. This kit is for when you want something small enough to read completely, or a way to understand what pgBackRest does for you.

Can I restore a single table?

You restore a whole cluster to a new directory at the point you choose, then copy the table you need out of it. The drill uses exactly that pattern.

Does it work with Docker or managed Postgres?

No. It needs access to the server's configuration and files. On a managed service, use the provider's own point-in-time recovery.

What does it cost in disk?

It depends on write volume and archive_timeout. The guide gives WAL volumes for a few workloads and the compression numbers we measured.

License

Use it on your own servers and for your employer's. Please do not resell or redistribute the files.

Changelog

• 0.1.0: first release.

Read more

• Restore PostgreSQL to a timestamp with recovery_target_time
• PostgreSQL WAL archiving from scratch on a single Debian VPS
• How much data a disk failure costs with WAL archiving
• pg_dump vs physical backup vs PITR: which to use

Get it

Buy on Polar: $39

Payment, tax and invoices are handled by Polar as merchant of record. You receive a download link after checkout.