Re: PITR Phase 2 - Design Planning

Simon Riggs wrote:
> 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. This could be in one of two places:
> 1. the transaction record in the clog
> 2. the log record in the xlog
> We would then recover the xlog record by record, until we found a record
> that had a timestamp > desired point-in-time.
>
> Currently, neither of these places have a timestamp. Hmmmm. We can't use
> pg_control because we are assuming that it needs recovery...
>
> I can't see any general way of adding a timestamp in any less than 2
> bytes. We don't need a timezone. The timestamp could refer to a number
> of seconds since last checkpoint; since this is limited already by a GUC
> to force checkpoints every so often. Although code avoids a checkpoint
> if no updates have taken place, we wouldn't be too remiss to use a
> forced checkpoint every 32,000 seconds (9 hours).
> Assuming that accuracy of the point-in-time was of the order of
> seconds?? If we went to 0.1 second accuracy, we could checkpoint (force)
> every 40 minutes or so. All of that seems too restrictive.
> If we went to milliseconds, then we could use a 4 byte value and use a
> checkpoint (force) every 284 hours or 1.5 weeks.
> Thoughts?

I was thinking --- how would someone know the time to use for restore?
Certainly they will not know subsecond accuracy? Probably second-level
accuracty is enough, _except_ when they want everything restored up to a
DROP TABLE transaction or some major problem. Is there a way to give
users a list of transactions on a log backup? Can we show them the
username, database, or commands or something? Would they be able to
restore up to a specific transaction in that case?

Basically, we could give them sub-second recovery, but what value would
it be?

--
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