Re: Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 5:37 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> ISTM what's really needed is a good way for users to handle retention for
> both WAL as well as base backups. A tool that did that would need to
> understand what WAL is required to safely restore a base backup. It should
> be possible for users to have a separate retention policy for just base
> backups as well as backups that support full PITR. You'd also need an easy
> way to deal with date ranges (so you can do things like "delete all backups
> more than 1 year old").

Anything else than measured in bytes either requires a lookup at the
file timestamp, which is not reliable with noatime or a lookup at WAL
itself to decide when is the commit timestamp that matches the oldest
point in time of the backup policy. That could be made performance
wise with an archive command. With pg_receivexlog you could make use
of the end-segment command to scan the completely written segment for
this data before moving on to the next one. At least it gives an
argument for having such a command. David Steele mentioned that he
could make use of such a thing.

> Perhaps a good starting point would be a tool that lets you list what base
> backups you have, what WAL those backups need, when the backups were taken,
> etc.

pg_rman? barman?
--
Michael