CODEWITHSUNDEEP
postgresqlwal
Sébastien Clément
Sébastien Clément

Reputation: 600

How to specify cleanup by file age or date with pg_archivecleanup

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.

Upvotes: 6

Views: 28633

Answers (2)

FuzzyChef
FuzzyChef

Reputation: 4061

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'

Upvotes: 7

joris
joris

Reputation: 121

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

Upvotes: 11

Related Questions