CharmingGroot

Production Server Setup Notes, Ep. 3 - DB Backup Strategy (pgBackRest)

infra6 min한국어

Also available in Korean: 운영계 세팅 회고 ep3 - DB 백업 전략 (pgBackRest)

Requirements

  • Data must be recoverable to a chosen point in time after a failure (Point-in-Time Recovery, PITR)
  • Backups are kept on a different disk from the DB (a separate HDD)
OS disk OS · k3s SSD DB data HDD backup repository WAL backups The DB and its backups live on different disks
DB data lives on the SSD, the backup repository on a separate HDD.

Why DB dumps (pg_dump) are not enough

A dump made with pg_dump holds only the data as of the moment it ran.
With a daily dump in the early morning, a failure in the afternoon can only be rolled back to that morning.
Every change in between is lost.

PITR works differently.
PostgreSQL writes each change to the WAL (Write-Ahead Log) before changing the data itself.
Take a base backup, keep every WAL file after it, and you can start from the base backup and replay WAL up to any moment you choose.

Daily dump Base backup + WAL Day 1 dump Day 2 dump Failure changes since the last dump are lost restore point base backup replay WAL to recover to any moment in between
A dump only takes you back to the moment it was taken. With a base backup and WAL you can go back to any moment before the failure.

Why pgBackRest

Base backups and WAL archiving are possible with PostgreSQL alone (pg_basebackup, archive_command).
But expiring old backups and WAL, checking that backups are intact, and getting the restore procedure right are all left to you.
pgBackRest provides the following in one tool.

  • Full / Differential / Incremental backups
  • Parallel backup and restore (process-max)
  • zstd compression
  • Retention-based expiry of backups and WAL
  • Repository checks (check, verify)

Backup setup

  • Full backup every Sunday, Incremental backup every day Monday to Saturday at 02:00 (example time)
  • Two Full backup sets are kept (repo1-retention-full=2)
  • WAL is archived to the backup repository continuously through archive_command
Weekly schedule Sun Full Mon Incr Tue Incr Wed Incr Thu Incr Fri Incr Sat Incr Backup WAL WAL archived continuously · two Full sets kept · 02:00 is an example time How the backup runs CronJob schedules Full · Incr RBAC: get·exec on DB pod only DB pod PostgreSQL pgBackRest archive_command kubectl exec Backup repository separate HDD WAL Full · Incr
Daily Incrementals build on the Sunday Full, and WAL archiving continues in between.

Why mix Full and Incremental backups

An Incremental backup holds only the changes since the previous backup, so it reads and writes less, finishes faster, and uses less space.
But it depends on the earlier backups, so if one of them is damaged or lost, the Incrementals after it are unusable too.
A fresh Full every week keeps the number of Incrementals depending on one Full to six or fewer.

The reason for daily Incrementals is restore time.
A restore brings back the nearest backup and then replays only the WAL after it.
With a weekly backup only, the worst case replays six days of WAL; with daily backups the WAL replayed during a restore never exceeds one day.
WAL itself is kept for about 7 to 14 days under the retention policy.

Incrementals depend on the previous backup Sun Full Mon Incr Tue Incr Wed Damaged Thu Incr Fri Incr Sat Incr Thu–Sat unusable for recovery chain restarts at each weekly Full WAL replayed for a Thursday-afternoon failure Failure Full only replay 4+ days since Sunday Daily Incr replay only since Thu 02:00
Incrementals depend on the earlier backups, and a restore replays only the WAL after the nearest backup.

A Differential backup holds every file changed since the last Full, so it grows through the week. That is why the daily backups are Incremental.

Retention policy for backups and WAL

When a Full backup expires, pgBackRest expires its Incrementals with it and removes WAL older than the oldest remaining Full.
For example, with repo1-retention-full=2, the moment the third Full finishes, the oldest Full, its Incrementals, and the WAL older than it are cleaned up.

So the range PITR can reach is not a fixed two weeks.
Right after a new Full it goes back to last week’s Full; just before the next Full it reaches back almost two weeks, so it swings between about 7 and 14 days.
With only one Full set, the previous Full is removed right after a new one, leaving nothing to fall back on if the new Full is damaged.
That is why two sets are kept, so a damaged new Full can still fall back on the previous one.

