Re: PITR and warm standby setup questions

On Tue, 2007-11-13 at 00:07 -0500, Greg Smith wrote:
> On Mon, 12 Nov 2007, Mason Hale wrote:
>
> > After the wal segment file is copied by the restore_command script, is
> > it safe to delete it from my archive?
>
> While I believe you can toss them immediately,

This is almost never possible. The last WAL file that must be kept
should be sufficient to allow recovery to restart from the last
restartpoint. So a variable number of WAL files needs to be kept, not 1,
not 2 and certainly never 0.

pg_standby with 8.2 provides a -k option to allow keeping last N files,
whereas 8.3 passes the %r parameter to show the filename of the last
file that must be kept.

> you should considering
> keeping those around for a bit regardless as an additional layer of
> disaster recovery resources. I try to avoid deleting them until a new
> base backup is made, because if you have the last backup and all the
> archived segments it gives you another potential way to rebuild the
> database in case of a large disaster damages both the primary and the
> secondary. You can never have too many ways to try and recover from such
> a situation.

Agreed

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com