Re: WAL archiving

On Fri, Feb 22, 2008 at 12:23 AM, Shilpa Sudhakar

<ssudhakar(at)internode(dot)com(dot)au> wrote:

> Since the wal logs keep increasing, do we take the base backup every now
> and then so that we can delete the old log files?
> How often do we take a base filesystem backup keeping in mind that our
> systems are 24 x 7.

Depends on your workload and needs.

Say, if you have a policy on having ability to "go back in time" for
at least 30 days, then
you must keep last base backup that was done before 30 days and all
the log files since
then.

But your primary concern should be probably recovery time after
failure (your whole machine
just fried ;)). What you need to do is restore last backup and reply
all archival WAL logs
since then. And assume worst possible situation (the machine fried
just before full backup),
for a weekly backups it means 7 days worth of WAL-log reply.

Now, if your database is DML-heavy recovery won't be very fast, so you
might want to take
base backups more frequently. Or better -- set up a standby server
which will constantly
reply the backups.

Regards,
Dawid Kuroczko

PS: And before setting a policy, do try test recovery and note down
how much time it took.