Re: Are file system level differential/incremental backups possible?

On 10/13/2011 05:30 AM, Bob Hatfield wrote:
> Is it possible to do a full file system level backup of the data
> directory, say once a week, and differentials or incrementals daily?

I'd love to be able to do this, but you can't do it usefully at a
file-system level. There's too much churn in the data files for even a
binary diff to be much use - and even if it were, the performance of it
would be miserable.

You *could* do a differential so long as it's a proper one that tracks
file removals as well as additions/changes. It'd be pretty pointless
though as you wouldn't save much if any storage.

I've looked into database-level diffs, but from what I can find out it
seems that PostgreSQL's MVCC system doesn't store enough information to
produce a differential dump, either. There's no way to detect and record
tuples that were deleted then vacuumed away since the last backup was
taken, so a trigger-based or WAL-based system is necessary.

I'd love a way to "collapse" or merge a set of WAL segments into a
minimal diff that only contained just enough information to get from the
start to end state of the series of WAL segments, rather than all the
churn in-between. This would be great for storing longer PITR histories
(but more coarsely) and being able to do faster restores. Unfortunately
given how scattered writes are I doubt it'd actually be possible or any
faster if it was.

SQL-level differentials would be great, though.

--
Craig Ringer