Thread: restoring wal archive and pg_xlog dir

restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
When reading the docs on recovery.conf, I noticed this:

  WAL segments that cannot be found in the archive will be sought in pg_xlog/;
this allows use of recent un-archived segments. However segments that are
available from the archive will be used in preference to files in pg_xlog/. The
system will not overwrite the existing contents of pg_xlog/ when retrieving
archived files.

If the system will use the files in the archive in preference to the ones in
pg_xlog, how can this actually happen if it will not overwrite the contents of
pg_xlog?

I also noticed that if there is not at least one wal archive available in
the archive or the pg_xlog dir, the restore errors out and exits.  So the
base backup is really not complete without at least one wal archive
following it.  Is this by design?


--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: restoring wal archive and pg_xlog dir

From
Tom Lane
Date:
Jeff Frost <jeff@frostconsultingllc.com> writes:
> If the system will use the files in the archive in preference to the ones in
> pg_xlog, how can this actually happen if it will not overwrite the contents of
> pg_xlog?

Segment files pulled from the archive are saved using temporary file
names (and then deleted after being replayed).  For obvious safety
reasons, we try never to overwrite any xlog file in either the archive
or local storage.

> I also noticed that if there is not at least one wal archive available in
> the archive or the pg_xlog dir, the restore errors out and exits.  So the
> base backup is really not complete without at least one wal archive
> following it.  Is this by design?

Certainly.  If you don't have wal data spanning the entire time that the
base backup is being taken, the backup is useless because there's no way
to make it consistent.

            regards, tom lane

Re: restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
On Thu, 23 Jun 2005, Tom Lane wrote:

> Segment files pulled from the archive are saved using temporary file
> names (and then deleted after being replayed).  For obvious safety
> reasons, we try never to overwrite any xlog file in either the archive
> or local storage.

So, that, immediately begs the question as to why the docs indicate we should
clear out all the files in the pg_xlog directory before beginning the restore?
Wouldn't it be better to keep them in place?  Or am I missing a big picture
item.

>> I also noticed that if there is not at least one wal archive available in
>> the archive or the pg_xlog dir, the restore errors out and exits.  So the
>> base backup is really not complete without at least one wal archive
>> following it.  Is this by design?
>
> Certainly.  If you don't have wal data spanning the entire time that the
> base backup is being taken, the backup is useless because there's no way
> to make it consistent.

Ohhhh...I guess I misunderstood the meaning of base backup.  So, what I really
need if my .backup file is named: 000000010000000500000051.00FB6C60.backup,
then I need at least the following to do a restore:

pg_xlog/000000010000000500000051
PGDATA base backup

and then any archived wal files that come after 51?

The background on this is that I'm trying to script up a one script restore
solution and my testing has me running into the occassional surprise.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: restoring wal archive and pg_xlog dir

From
Tom Lane
Date:
Jeff Frost <jeff@frostconsultingllc.com> writes:
> So, that, immediately begs the question as to why the docs indicate we
> should clear out all the files in the pg_xlog directory before
> beginning the restore?  Wouldn't it be better to keep them in place?

IIRC, the docs recommend getting rid of any xlog files that were copied
as part of your base backup: they are either too old to be interesting
(if finished before the base backup started) or not to be trusted
because they might be incomplete (if they were actively being written
while the base backup proceeded).  You have to get this range of data
from your archive in order to trust it.

The reason we avoid overwriting data automatically is that we don't know
what is in your local pg_xlog exactly; there might be xlog files that
are newer than the base backup and potentially contain data not
available anywhere else (if for some reason they never got copied to the
archive area).  So prudence dictates not wiping them out.

> Ohhhh...I guess I misunderstood the meaning of base backup.  So, what I really
> need if my .backup file is named: 000000010000000500000051.00FB6C60.backup,
> then I need at least the following to do a restore:

> pg_xlog/000000010000000500000051
> PGDATA base backup

> and then any archived wal files that come after 51?

You got it.

            regards, tom lane

Re: restoring wal archive and pg_xlog dir

From
Simon Riggs
Date:
On Thu, 2005-06-23 at 10:55 -0700, Jeff Frost wrote:
> When reading the docs on recovery.conf, I noticed this:
>
>   WAL segments that cannot be found in the archive will be sought in pg_xlog/;
> this allows use of recent un-archived segments. However segments that are
> available from the archive will be used in preference to files in pg_xlog/. The
> system will not overwrite the existing contents of pg_xlog/ when retrieving
> archived files.
>
> If the system will use the files in the archive in preference to the ones in
> pg_xlog, how can this actually happen if it will not overwrite the contents of
> pg_xlog?

