Hi,
I'll try to answer your questions below, but in the future please post questions concerning the usage and
administrationof 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_commandfails due to the disk being full, they remain in pg_xlog. Once you enlarge the filesystem holding the
WALarchive 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.
Youcan 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
executionin 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
wantto recover to. If some of those WAL segments still reside in pg_xlog, you'll either need to teach your
restore_commandto fetch them from there. Note that you cannot recover "in reverse". To recover up to a certain point in
timeyou always need to start from a base backup taken *before* that time.
best regards,
Florian Pflug