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-IIand configured the online recovery with help of rsync and ssh, which is working fine at the moment. My archive
commandis:
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
1GB 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
WALarchives (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
emergencybackup script to prevent the hard drive from filling up. Or with another job in the cron run every week or so.
Ora 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
http://linuxsilo.net/
"Ubi sapientas ibi libertas"