Wk-3 Full Week 1 · expired Week 2 Recoverable range ~7 days Wk-3 Sat Week 1 · expired Week 2 Week 3 Recoverable range ~13 days ■ Full ▭ Incrementals and WAL in between
With two Full sets kept, the recoverable range swings between about 7 and 14 days.

WAL archiving and archive_timeout

archive_command is called only for completed WAL files.
With light writes a file can stay open for a long time, and the changes in it are not in the backup repository yet.
With archive_timeout, once that time has passed the server closes even a partly filled file, archives it, and switches to a new one.

0 min 5 min 10 min 15 min WAL files archived at full size even if partly filled Failure lost Repository archived files are all the same size ■ data actually written to the file A shorter archive_timeout loses less, but archives more files
After archive_timeout a partly filled WAL file is closed and archived. A failure loses the last stretch that was not archived yet.

A shorter value loses less, but files closed early are the same size as full ones, so the backup repository grows.
The official PostgreSQL documentation also warns against very short values and says a minute or so is usually reasonable.
We first set the acceptable loss on a sudden failure (RPO) at five minutes, and set archive_timeout to 300 seconds to match.
That is longer than the documented guideline of about a minute, but it archives fewer WAL files.
So a sudden power loss can lose up to about five minutes of changes.
On a clean shutdown PostgreSQL archives the remaining WAL, so nothing is lost.

The backup runs from a Kubernetes CronJob that enters the DB pod with kubectl exec.
The CronJob's RBAC permissions are limited to get and exec on that one DB pod.

Restore

A restore overwrites the current DB data directory with the data as of the chosen point in time.
Run by mistake, it would wipe the current data, so an interlock keeps it from running by accident.
The DB pod enters restore mode only when a restore request file exists in the server’s backup directory and the helm values carry the same restore ID.
If either is missing, it starts normally.

Restore request file server backup directory helm values restore ID Both present and IDs match? Start in restore mode old data dir kept Start normally yes no
Restore mode starts only when the restore request file and the helm values both match.

Before restoring, the existing data directory is renamed and kept rather than deleted.
After the restore the request file is removed automatically, and dropping the restore entry from the helm values returns the pod to normal mode.

Verified on the production server too

Before moving data, backup and restore were tested with a copy of the real data and with a large test dataset made by copying its tables n times.

ItemReal data copyLarge test dataset
Full backup8.3 s26 s
Parallelism (process-max)-4: 26 s · 8: 19.7 s · 16: 19.9 s
RestorePoint-in-time restore 5.1 sLatest-point restore 37.2 s (46.6 s until the DB is ready)
  • First scheduled Incremental backup → ran as expected
  • Point-in-time restore → confirmed all deliberately deleted data came back
  • Latest-point restore → recovered even the last changes that had not been archived yet, because PostgreSQL archives the remaining WAL on a clean shutdown

A failure test was also run.
Archiving was made to fail on purpose by locking the WAL directory in the backup repository with chmod 000.
Meanwhile the DB kept serving writes, and the WAL it could not archive piled up in the pg_wal directory.
Once the directory permissions were restored to 750, the backlog of WAL was archived within 30 seconds.

Notes

The postStart hook blocked by SELinux

The DB pod creates the pgBackRest stanza in a postStart hook when it starts.
The hook sent its output to /proc/1/fd/1 to show up in the container log, but SELinux (Enforcing) blocked that write (Permission denied) and the command never started.
So the stanza was never created, and WAL archiving was failing too.

The development server runs SELinux in Permissive mode, so the problem never showed up there.

The fix:

  1. First, pgbackrest stanza-create was run by hand inside the DB pod. The backlog of WAL started archiving right away, and pgbackrest check passed.
  2. The Helm chart was changed so the hook writes its output to a log file inside the pod instead of /proc/1/fd/1. After redeploying, the hook was confirmed to check the stanza on DB pod restart, and check passed.

References