PostgreSQL 9.2 – Specify Cleanup by File Age

archivepostgresqlpostgresql-9.2write-ahead-logging

I want to keep my wal_files for 30 days, using the pg_archivecleanup.

I've followed these scripts:

https://stackoverflow.com/questions/31346606/postgresql-automatic-archive-purge-not-occuring

and

https://stackoverflow.com/questions/16943599/how-to-specify-cleanup-by-file-age-or-date-with-pg-archivecleanup …

#!/bin/bash

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
CHECKPOINT=$(find $ARCHIVEDIR -type f -mtime +30 -type f -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
/usr/bin/pg_archivecleanup $ARCHIVEDIR $CHECKPOINT

find $ARCHIVEDIR -type f -mtime +30 -a -type f -a ! -newer $CHECKPOINT -delete

… but it's not working for me.

I'm getting this error:

pg_archivecleanup: invalid filename input
Try "pg_archivecleanup --help" for more information.
find: `-delete': No such file or directory

my recovery.conf:

archive_cleanup_command = 'pg_archivecleaup_mv.bash -d'

Is there any way to make this work?

UPDATE 1:

I've done some tests…

recovery.conf:

archive_cleanup_command = 'pg_archivecleaup_mv.bash'

pg_archivecleaup_mv.bash:

declare -r -x PATH='/usr/local/bin:/usr/bin:/bin';

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
CHECKPOINT=$(find $ARCHIVEDIR -type f -mmin +3 -type f -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
exec "pg_archivecleanup" "$ARCHIVEDIR" "$CHECKPOINT" "%r";
find $ARCHIVEDIR -type f -mmin +3 -a -type f -a ! -newer $CHECKPOINT -delete

-mmin +3 for testing purpose…

By doing that, the error changes:

pg_archivecleanup: too many parameters
Try "pg_archivecleanup --help" for more information.
archive_cleanup_command "/var/lib/pgsql/bin/pg_archivecleaup_mv.bash": return code 512

UPDATE 2: Following @Sebastian Webber idea, I changed the pg_archivecleaup_mv.bash script; I'm getting the same issue that I had using the script I wrote:

#!/bin/bash -x

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
LAST_BACKUP=$(ls -lto ${ARCHIVEDIR})

FILE_LIST=$(/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}")

## TEST FIRST
/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 | xargs ls -lh

##THEN UNCOMMENT THIS LINE
#/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 -delete

recovery.conf:

archive_cleanup_command = 'exec /var/lib/pgsql/bin/pg_archivecleaup_mv.bash -d "%r"'

  • With or without the "%r" I get the error: pg_archivecleanup: invalid filename input

  • Without the "%r", I put the %r into the script: /usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" "%r"| find -type f -mmin +30 | xargs ls -lh – Then I get the error: pg_archivecleanup: too many parameters

Best Answer

First of all, if you need to retain the archived WAL files it's a good pratice to put in another directory. If you use the archive files to recovery a standby server, i may prefer make a separated copy for it.

You can adjust that in your master server with the parameter archive_command.

Now, if you want to keep just one directory for same purpose, remember that pg_archivecleanup needs the archived wal location (seems ok with the variable $ARCHIVEDIR) and the last backup file (witch contains the first and last archived wal file for the backup, it contains a .backup extension, eg: 000000010000003700000010.00000020.backup) and that it's missing.

To solve your script, find the latest .backup file on the $ARCHIVEDIR directory and call pg_archivecleanup with the -n option to get a list of wal files to delete then use that list to find the files older than 30 days.

My idea it's something like this:

https://gist.github.com/sebastianwebber/855585ef3ef6a50c50cca7b57bc8d7d3#file-pg_cleanup_oldmorethan_30days-sh

Please, take a look at the documentation for more details: https://www.postgresql.org/docs/current/static/pgarchivecleanup.html

Related Question