Thread: restoring wal archive and pg_xlog dir
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
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
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
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
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
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
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
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
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
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
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