Re: Deleting old archived WAL files

Jaume Sabater wrote:
> Hello everyone!
>
> I am trying to setup a warm standby cluster with pgpool-II and PostgreSQL 8.3 on Debian Etch (with backported packages), with replication and load balancing. I have enabled WAL archiving in both PostgreSQL instances as required by pgpool-II and configured the online recovery with help of rsync and ssh, which is working fine at the moment. My archive command is:
>
> archive_command = '/bin/cp %p /var/lib/postgresql/pg_xlog_archive/%f'
>
> Now it turns out that, with just a few tests using pgbench, my /var/lib/postgresql/pg_xlog_archive directory is almost 1 GB big, and this is just a computer running two Xen instances for testing and such.
>
> So I have been investigating, reading online docs and googling a lot, and I believe that, in order to get rid of old WAL archives (and to be able to actually call those "old" ones), I should be doing something like this:
>
> 1. Once every now and then, depending on how big that directory grows, I should run pg_start_backup('label') and pg_stop_backup.
>
> 2. Note the created .backup file inside /var/lib/postgresql/pg_xlog_archive and delete all archived WAL files "older" ("lesser" than, alphabetically speaking) than the one referenced in the "START WAL LOCATION" label inside that .backup file.
>
> I could do this from a job in the cron, triggered every hour, that would check the size of the folder and run an emergency backup script to prevent the hard drive from filling up. Or with another job in the cron run every week or so. Or a combination of both.
>
> Would this be a good procedure? I have not been able to find any information or examples on how to do with old WAL backups. Everyone seems to be archiving those until the day of the armageddon...
>
If you are running warm-standby, its presumable that your standby server
is "consuming" these files as they are being generated. In such a case,
you can set "log_restartpoints" in your recovery.conf file, and use
pg_standby with the '%r' (restartwalfile) parameter so that it can
"prune" old WAL files when necessary. In such a case, you wouldn't need
to do any pruning yourself, since pg_standby would do it for you, when
the standby server indicates that it is "safe" to remove those old files.

log_restartpoints='true'
restore_command='pg_standby /archive_dir %f %p %r'

In short, your system that is in "recovery mode" can decide which ones
it needs to get rid of, once it knows it no longer needs them.

Note: I believe the log_restartpoints option (and %r argument) were
added in 8.3 .

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Expert PostgreSQL, PostGIS, and other training delivered worldwide.