Re: Deleting old archived WAL files

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
reallyneed to do WAL archiving unless there is an online recovery process getting started or happening. Therefore what
Iwill 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/disablingWAL archiving requires restart, but enabling/disabling archive_command only needs a reload). I guess
Iwill 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
ampretty much sure that I won't have problems. I am still in the process of checking the code in development with the
programmersso 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"