Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: <pgsql-admin@postgresql.org>
Cc: <fgp@phlo.org>
References: <001201cb0321$df429d40$9dc7d7c0$@com>
In-Reply-To: <001201cb0321$df429d40$9dc7d7c0$@com>
Subject: PITR Recovery Question
Date: Fri, 4 Jun 2010 17:24:14 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00ca01cb03dc$a9ffe780$fdffb680$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcsDIdmsbIADJ47YRMav4sbU9E4TXQAt16kg
Content-Language: en-us

Hi Florian,

I'm moving this discussion to pgsql-admin.  To give a picture of my original
question, it is given below, so that other users in this mailing list will
understand my original problem statement.

> If you point it at a cluster's own pg_xlog directory, it won't work.

> You might want to re-ead the section on the recovery process in the PTITR
documentation, at
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BAC
KUP-PITR-RECOVERY

OK.

My pg_xlog/ and walarchive/ directory locations are
"/usr/local/pgsql/data/pg_xlog" and "/mnt/pitr/walarchive" respectively.

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?

1. How do I clean up WAL archives that are accumulated in pg_xlog/
directory?  Does it harm the database in anyway if I remove WAL segments
manually?
2. Few important command outputs:

[root@dbserver data]# pwd
/usr/local/pgsql/data
[root@dbserver data]# ls -l pg_xlog |wc -l
14438
[root@dbserver data]# ls -l pg_xlog/archive_status |wc -l
14436
[root@dbserver data]# ls -l /mnt/zoniacpitr/walarchive | wc -l
1783

NOTE: I can see all the files under pg_xlog/archive_status/ directory having
suffixed with "00*.ready".

-----Original Message-----
From: Gnanakumar [mailto:gnanam@zoniac.com] 
Sent: Thursday, June 03, 2010 7:07 PM
To: pgsql-hackers@postgresql.org
Subject: PITR Recovery Question

Hi,

My production server is running PostgreSQL v8.2.3 on CentOS release 5.2
(Final).

I've setup PITR in my production server.  For some reason, after setting up
PITR, we're not able to manage and maintain it.  Because of this our WAL
archive drive become full (100% use) approximately after 1 month.

PITR SETUP DETAILS
We've 2 drives.  Primary drive (pgsql/data/ directory resides) is 400 GB and
secondary drive (WAL archive) is 30 GB.  All WAL archives are written to
secondary drive.

Base backup taken on: Aug03, 2009
WAL archive drive become full (100% use) on: Sep05, 2009

Because this WAL archive drive has become full, all WAL archive segments to
be archived are accumulated into pg_xlog/ directory itself.  Eventually, 9
months (as of today from Sep05, 2009) of WAL archives are residing in
pg_xlog/ directory.

My question is, in case if I would like to perform recovery process as it is
in this situation, will this work out?  That is, I'm seeing/finding out
whether recovery process would perform successfully anywhere between the
date range Aug03, 2009 (my base backup date) and as of today - Jun03, 2009.
Reason I'm asking this is still all my WAL archives are residing in pg_xlog/
directory.

Experts advice/idea/suggestion on this appreciated.

Regards,
Gnanam