Re: [HACKERS] Re: BUG #4796: Recovery followed by backup creates unrecoverable WAL-file

Simon Riggs wrote:
> On Fri, 2009-05-15 at 20:38 +0900, Fujii Masao wrote:
>
>> On Fri, May 15, 2009 at 8:20 PM, Heikki Linnakangas
>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> The probe in findNewestTimeLine() initialized to recovery target timeline +
>>> 1. It doesn't require history files for any old timelines to be present.
>> What if recovery_target_timeline = 'latest'? The unexpected (not latest)
>> recovery target timeline might be chosen when some timeline history
>> files don't exist.
>>
>>> The
>>> purpose of findNewestTimeLine() is to ensure that if you e.g recover to a
>>> point in time in timeline 5, and there's already WAL files for timelines 6
>>> and 7 in the archive, we pick a unique timeline id.
>> When only the history file for timeline 6 is deleted, timeline 6 would be
>> assigned as the newest one *again* at the end of archive recovery.
>> Is this safe?
>
> Yeh, those cases screw us up. I'm sure we can think of others, I had
> time to analyse things in more detail. I'd be happier with the general
> assessment that "it's unsafe to keep history files in the archive".
>
> My suggestion is that we keep history files in a new directory under the
> data directory. That way they get copied as part of the base backup,
> rather than sent off to the archive where DBAs can have mad moments and
> delete all, or worse, just some of them. Implementation for this
> proposal is really easy and safe for where we are now: we just access
> the appropriate local directory. Call it pg_history or pg_timeline etc..
> Not under pg_xlog!
>
> There is no particular reason to send history files to the archive,
> since new ones are only ever generated at the end of an archive
> recovery.

Consider this:

1. Take base backup, on timeline 1. Archive to directory X
2. Disaster.
3. restore from base backup and the archive. Timeline ID is incremented
to 2. Keep archiving to directory X.
4. Another disaster.
5. Restore again from the base backup and archive. Timeline ID is
incremented to 3.

If the history files are not in the archive, where is the restore at
step 5 going to get the history file for timeline 2? You certainly need
the history files in the archive.

The history files should be considered as part of the WAL data. They
need to be archived together with the WAL segments. When you take a new
base backup, you no longer need the history files for old timelines,
just like you don't need old WAL.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com