Re: Differential backup

Simon Riggs wrote:

> Is this route worthwhile? Or in some way unacceptable?

From an admin perspective, I think block-level differentials would be a
lot more useful, especially if index storage could be safely excluded.

IMO Pg really could use an "index bad or missing, rebuild on postmaster
start" flag so that indexes could simply be omitted from backups and
would be automatically REINDEXed on startup. That'd be *great* for
pg_start_backup() / pg_stop_backup() filesystem level backups,
especially if indexes were configured to live in another tablespace.

Another avenue possibly worth investigating may be using the in-heap
mvcc information to do SQL-level differential backups of individual
tables or of the whole database. think:

pg_dump --incremental --last-backup-id '10296:10296:'

where "--last-backup-id" is the output of "select
txid_current_snapshot()" from the last backup, and could possibly be
fished out of a header in the previous dump.

This would be *incredibly* handy for people who have one database in a
that's more important than another and needs long-term history storage,
but for whom PITR is a PITA because it's whole-cluster-or-nothing.

This is trivial to do for individual append-only tables. I was trying to
figure out how to handle updates/deletes but quickly found myself
completely stumped.

I'd be surprised if this hasn't been looked at and put in the
"impossible" or "too hard" pile, but thought it was worth mentioning on
the off chance.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/