Date: Fri, 31 Oct 2008 11:32:42 +0000 (GMT)
From: Jaume Sabater <jsabater@linuxsilo.net>
To: pgsql-admin@postgresql.org
Message-ID: <11283608.1941225452762530.JavaMail.root@zimbra.linuxsilo.net>
In-Reply-To: <490A1668.6000107@otg-nc.com>
Subject: Re: Deleting old archived WAL files
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Chander Ganesan wrote:

> Ahh...typically the term "warm standby" refers to a secondary server
> that is not "hot" (i.e., queryable).  I assumed that you meant you were

You are right. My fault :'(

> So really, when you need to "recover", you can simply remove all the old
> "wals" prior to issuing your pg_start_backup(), since they would not be
> needed after your rsync.

[..]

> Why?  If you haven't saved a backup that you made previously (using
> pg_start_backup()), having archived wal files is useless.  I don't see
> why you just don't arbitrarily remove them immediately.

Aha. Now I understand the situation. So, since pgpool-II issues online recovery in two stages, as you say, I don't really need to do WAL archiving unless there is an online recovery process getting started or happening. Therefore what I will do is modify my base-backup script to, as you suggest, activate WAL archiving.

Now it all makes sense to me:

1. I do pg_start_backup
2. Therefore, from now onwards, I need to archive/store WAL files.
3. I do pg_stop_backup
4. Once the restoration is done, I don't need them anymore, so I can disable WAL archiving and delete the old, just used, already used ones.

Thank you very much for your explanation, Chander, you just made everything make sense.

> Note: If you are using 8.3, you can even just disable the
> archive_command when you aren't doing a recovery...

Yes, this is what I will do. I will look for how to do this on the online documentation (I have read that enabling/disabling WAL archiving requires restart, but enabling/disabling archive_command only needs a reload). I guess I will have to modify the /etc/postgresql/8.3/main/postgresql.conf file from the script, then ask the daemon for a reload. But I'll see if there is another way to do this that does not require altering that configuration file.

> I'm not sure that I agree with your strategy in terms of preserving your
> data in the case of some sort of failure, but if I understand your
> problem correctly, this would serve as a pretty good solution...

This database is almost read only, with scheduled updates of the contents. Therefore nightly pg_dumps are good enough (sufficient) as a backup strategy.

> As a side note, I'd recommend you read up on the standard issues that
> apply when using PgPool-II in multi-master replication mode with query
> balancing (if you aren't already aware of them) :-)  ...

Yes, I believe I have. I guess you mean not being able to use CURRENT_TIMESTAMP or SERIAL in queries and stuff like that. We use the database as a data storage, all business logic remains in .NET classes or in Java classes, therefore I am pretty much sure that I won't have problems. I am still in the process of checking the code in development with the programmers so that there is no query not abiding by the rules stated in the pgpool-II readme.

Thanks again for your help! It is very much appreciated.

-- 
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"