Re: 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