Specify cleanup by file age - PostgreSQL 9.2

Question

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

Answer 1

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

• Patrick B.: Thank @Sebastian. Please, would be nice if you could give me an example.. I wasn't using pg_basebackup to do this... do I need to? Cheers
• Sebastian Webber: I just update with a gist sample. please test before remove the files.
• Patrick B.: Oh ok! You wrote pg_basebackup but I think you meant pg_archivecleanup - I'm testing your script... I got an error: pg_archivecleanup: invalid filename input but will try to solve it. Also, I noticed that I get a list of all the data inside /var/lib/pgsql/9.2/data - It's like if I do: ls -la in that directory... do you know why?
• Sebastian Webber: yes @PatrickB. I mean pg_archivecleanup. I update the response. :)
• Sebastian Webber: run the script again with bash -x to see this inputs and the error properly.

Answer 2

Why are you doing this with pg_archivecleanup at all? The point of pg_archivecleanup is that it selects which files to remove based on the needs of the standby. But that apparently isn't what you want to do.

If you want to delete files older than 30 days, just write a find command that deletes files older than 30 days, and invoke it once a day from cron or your favorite scheduler.

find $ARCHIVEDIR -type f -mtime +30 -delete

• Patrick B.: I'm wanna keep the wal_files for 30 days or longer. Postgres does not do that. I need a bash script, that's what I'm trying to write. But I'm getting erros as you can see above. I'm already using the command you provided. Just the FIND does not solve my problem. I need that pg_archivecleanup does that job.
• jjanes: So, what does my example do that you don't want done, or not do that you do want done? We can't reverse engineer what you want out of your examples, because your examples don't do what you want.
• Patrick B.: the find command you provided will just delete the files older than X days... but first, I need to keep the files... I have to keep the files for 30 days and then delete the older ones.

Answer 3

This has been solved.

recovery.conf:

archive_cleanup_command = 'exec /var/lib/pgsql/bin/pg_archivecleaup_mv.bash'

Final pg_archivecleanup script:

#!/bin/bash
declare -r -x PATH='/usr/local/bin:/usr/bin:/bin';
ARCHIVEDIR='/var/lib/pgsql/9.2/wal_archive'
LAST_BACKUP=$(ls -lto ${ARCHIVEDIR})
cd $ARCHIVEDIR
exec "/usr/pgsql-9.2/bin/pg_archivecleanup" -d /var/lib/pgsql/9.2/wal_archive ${LAST_BACKUP} | find $ARCHIVEDIR -type f -mmin +30 -delete

The scripts works fine. However, I'm getting an error: /usr/pgsql-9.2/bin/pg_archivecleanup: invalid option -- 'r'

Anyway, I'll try to find that issue and then I edit this answer. But it's working.