Re: Streaming Replication and archiving

On Thu, Jan 21, 2010 at 1:48 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Huh?  *Archived* segments aren't supposed to get deleted, at least not
>> by any automatic Postgres action.  It would be up to the DBA how long
>> he wants to keep them around.
>
> OK.  The docs indicated that the segments needed to be kept around in
> case the slave fell behind.  If that's not the case (as it appears not
> to be) then they can just be deleted by cron job, or the archive_command
> on the master can be changed.

It's definitely the case.

Generally speaking each base backup image has an oldest archived log
which is needed to make it useful. And each standby database -- which
is just a recovered base backup which has been rolled forward some
distance already -- also has one.

What would be useful is a tool which given a list of standby databases
and list of base backup images can apply a set of policy rules to
determine which base backups and archived logs to delete.

The policy might look something like "keep one base backup per week
going back a month and one per day going back seven days and keep
archived logs going back far enough for any of these base backups or
any of these live replicas."

Bonus points if you can say "also keep one base backup per month going
back three years with just enough archived logs to recover the base
backup to a consistent state".

--
greg