Re: PITR Recovery Question - Mailing list pgsql-admin

On Jun 4, 2010, at 13:54 , Gnanakumar wrote:
> In case, if I decide to clean the old WAL archives and set right PITR from
> today onwards by taking base backup, so that I can start managing and
> maintaining atleast from now onwards, what is the correct way/method of
> removing files from pg_xlog/, pg_xlog/archive_status/ and
> /mnt/pitr/walarchive/ directories?

You should *never*, I repeat, *never* remove files from pg_xlog manually unless you know *exactly* what you're doing!

If you remove those, and postgres crashes, you *will* lose your whole database!

About the same holds for pg_xlog/archive_status. Removing files from there won't prevent postgres from starting, but it
*will*interfere with the WAL archiving process, and may lead to unusable base backups! 

Postgres will recycle old WAL segments automatically, once they're no longer needed for crash recovery *and* after
they'vebeen archived successfully (if archiving is enabled). Once a WAL segment is recycled, it's corresponding
archive_status/file(s) will be removed too. 

The correct way to clean out pg_xlog therefore is to either disable WAL archiving, or to make sure your archive_command
succeedseventually. 

A common way to use PITR is the following.

1) You setup an archive_command.
2) You take a base backup. That also triggers the creation of a backup history file (<number1>.<number2>.backup) in the
archivedirectory. 
3) You remove old base backups. You probably want to keep more than one, though, just in case. How long you retain your
basebackups determines 
how far back in time you'll be able to go in the event of a disaster. The furthest point you can go back to is the time
pg_stop_backup()was called while taking the oldest base backup you retained.  
4) You remove all WAL segments that predate the remaining base backups. For that, you find the backup history file in
thearchive directory that corresponds to the oldest remaining base backup and then remove all WAL segments whose name
isnumerically smaller than the <number1> from that backup history file. Keeping older WAL segments buys you nothing -
WALfiles without a base backup that *predates* them are worthless. 
5) Your archive directory will now slowly grow. At some point the cost of storing all those segments will out-weight
thecost of taking a new base backup. Whether that happens after an hour, a day, a week or a year depends on the size of
yourdatabase vs. the amount of modifications it receives. However, at some point or another you will reach that cutoff
point,and that is when you continue with (2). 

http://www.postgresql.org/docs/8.2/static/continuous-archiving.html explains all of this pretty exhaustively.

best regards,
Florian Pflug