Thread: Online Backups with 8.0 -- Confused

Online Backups with 8.0 -- Confused

From
Jason DiCioccio
Date:
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-

Re: Online Backups with 8.0 -- Confused

From
Simon Riggs
Date:
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


Re: Online Backups with 8.0 -- Confused

From
Jason DiCioccio
Date:
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-

Re: Online Backups with 8.0 -- Confused

From
Simon Riggs
Date:
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


Re: Online Backups with 8.0 -- Confused

From
Simon Riggs
Date:
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


Re: Online Backups with 8.0 -- Confused

From
Pallav Kalva
Date:
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
>
>
>