Re: Incremental backup

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 <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC