Re: Problem with PITR recovery

I could still use a little clarification. It seems sort of like there is
an extra step, like:

(1) start archiving
(2) pg_start_backup()
(3) copy PGDATA directory with tar
(4) pg_stop_backup()
(5) ??

And the text you have at
http://candle.pha.pa.us/main/writings/pgsql/sgml/backup-online.html

says: "To make use of this backup, you will need to keep around all the
WAL segment files generated during and after the file system backup.".

How long after? Wouldn't you be keeping the WAL segments afterward
anyway by archiving?

I've tested and been able to recover using PITR before, but I'd like a
little clarification on the steps to make absolutely sure that the base
backup I have is viable.

Can you sort of run through the failure case again, and how to prevent
it?

Regards,
Jeff Davis

On Sun, 2005-04-17 at 21:38 -0400, Bruce Momjian wrote:
> Bruce Momjian wrote:
> > I figured that part of the goal of PITR was that you could recover from
> > just the tar backup and archived WAL files --- using the pg_xlog
> > contents is nice, but not something we can require.
> >
> > I understood the last missing WAL log would cause missing information,
> > but not that it would make the tar backup unusable.
> >
> > It would be nice if we could force a new WAL file on pg_stop_backup()
> > and archive the WAL file needed to match the tar file. How hard would
> > that be?
> >
> > I see in the docs:
> >
> > To make use of this backup, you will need to keep around all the WAL
> > segment files generated at or after the starting time of the backup. To
> > aid you in doing this, the pg_stop_backup function creates a backup
> > history file that is immediately stored into the WAL archive area. This
> > file is named after the first WAL segment file that you need to have to
> > make use of the backup. For example, if the starting WAL file is
> > 0000000100001234000055CD the backup history file will be named something
> > like 0000000100001234000055CD.007C9330.backup. (The second part of this
> > file name stands for an exact position within the WAL file, and can
> > ordinarily be ignored.) Once you have safely archived the backup dump
> > file, you can delete all archived WAL segments with names numerically
> > preceding this one.
> >
> > I am not clear on what the "backup dump file" is? I assume it means
> > 0000000100001234000055CD. It is called "WAL segment file" above. I
> > will rename that phrase to match the above terminology. Patch attached
> > and applied.
>
> I found that the docs mention above are inaccurate because they state
> you only need the WAL segment used at the start of the file system
> backup, while you really need all the WAL segments used _during_ the
> backup before you can safely delete the older WAL segments. Here is
> updated text I have applied to HEAD and 8.0.X:
>
> Once you have safely archived the WAL segment files used during the file
> system backup (as specified in the backup history file), you can delete
> all archived WAL segments with names numerically less. Keep in mind that
> only completed WAL segment files are archived, so there will be delay
> between running pg_stop_backup and the archiving of all WAL segment
> files needed to make the file system backup consistent.
>