Renato Oliveira wrote:
> I thought the base backup was only necessary once. For example
> once you have done your first base backup, that is it, all you
> need is to replay the logs and backup the logs. What would be
> the reason(s) for you to do weekly base backups?
There are a few reasons, many of which are probably not applicable
to most environments.
(1) Most of our source databases are spread around the state --
some over 300 miles away (500 km for those with sane units of
measure). Management has mandated that we must have backups in our
central location which have been restored to confirm usability, as
well as a copy of the backup files in the source locations. We
don't have equipment at the source locations to keep a warm standby
running, so a recovery there would need to replay all WAL files
since the base backup. A weekly cycle for base backups keeps the
set of WAL files we need to keep relatively small and allows
relatively rapid recovery.
(2) Because of the large number of database clusters being backup
up (about 100), it is not feasible to actually have warm standbys
for all of them which we can just switch to in case of emergency.
We use one rather largish machine to host a all the "warm standby"
instances, just to make sure that the WAL files are applying without
error to the base backups. We keep three machines prepped and ready
to go for emergencies, but we have to do the whole PITR recovery
from the base backup to get a production-ready copy. Again, the
shorter the time from the base backup, the fewer WAL files to apply,
and the sooner we're ready to go.
(3) We're mandated to keep monthly archival "snapshot" backups, so
we just grab the first weekly base backup of each month, with the
minimum set of WAL files needed to get it running (determined by the
.backup file).
(4) A fair amount of paranoia. If any subtle bugs affecting corner
cases were ever to creep into the software, the affects would be
minimized by using a fresher base backup and fewer WAL files.
I have heard of people running warm standbys for months without
getting a fresh base, and for many users that may be the best
option. Our situation is pretty unique -- although when I say that,
people are usually quick to point out that so is everyone else's.
;-)
-Kevin