Re: PITR Recovery Question - Mailing list pgsql-hackers

Hi Florian,

Sure.  I'll post my future questions either in pgsql-general or pgsql-admin.

> If some of those WAL segments still reside in pg_xlog, you'll either need
to teach your restore_command to fetch them from there. Note that you cannot
recover "in reverse".

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

If my normal restore command is: restore_command='cp
"/mnt/pitr/walarchive/%f "%p"', how should I instruct restore command to
fetch?  Should I just replace this with something like restore_command='cp
/usr/local/pgsql/data/pg_xlog/%f "%p"'.  Also you have mentioned that we
cannot recover "in reverse", what I understand from this is that even though
if I replace the restore command pointing to pg_xlog/ directory, this will
not work out in this situation?  Is my understanding right?

-----Original Message-----
From: Florian Pflug [mailto:fgp@phlo.org] 
Sent: Thursday, June 03, 2010 8:50 PM
To: gnanam@zoniac.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] PITR Recovery Question

Hi,

I'll try to answer your questions below, but in the future please post
questions concerning the usage and administration of postgres to
pgsql-general or pgsql-admin. This list focus is the development of new
features and bugfixes.

On Jun 3, 2010, at 15:37 , Gnanakumar wrote:
> 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.

This is by design. WAL logs are only removed from pg_xlog once they have
been archived successfully. Since your archive_command fails due to the disk
being full, they remain in pg_xlog. Once you enlarge the filesystem holding
the WAL archive they should be copied and subsequently removed from pg_xlog.

Note that you'd usually take a new base backup once in a while to limit the
number of WAL segments you need to retain. You can take a base backup while
postgres is running by issuing pg_start_backup() before you start the copy
and pg_stop_backup() after it finished. Apart from creating additional IO
load, doing so won't interfere with normal query execution in any way.

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

For PITR, you'll obviously need the WAL segment starting from the time your
base backup started up until the point you want to recover to. If some of
those WAL segments still reside in pg_xlog, you'll either need to teach your
restore_command to fetch them from there. Note that you cannot recover "in
reverse". To recover up to a certain point in time you always need to start
from a base backup taken *before* that time.

best regards,
Florian Pflug