Re: PITR potentially broken in 9.2

On 5 December 2012 18:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>>> On 2012-12-05 17:24:42 +0000, Simon Riggs wrote:
>>>> So ISTM that we should make recoveryStopsHere() return false while we
>>>> are inconsistent. Problems solved.
>
>>> I prefer the previous (fixed) behaviour where we error out if we reach a
>>> recovery target before we are consistent:
>
>> I agree. Silently ignoring the user's specification is not good.
>> (I'm not totally sure about ignoring the pause spec, either, but
>> there is no good reason to change the established behavior for
>> the recovery target spec.)
>
> On further thought, it seems like recovery_pause_at_target is rather
> misdesigned anyway, and taking recovery target parameters from
> recovery.conf is an obsolete API that was designed in a world before hot
> standby. What I suggest people really want, if they're trying to
> interactively determine how far to roll forward, is this:
>
> (1) A recovery.conf parameter that specifies "pause when hot standby
> opens up" (that is, as soon as we have consistency).

Agreed.

> (2) A SQL command/function that releases the pause mode *and* specifies
> a new target stop point (ie, an interactive way of setting the recovery
> target parameters). The startup process then rolls forward to that
> target and pauses again.

That was my original patch, IIRC, with different functions for each
target type, plus an advance N records function for use in
debugging/automated testing.

Having a single function would be possible, taking a single TEXT
parameter that we parse just like the recovery.conf, taking
semi-colons as newlines.

> (3) A SQL command/function that releases the pause mode and specifies
> coming up normally, ie not following the archived WAL any further
> (I imagine this would force a timeline switch).
>
> The existing "pause now" function could still fit into this framework;
> but it seems to me to have mighty limited usefulness, considering the
> speed of WAL replay versus human reaction time.

I think that was in there also.

Can't remember why we didn't go for the full API last time. I'll have
another go, in HEAD.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services