Re: Differential backup

On Apr 27, 2010, at 15:50 , Alvaro Herrera wrote:
> Simon Riggs wrote:
>> Thinking about allowing a backup to tell which files have changed in the
>> database since last backup. This would allow an external utility to copy
>> away only changed files.
>>
>> Now there's a few ways of doing this and many will say this is already
>> possible using file access times.
>>
>> An explicit mechanism where Postgres could authoritatively say which
>> files have changed would make many feel safer, especially when other
>> databases also do this.
>>
>> We keep track of which files require fsync(), so we could also keep
>> track of changed files using that same information.
>
> Why file level? Seems a bit too coarse (particularly if you have large
> file support enabled). Maybe we could keep block-level last change info
> in a separate fork.

Hm, but most backup solutions work per-file and not per-block, so file-level tracking probably has more use-cases that block-level tracking..

In any case, it seems that this information could easily be extracted from the WAL. The archive_command could call a simple tool that parses the WAL and tracks the latest LSN per database file or page or whatever granularity is required. This, together with the backup label of the last backup should be enough to compute the list of changed files I think.

best regards,
Florian Pflug