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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