That was the bit I thought of. The files are streamed in one by one using a temp filename, so you never run out of
spaceno matter how big the archive of transaction logs. Thats an important feature if a base backup goes bad and you
haveto go back to n-1 base backup. 

> I also noticed that if there is not at least one wal archive available in
> the archive or the pg_xlog dir, the restore errors out and exits.  So the
> base backup is really not complete without at least one wal archive
> following it.  Is this by design?

That was the bit I didn't. Fix partially implemented and I am to submit
for 8.1, godspeed my typing fingers. There's a simple workaround if you
have a low transaction rate system: load up enough data to trip into the
next xlog.

Best Regards, Simon Riggs


Re: restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
On Thu, 23 Jun 2005, Simon Riggs wrote:

>> I also noticed that if there is not at least one wal archive available in
>> the archive or the pg_xlog dir, the restore errors out and exits.  So the
>> base backup is really not complete without at least one wal archive
>> following it.  Is this by design?
>
> That was the bit I didn't. Fix partially implemented and I am to submit
> for 8.1, godspeed my typing fingers. There's a simple workaround if you
> have a low transaction rate system: load up enough data to trip into the
> next xlog.

Simon, thanks for the clarification!  What we have is a low transaction rate
system which rolls over about 5 xlogs a day or so.  I'm trying to write a nice
automated script that anyone can run to restore the DB from a pitr archive and
base backup should disaster strike and I'm not around, so I'm attempting to
sort out what exactly that might entail.  It looks like this is what I need to
have a complete restorable data dir:

So, .backup file is named: 000000010000000500000051.00FB6C60.backup, then I
need at least the following to do a proper restore:

* pg_xlog/000000010000000500000051
* PGDATA base backup

and then any archived wal files that come after 51 if none happen before the
next base backup?

Thanks for all the help guys!

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
On Thu, 23 Jun 2005, Simon Riggs wrote:

> That was the bit I thought of. The files are streamed in one by one using a
> temp filename, so you never run out of space no matter how big the archive
> of transaction logs. Thats an important feature if a base backup goes bad
> and you have to go back to n-1 base backup.

I guess I forgot to ask one other question.  If the archived wal files are
streamed in one by one, would it still be safe to leave the pg_xlog directory
intact in the base backup?  What would happen with the out of date log files?
Would they just stay in place or would the postmaster eventually recycle them
after the restore was complete?


--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
On Thu, 23 Jun 2005, Jeff Frost wrote:

> I guess I forgot to ask one other question.  If the archived wal files are
> streamed in one by one, would it still be safe to leave the pg_xlog directory
> intact in the base backup?  What would happen with the out of date log files?
> Would they just stay in place or would the postmaster eventually recycle them
> after the restore was complete?

And here's one other question..is it safe to ignore the: pgsql_tmp directories
when doing the base backup?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: restoring wal archive and pg_xlog dir

From
Bruce Momjian
Date:
Jeff Frost wrote:
> On Thu, 23 Jun 2005, Jeff Frost wrote:
>
> > I guess I forgot to ask one other question.  If the archived wal files are
> > streamed in one by one, would it still be safe to leave the pg_xlog directory
> > intact in the base backup?  What would happen with the out of date log files?
> > Would they just stay in place or would the postmaster eventually recycle them
> > after the restore was complete?
>
> And here's one other question..is it safe to ignore the: pgsql_tmp directories
> when doing the base backup?

Yes, those are used only by running backups, but they should have little
disk space in them.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
On Sat, 25 Jun 2005, Bruce Momjian wrote:

>> And here's one other question..is it safe to ignore the: pgsql_tmp directories
>> when doing the base backup?
>
> Yes, those are used only by running backups, but they should have little
> disk space in them.

Bruce,

Thanks!  I actually ask not because of disk space concerns, but because rsync
occassionally reports that these files disappear out from under the running
rsync process, and so I was thinking I'd rather just exclude them if they are
not required as I want my pitrbasebackup script to return 0 if rsync is
successful.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: restoring wal archive and pg_xlog dir

From
Jeff Frost
Date:
On Thu, 23 Jun 2005, Simon Riggs wrote:

> That was the bit I didn't. Fix partially implemented and I am to submit
> for 8.1, godspeed my typing fingers. There's a simple workaround if you
> have a low transaction rate system: load up enough data to trip into the
> next xlog.

I just noticed one other thing...it will always look in the pg_xlog directory
for that current xlog which is required and will not look for it in the
archive dir.  It would be nice if it would also look in the archive dir for
this one, no?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954