Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 1CC746323FF
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat,  5 Jun 2010 04:05:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 90606-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat,  5 Jun 2010 07:05:49 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id B15556321A7
	for <pgsql-admin@postgresql.org>; Sat,  5 Jun 2010 04:05:49 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o5575cAK020069;
	Sat, 5 Jun 2010 02:05:39 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Florian Pflug'" <fgp@phlo.org>
Cc: <pgsql-admin@postgresql.org>
References: <001201cb0321$df429d40$9dc7d7c0$@com>
	<00ca01cb03dc$a9ffe780$fdffb680$@com>
	<F993CF5B-1C46-4936-99AB-7ED9AAF7B3F8@phlo.org>
In-Reply-To: <F993CF5B-1C46-4936-99AB-7ED9AAF7B3F8@phlo.org>
Subject: Re: PITR Recovery Question
Date: Sat, 5 Jun 2010 12:35:37 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00cc01cb047d$82079830$8616c890$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcsEQieqU2dLv4KsRKmkZOazkUf1GQALYxjw
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.1 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201006/54
X-Sequence-Number: 34832

Hi Florian,

Thanks for your valuable suggestion and a detailed step on common way to use
PITR.  Things are very clear now except that I've some other question in
connection to this.

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

Probably I would go with the 2nd option, that is allowing archive command to
run successfully until things are completely clear.

But this question is for my understanding:  In case if I decide to go with
1st option, that is disable WAL archiving for a while, will it completely
clean out files from pg_xlog/ and pg_xlog/archive_status/ directories, so
that I can start the PITR by taking base backup by enabling WAL archiving
later?

> A common way to use PITR is the following.

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

Can you share with me any automated shell script that takes care of this
removal automatically?  Or can you share any systematic way (steps) of doing
things if I want to do this manually?

Regards,
Gnanam