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.
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 | | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Search Discussions

Discussion Posts


Follow ups

Related Discussions



site design / logo © 2021 Grokbase