Kevin, Hi. I was looking into PITR for PostgreSQL myself about a year back but life intervened. I am an Oracle DBA so may be able to help you with an understanding of how Oracle does this.
You wrote: > Oracle has something they call "rollback segments" which I assume are > separate bits of data that have enough information to reverse changes > that were made to the database during a transaction, and I figured > PITR would (or could) apply particular saved rollback segments to the > current state in order to "roll back" a table, tablespace, or database > to the state it was in at a particular point in time. > > As it is, it sounds like PITR is a bit less refined than I expected. Actually Oracle uses its redo logs (like PostgreSQL' WAL) and archivelogs (copies of old redo logs) rather than rollback segments. The way it works, more or less, is that you start from a hot backup, and roll-forward using archivelogs and redo logs to the point in time to which you wish to recover. The whole point of this is not so much that we can restore our database to some point in the past, as that we can recover to just before some disaster struck, from a starting point of a previous hot backup. In fact, the whole PITR thing for Oracle seems to be simply an enabling technology for hot backups. This seems to me to be one of the killer enterprise features that PostgreSQL currently lacks. > So the relevant question is: how is *our* PITR going to work? In > particular, how is it going to interact with our WAL files and the > table store? If I'm not mistaken, right now (well, as of 7.2 anyway) > we round robin through a fixed set of WAL files. For PITR, I assume > we'd need an archivelog function that would copy the WAL files as > they're checkpointed to some other location (with destination names > that reflect their order in time), just for starters. I believe that three things are needed: 1) filesystem-based hot backups. It may be possible to simply copy the database files even though they would be open. Oracle places each tablespace into a hot-backup mode prior to taking the copy. In this mode, updates to the files are queued-up to be applied once the tablespace is taken out of hot-backup mode. This all seems quite tricky and will slow the database down. 2) Auto-archiving of WAL files. Just as you suggest. 3) A recovery controller that can figure out the state of the restored database, and manage the reapplication of archived and current WAL files. > > It'd be *awfully* nice if you could issue a command to roll a table > (or, perhaps, a tablespace, if you've got a bunch of foreign keys and > such) back to a particular point in time, from the command line, with > no significant advance preparation (so long as the required files are > still around, and if they're not then abort the operation with the > appropriate error message). But it doesn't sound like that's what > we're talking about when we talk about PITR... Nice but lots of work for questionable benefit. If your application needs to keep track of history there are better ways to do it. If not, then PITR should be seen as an exceptional circumstance and so need not be so easy to manage. > I wouldn't expect the O***** docs to be particularly revealing about > how the database manages PITR at the file level, but if it does, would > you happen to know where so I can look at it? What I've seen so far > is very basic and not very revealing at all... The best Oracle documentation on this is available through Oracle technet (technet.oracle.com) for which you will have to need to register. Look for documentation->Oracle 9i Documentation->list of books->Backup and Recovery Concepts. -- Marc [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
