How to specify cleanup by file age or date with pg_archivecleanup

Question

Is there a way to specify files by age or date instead of by a hardcoded name to cleanup the WAL archives with the pg_archivecleanup command ?

For example, using the following command is pretty straightforward:

pg_archivecleanup "C:\Program Files\PostgreSQL\9.2\data\pg_xlog" 000000010000004600000045

where 000000010000004600000045 is the file name, and any file created before will be deleted.

However, if I want to automate the process, there must be a way to choose the files by age/date.

Answer 1

You can perfectly use it as a standalone tool.

I see you use Windows, so I can't help you, but here's what I would do on Unix.

The following command will call for a cleanup up to the more recent checkpoint older than 3 days. You must set $ARCHIVEDIR so it matches your dump of archive logs.

find $ARCHIVEDIR -mtime +3 -name '*backup' -printf '%f\n' | sort -r | head -1 | xargs pg_archivecleanup $ARCHIVEDIR 

A more complete script would be :

#!/bin/bash

ARCHIVEDIR='/var/lib/pg_xlog_archives/whatever/'
CHKPOINT=$(find $ARCHIVEDIR -type f -mtime +3 -name '*backup' -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
/usr/lib/postgresql/9.3/bin/pg_archivecleanup $ARCHIVEDIR $CHKPOINT

find $ARCHIVEDIR -type f -mtime +3 -a -name '*backup' -a ! -newer $CHKPOINT -delete