Re: PITR Recovery Question

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've been 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 succeeds eventually.

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 archive directory.
3) You remove old base backups. You probably want to keep more than one, though, just in case. How long you retain your base backups 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 the archive directory that corresponds to the oldest remaining base backup and then remove all WAL segments whose name is numerically smaller than the <number1> from that backup history file. Keeping older WAL segments buys you nothing - WAL files 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 the cost of taking a new base backup. Whether that happens after an hour, a day, a week or a year depends on the size of your database 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