Re: Differential backup

On Tue, Apr 27, 2010 at 8:15 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
>
> 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:
>

You can't use the mvcc information to do incremental backups because
transactions don't necessarily commit or take snapshots in sequential
order. A row can be inserted by transaction id 500 and then later
updated by transaction id 400 and then later deleted by transaction id
300.

I think what you need to use is the LSN on the page. Normally when you
take a hot backup you note the LSN at the start and end of the backup
and know that you need to replay that range of logs to have a
consistent restore. An incremental backup would be the same except it
would only back up any blocks that have an LSN > the start of the last
backup. Then you could restore the previous backup, apply this
incremental to bring the restore up to the beginning of this backup,
then apply the logs for the range of this backup.

--
greg