Re: Incremental backup

Curt Sampson <cjs(at)cynic(dot)net> writes:
> 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,

Yeah, good ol' time-travel. This was built into Postgres in Berkeley
days, and later ripped out for performance reasons.

> 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?)

No. If they did, they'd not work under MVCC.

> BTW, why exactly do we pre-create log segments, anyway?

Partly because the logic is designed to work in the PITR case, but
mostly because we don't want to suffer an out-of-disk-space condition
while we are in the midst of using a log segment.

> 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.

The comment is being paranoid about whether fdatasync is correctly
implemented everywhere. Whether you consider this worry justified or
not is academic, since the possible out-of-disk-space failure mode is
reason enough to do it anyway.

regards, tom lane