On Fri, 14 Feb 2003, Kevin Brown 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.
You're right about what rollback segments are; they hold the information about previous versions of a row, so that if a transaction is rolled back, the previous data can be restored. (Postgres doesn't need this, since it doesn't update the rows in place: the old copy of the row stays as and where it is, and a new copy is made with the new data. [The old copy can later be removed, after no transactions reference it any more, with a VACUUM.]) Oracle9i has a new feature called a "flashback query," which uses the information in the rollback segements to let you query the database in a previous state. (I.e., "select such and such from this table as of two hours ago.") Postgres could do this using the older copies of rows as well, though the performance often wouldn't be pretty, since your indexes become useless, I believe. (Don't they point to only the latest copy of a row?) Still, it would be cool and life-saving in some situations. But yeah, PITR takes a snapshot and goes in a forward direction, not a backwards one. This is just what Oracle does, too, using the redo logs. > 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. Well, I'm not sure that you'd really need to have any special archiving facilities: you just need to let the old files sit there, and keep creating new ones. You do lose a bit of performance in that you can't recycle log segments, but you could always just fork off a (well niced) process to create a new, empty log segment at the time you start in on the last pre-created one, so that you will have another pre-created one ready when you finish the current one. BTW, why exactly do we pre-create log segments, anyway? I see this comment in backend/access/transam/xlog.c: /* * Zero-fill the file. We have to do this the hard way to ensure that * all the file space has really been allocated --- on platforms that * allow "holes" in files, just seeking to the end doesn't allocate * intermediate space. This way, we know that we have all the space * and (after the fsync below) that all the indirect blocks are down * on disk. Therefore, fdatasync(2) or O_DSYNC will be sufficient to * sync future writes to the log file. */ This seems to imply to me that fdatasync will, when synchronizing the data blocks of a file, not necessarially synchronize the indirect blocks, which seems a little...odd. It's not like there's much point in writing the data to the disk if you can't get to it. I'd understood fdatasync as just a way to avoid updaing the inode's last changed date. Are there OSes that implement fdatasync in a way that you could still lose data? > 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... I don't think most people are thinking of that when they think of PITR; I think they're thinking of applying changes from a log to a previous version of a database. And you can't do such a rollback at all, except on an entire database, because of the potential integrity violations. The best you could do would be to generate SQL for the changes you'd need to get back to the previous point, and see if you can execute these changes. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])