pgBackRest

Which files let you recover to a time between backups?

Keep a usable base copy and the continuous PostgreSQL change log up to the point you need. PostgreSQL calls this the write-ahead log, or WAL. If the log has a gap, the existence of an earlier backup does not prove the later point is recoverable.

A full backup can serve as the base. Differential and incremental backups need their earlier backup files. Track what the chosen copy depends on before shortening retention or moving archives.

Why do you need a separate PostgreSQL restore machine or directory?

This restores the database server’s data files. For a rehearsal use an empty isolated target and compatible PostgreSQL installation. Confirm the database service at the target is stopped, the directory and any separate data folders belong to the test copy, and the copy cannot write to the production backup store.

The restore can replace or remove destination files. External uploads and third-party sign-in services need their own recovery plan. PostgreSQL access rules and configuration kept outside the copied data directory also need review.

What must you budget for and keep outside the server?

pgBackRest is MIT-licensed open-source software. Your bill comes from the machines, storage for backup files and the change log, transfer where applicable, and the time and space needed to test recovery.

Keep the backup store accessible to a recovery operator, with the matching configuration and any encryption passphrase stored separately. A provider-hosted database may not give you the server access this tool needs. Check what you operate before choosing it.

Can you tell on paper whether a 9:30 copy is possible?

Use a fictional PostgreSQL server and draw a 9 a.m. base copy. Do not connect a database or run a restore.

1. 1
   Put the changes in order

   Draw an intact log through 9:35. Put a new booking at 9:20 and a mistaken update at 9:32. A copy recovered to 9:30 should contain the booking and omit the mistaken change.
2. 2
   Remove a log segment

   Cross out the 9:10 segment. Mark the 9:30 point unverified and find what other intact copy you could check. Do not assume a later segment can fill the gap.
3. 3
   Draw the separate target

   Label the isolated directory and start the paper check with only the test database. Keep the live app and the live backup store outside its write access.