Re: Command to prune archive at restartpoints

On 10/06/10 22:24, Dimitri Fontaine wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Maybe we could add a new pg_cleanuparchive binary, but we'll need some
>> discussion...
>
> Would this binary ever be used manually, not invoked by PostgreSQL? As
> it depends on the %r option to be given and to be right, I don't think
> so.

Hmm, actually it would be pretty handy. To make use of a base backup,
you need all the WAL files following the one where pg_start_backup() was
called. We create a .backup file in the archive to indicate that
location, like:

00000001000000000000002F.00000020.backup

So to clean up all WAL files older than those needed by that base
backup, you would simply copy-paste that location and call
pg_cleanuparchive:

pg_cleanuparchive /walarchive/ 00000001000000000000002F

Of course, if there's a perl one-liner to do that, we can just put that
in the docs and don't really need pg_cleanuparchive at all.

> Therefore my take on this problem is to provide internal commands here,
> that maybe wouldn't need to be explicitly passed any argument. If
> they're internal they certainly can access to the information they need?

You want more flexibility in more advanced cases. Like if you have
multiple standbys sharing the archive, you only want to remove old WAL
files after they're not needed by *any* of the standbys anymore. Doing
the cleanup directly in the archive_cleanup_command would cause the old
WAL files to be removed prematurely, but you could put a shell script
there to store the location to a file, and call pg_cleanuparchive with
the max() of the locations reported by all standby servers.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com