pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Chander Ganesan <chander@otg-nc.com>
To: Jaume Sabater <jsabater@linuxsilo.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Deleting old archived WAL files
Date: Thu, 30 Oct 2008 12:46:05 -0400
Message-ID: <4909E4CD.70703@otg-nc.com> (raw)
In-Reply-To: <31580567.1881225382593510.JavaMail.root@zimbra.linuxsilo.net>
References: <31580567.1881225382593510.JavaMail.root@zimbra.linuxsilo.net>
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.
view thread (8+ messages) latest in thread
Message-ID: <4909E4CD.70703@otg-nc.com> Permalink: ../4909E4CD.70703@otg-nc.com/ Also on: postgresql.org/message-id/4909E4CD.70703@otg-nc.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-admin@postgresql.org Cc: chander@otg-nc.com, jsabater@linuxsilo.net Subject: Re: Deleting old archived WAL files In-Reply-To: <4909E4CD.70703@otg-nc.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox