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.

*Don't do this*, it's a great way to seriously mess up the database. PostgreSQL manages the xlogs its self. If you're getting a pile-up of WAL, the question is why? What's your `checkpoint_segments` and `wal_keep_segments` ? — Craig Ringer, Jun 06 '13 at 00:26
Are you trying to run this on the master? or the replica? pg_archivecleanup is to clean up the archive logs on the standby server, no the pg_xlog on the master. — Thilo, Apr 17 '14 at 04:33

joris · Answer 1 · 2014-04-14T14:43:08.517

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

score 7 · Answer 2 · edited Dec 05 '17 at 07:46

You are not supposed to be running pg_archivecleanup via shell script. The purpose of that utility is to be called by the replica through recovery.conf, when it reads the archive directory. Only the actual replica database knows what files it can get rid of.

Also, archivecleanup is not used on your pg_xlog directory. It is used on the archive copy of your logs, which is stored in some location you've specified using archive_command on the master.

Example:

master postgresql.conf:

archive_command = "scp %f replica:/usr/share/wal_archive:%r"

(note: I strongly recommend having archive command call a script instead of the above)

replica recovery.conf:

archive_cleanup_command = 'pg_archivecleanup /usr/share/wal_archive %r'

How to specify cleanup by file age or date with pg_archivecleanup

2 Answers2