Thread: Online Backups with 8.0 -- Confused
Greetings,
I am having some trouble figuring out exactly what I'm supposed to
be doing to successfully recover from an online backup. I am not in a
critical situation or anything, simply doing some testing to make sure
that my backup system would work. Here's the problem:
I do a 'SELECT pg_start_backup('backup1');', it gives me the usual
successful result. I then backup (via rsync) the data directory, in
this case /usr/local/pgsql/data. This goes fine. I then do a
pg_stop_backup(); and backup my archived WAL directory. Here's where
the problem seems to occur.
Should all of the WAL files necessary for restoration not be in the
archive directory after a pg_stop_backup();? It does not appear that
this is the case, because if I try to restore a database backed up via
the method above, I receive the following errors:
cp: /usr/local/pgsql/archive/00000001.history: No such file or directory
cp: /usr/local/pgsql/archive/000000010000000000000022: No such file or directory
PANIC: WAL ends before end time of backup dump
Abort trap (core dumped)
Now, realize that ..0022 was not in the archive directory, and
actually is still not. However, it seems to be requiring it. Do I
have to wait for this log to be archived? Is there a way that I can
force the archival of this WAL log?
Any help would be greatly appreciated.
Thanks!
-JD-
On Thu, 2005-03-31 at 18:41 -0500, Jason DiCioccio wrote: > Now, realize that ..0022 was not in the archive directory, and > actually is still not. However, it seems to be requiring it. Do I > have to wait for this log to be archived? Yes. > Is there a way that I can > force the archival of this WAL log? No, other than write lots of stuff to a table. Straight answers, but I hope that helps. In retrospect, there is a slight timing error between end backup and archival of next WAL log. The first-release design for PITR didn't fully cover lower traffic situations, so in those cases the timing error becomes noticeable. You're not the first to observe this, though only by about a month. I'm considering options for the next release, since major change seems to be required to the basic xlog handling mechanisms. Best Regards, Simon Riggs
Simon, On Mar 31, 2005 7:29 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > <snip> > In retrospect, there is a slight timing error between end backup and > archival of next WAL log. The first-release design for PITR didn't fully > cover lower traffic situations, so in those cases the timing error > becomes noticeable. You're not the first to observe this, though only by > about a month. > > I'm considering options for the next release, since major change seems > to be required to the basic xlog handling mechanisms. Thank you very much for the info. Given the situation then, how would you recommend doing what I'm trying to do? Or what would be a good method of doing these online backups? Would doing 1 base backup a day, keeping a couple of days worth, and backing up the archive logs as the come in be the best way? I guess I was just trying to do the base and WAL backup at the same time and all at once, which is probably not what it's designed for, as I'm not figuring out. Thanks! -JD-
On Thu, 2005-03-31 at 23:46 -0500, Jason DiCioccio wrote: > Simon, > > On Mar 31, 2005 7:29 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > <snip> > > In retrospect, there is a slight timing error between end backup and > > archival of next WAL log. The first-release design for PITR didn't fully > > cover lower traffic situations, so in those cases the timing error > > becomes noticeable. You're not the first to observe this, though only by > > about a month. > > > > I'm considering options for the next release, since major change seems > > to be required to the basic xlog handling mechanisms. > > Thank you very much for the info. Given the situation then, how would > you recommend doing what I'm trying to do? Or what would be a good > method of doing these online backups? > > Would doing 1 base backup a day, keeping a couple of days worth, and > backing up the archive logs as the come in be the best way? Sounds good. Many sites keep more than a couple of copies: last 3 days, last week and last month. Best Regards, Simon Riggs
On Fri, 2005-04-01 at 01:29 +0100, Simon Riggs wrote: > I'm considering options for the next release, since major change seems > to be required to the basic xlog handling mechanisms. Hmmm. I've thought about what to do here, I'll detail a proposal on HACKERS over next few days. If I'm right it will be more minor change than I'd guessed at. Best Regards, Simon Riggs
Simon Riggs wrote: >On Thu, 2005-03-31 at 18:41 -0500, Jason DiCioccio wrote: > > > >> Now, realize that ..0022 was not in the archive directory, and >>actually is still not. However, it seems to be requiring it. Do I >>have to wait for this log to be archived? >> >> > >Yes. > > > >>Is there a way that I can >>force the archival of this WAL log? >> >> > >No, other than write lots of stuff to a table. > >Straight answers, but I hope that helps. > >In retrospect, there is a slight timing error between end backup and >archival of next WAL log. The first-release design for PITR didn't fully >cover lower traffic situations, so in those cases the timing error >becomes noticeable. You're not the first to observe this, though only by >about a month. > >I'm considering options for the next release, since major change seems >to be required to the basic xlog handling mechanisms. > If postgres is trying to read an archive log file which was not yet generated, why cant it read from the transaction log ? I mean the current last transaction log before you start the restoration process, wouldnt that work ? I tried restore couple of time on postgres 8 it didnt give me this error, can anyone tell me how to reproduce this error, we are planning to move to 8.0 soon and I will be using online backups . > >Best Regards, Simon Riggs > > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > >