Postgresql – Incremental Backup. Base backup creation frequency

backuppg-basebackuppostgresql

So I'm trying to understand how Continuous Archiving and Point-In-Time-Recovery works.

From what i've read there are some steps involved for the incremental backup and some steps for the recovery:

Summary of PostgreSQL Backup Steps

  1. Modify postgresql.conf to support archive log
  2. Make a base backup (full database backup)
  3. Copy base backup to remote storage.
  4. Backup WAL (archive log files) to remote storage (continuous process)

Summary of PostgreSQL Point-in-time Recovery Steps

  1. Extract files from base backup
  2. Copy files from pg_xlog folder
  3. Create recovery.conf file
  4. Start Recover

So my questions is: Is it safe to stick with this base backup forever (plus all the wal files) until an error appears, and then apply the PITR steps? Or should I re-generate the base backup periodically (each week, month?) in order to minimize any type of error when trying to recover the database?

Thanks in advance.

Best Answer

Your step 2 in PITR should not be there. You might want to remove the files from the restored copy of the base backup's pg_xlog. You don't want to copy them. (And there really shouldn't be anything to copy/move/remove, as you shouldn't be backing up that directory to start with).

If you only take one base backup and keep it forever (or until you upgrade to the next major version of PostgreSQL), then doing PITR will take longer and longer as you accumulate more archived log files. This is the driving force in how often to take base backups: how long you are willing to wait for recovery. Also, the more log files you need to replay during recovery, the more likely it is that one of them is going to somehow get lost or corrupted.

If you do decide not to take frequent base backups (or even if do take them frequently), make sure you test your PITR occasionally onto a test server. This will both verify all your log files are intact, and give you evidence of how long it will take should you need to do a real recovery.

Related Question