0039. The control plane's PostgreSQL has point-in-time recovery through pgBackRest
Status: accepted, 2026-10-30. Refines 0014: restic stays the engine for every
other source.
Context
The platform's databases were copied once a night by pg_dump, so a mistake in the afternoon (a bad migration, a
delete) or a lost server lost up to a day of sign-ins, secrets' versions, documents, mail and AI conversations.
Point-in-time recovery needs WAL archived continuously and base backups to replay it from. restic (ADR 0014) could
carry WAL as a snapshot a minute, but that means thousands of snapshots, contention with the exclusive locks of
retention and checks, restores that open the repository once per batch, and our own code for timelines, history
files and partial segments — a lot of code where a bug loses data. wal-g does the job but isn't packaged by the
distributions. pgBackRest is packaged by Debian, Ubuntu and PostgreSQL's own repository (PGDG), archives through a
bounded local spool, takes block-incremental base backups, encrypts, keeps time-based retention, verifies, and
restores a cluster to a time or one database out of it.
Decision
- pgBackRest archives the cluster. xvii-node installs it from the distribution (PGDG's when the
distribution's is too old), writes its configuration and PostgreSQL's (
archive_mode, asynchronous
archive-push, archive_timeout of a minute, WAL compression), and runs full backups weekly and incremental
ones nightly, which backupsd schedules, watches and records.
- In a destination, beside restic. The archive is a
pitr folder of an S3, SFTP or local destination, with
its own cipher passphrase in the recovery kit; restic ignores the folder.
- The database outlives its archive. The spool is bounded; past it, pgBackRest drops WAL rather than let the
disk fill and stop PostgreSQL. The chain breaks, a critical alert says so, and a full backup starts as soon as
archiving works again.
- Restores: one database as it was at a moment, into a new name or in place (through a scratch copy of the
cluster), and the whole cluster at a moment when the instance is restored. A restored cluster archives nothing
until a person says the old server is off; a drill never does.
- Targets: archiving more than 15 minutes behind pages; any second of the last 7 days can be restored.
Consequences
- The databases' RPO drops from a day to minutes; data folders keep their snapshots' RPO (an hourly plan is
suggested), and services whose folder must match their database restore both from one pair.
- The recovery kit gains pgBackRest's configuration, and a third format beside restic's and the Telegram
channel's; without xvii, the stock
pgbackrest with the kit restores it.
- Turning archiving on restarts PostgreSQL once. The archive costs what the platform writes as WAL, measured
before it is turned on.
- Vaults and Telegram channels can't hold the archive.
- Design:
superpowers/specs/2026-10-30-backups-dr-design.md in this repository.