Skip site navigation (1) Skip section navigation (2)

Deleting old archived WAL files

From: Jaume Sabater <jsabater(at)linuxsilo(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Deleting old archived WAL files
Date: 2008-10-30 16:03:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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...

I would be doing nightly pg_dumps, too.

Thanks in advance.

Jaume Sabater

"Ubi sapientas ibi libertas"


pgsql-admin by date

Next:From: Alvaro HerreraDate: 2008-10-30 16:08:00
Subject: Re: system catalog views + tables
Previous:From: damien clochardDate: 2008-10-30 08:52:18
Subject: Re: system catalog views + tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group