Subject: Re: PITR Recovery Question
Mime-Version: 1.0 (Apple Message framework v1078)
Content-Type: text/plain; charset=us-ascii
From: Florian Pflug <fgp@phlo.org>
In-Reply-To: <00ca01cb03dc$a9ffe780$fdffb680$@com>
Date: Sat, 5 Jun 2010 02:00:39 +0200
Cc: <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <F993CF5B-1C46-4936-99AB-7ED9AAF7B3F8@phlo.org>
References: <001201cb0321$df429d40$9dc7d7c0$@com>
	<00ca01cb03dc$a9ffe780$fdffb680$@com>
To: <gnanam@zoniac.com>

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.=20
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