Re: Problem with PITR recovery

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.

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