Back up Postgres with pgBackRest in weekly fulls and daily differentials

Decision

Run pgBackRest on a grandfather-father-son schedule against whichever node is the Patroni primary. A full backup runs every Sunday at 03:00 UTC and is kept for 52 weeks. A differential against that full runs at 03:00 on every other day, and the seven most recent differentials are kept. Incrementals run every six hours between them. Postgres archives WAL continuously to the same repository, an encrypted Cloudflare R2 bucket. WAL retention is keyed to differentials, so continuous WAL is kept back to the most recent full or differential backup. pgbackrest verify checks the repository weekly.

Why

We want a year of restore points. Daily fulls would store 365 copies of the database for that year; weekly fulls store 52.

Differentials bound the restore chain. A restore inside the current week reads the Sunday full, one differential and at most one incremental, then replays WAL. A differential depends only on its week's full, so one bad differential costs one day of restore points.

Disaster recovery is one large transfer out of object storage, and R2 charges nothing for egress.

Rejected alternatives

• A full backup every day. Seven times the storage for the same year of coverage.
• Incrementals with no differentials. Each incremental depends on the one before it, so one corrupt backup breaks every later restore point until the next full, and restore chains grow through the week.
• Other S3-compatible storage. Egress fees land exactly when we need the data back.

Consequences

Point-in-time recovery reaches back to the most recent full or differential backup, which is at most about a day. Older targets restore to a backup boundary: a six-hourly point within the past week, then a Sunday full for the rest of the year. A wider PITR window would need longer WAL retention and more stored WAL. Every node runs the backup timers, and replicas skip the run, so backups follow a Patroni failover without reconfiguration.