Re: PITR Phase 2 - Design Planning - Mailing list pgsql-hackers

On Tue, 2004-04-27 at 08:56, Hans-Jürgen Schönig wrote:
> Simon Riggs wrote:
> > Since Phase1 is functioning and should hopefully soon complete, we can
> > now start thinking about Phase 2: full recovery to a point-in-time.
> >
> > Previous thinking was that a command line switch would be used to
> > specify recover to a given point in time, rather than the default, which
> > will be recover all the way to end of (available) xlogs.
> >
> > Recovering to a specific point in time forces us to consider what the
> > granularity is of time.
> > We could recover:
> > 1.to end of a full transaction log file
> > 2.to end of a full transaction
> >
> > Transaction log files currently have timestamps, so that is
> > straightforward, but probably not the best we can do. We would
> > rollforward until the xlog file time > desired point in time.
> >
> > To make (2) work we would have to have a timestamp associated with each
> > transaction.

...

> > So, I propose:
> >
> > - appending 8 byte date/time data into xlog file header record
> > - appending 4 bytes of time offset onto each xlog record
> > - altering the recovery logic to compare the calculated time of each
> > xlog record (file header + offset) against the desired point-in-time,
> > delivered to it by GUC.
> >
> > Input is sought from anybody with detailed NTP knowledge, since the
> > working of NTP drift correction may have some subtle interplay with this
> > proposal.
> >
> >
> I have one question which might be important: If we use timestamps
> inside the WAL system to find out where to stop. What happens if
> somebody changes the time of the system? (e.g. correcting the system
> clock by calling ntpdate). Wouldn't it confuse the PITR system? How do
> you plan to handle that? Unfortunately time is nothing which can be used
> as a key (at least not from my point of view).
>

Interesting question.

The timestamps written within WAL would be "just data" once written. The
recovery process would refer only to that timestamp data, so would not
refer to any external notion of time. This is required to ensure that
the same recovery will happen identically, no matter how many times you
choose to re-run it (or are forced to by external circumstances).

If you change the time of the system backwards, this might invalidate
the transaction log history....this would effectively create two (or
more) xlog records with the same timestamp on them and it would be
logically indeterminate which one should limit recovery. In practical
terms, I would implement this as "apply all records <= PIT". This would
mean that recovery would stop only when the time became larger than PIT,
which would only occur at the second (or last) record that had a
timestamp equal to PIT. I guess I could put a WARNING in to say "time
just went backwards...spoohw"

Overall, I'd refer back to the points Bruce raised - you certainly do
need a way of finding out the time to recover to, and as others have
said also, time isn't the only desirable "recovery point".

Best regards, Simon Riggs