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		marc@bloodnok.com

15 Feb 2003 11:26:32 -0800
Marc Munro <marc@bloodnok.com>
Re: Incremental backup