Impact on Recovery Time Using A Single Perpetual Base Backup and WAL Archives

Question

What would be the impact on recovery time when restoring a Postgres database from a backup where one base backup was taken at the start of the backup strategy and then only applying WAL archives from anywhere between a week, to a month, to even 3 or 6 months?

From the perspective of an Oracle RMAN incremental backup strategy, Oracle recommends weekly level 0 backups combined with daily level 1 incrementals, however, I am unable to find any such recommendations with regards to Postgres.

All Postgres Continuous Archiving and Point-in-Time Recovery articles mention only one base backup and then archiving WAL from that point onward, but how would this impact recovery time compared to having a weekly base backup and daily WALs strategy?

The reason why I’m asking this is because Redstor (the cloud backup solution we use) supports deduplication and backup archiving, so we’re trying to find the best backup strategy that will provide PITR where the total size of the backup on the backup server’s filesystem will always reflect the total size of the database in its current state. When it comes to restoring a backup, we can choose any 24 hour time period to restore from and replay from the backed-up WAL files to any point in time within 24 hours to bring the system to a current state.

Answer 1

I think the answer is "it depends".

In my experience, the thing it depends upon mostly is the amount of data you are restoring, bearing in mind that the amount of data is the base backup plus all of the WALs since that base backup.

This means that:

• if you have a large database (base backup) with few UPDATEs/INSERTs, then there will be very little WAL data to restore. In this case you can have a large period between base backups, with little impact on the restore time.
• if you have a small database which is heavily UPDATEd/INSERTed then a large amount of your restore time will be spent replaying WALs. In this case having a large period between base backups, will lead to much increased restore times.