Re: PITR potentially broken in 9.2

On 2012-12-05 13:48:53 -0500, Tom Lane 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).

> (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.
>
> (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).

That sounds good. The multitude of options for 2) sounds a bit annoying,
but I am not sure where to cut there.

>
> 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 "pause now" is useful for independent purposes. You can use
while operating a normal standby to stop replay for some time if you
need consistent data and then happily resume afterwards (if you have
enough wal stored...).

Greetings,

Andres Freund

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