Re: Differential backup

On Tue, Apr 27, 2010 at 10:32 AM, Michael Tharp
<gxti@partiallystapled.com> wrote:
> On 04/27/2010 09:59 AM, Kevin Grittner wrote:
>>
>> Under what circumstances would PostgreSQL
>> modify a file without changing the "last modified" timestamp or the
>> file size?
>
> Do all OSes have sub-second precision mtimes? Because otherwise I could see
> a scenario such at this:
>
> * File is modified
> * Backup inspects and copies the file in the same second
> * File is modified again in the same second, so the mtime doesn't change
> * Backup is run again some time later and sees that the mtime has not
> changed
>
> Even with microsecond precision this kind of scenario makes me squidgy,
> especially if some OSes decide that skipping frequent mtime updates is OK.
> Florian's point about clock changes is also very relevant. Since Postgres
> has the capability to give a better answer about what is in the file, it
> would be best to use that.

Why not just force all files to be checked irregardless of mtime?  The
proposal only seems a win to me if a fair percentage of the larger
files don't change, which strikes me as a relatively low level case to
optimize for.  Maybe I'm missing the objective, but it looks like the
payoff is to avoid scanning large files for checksums.  If I was even
infinitesimally insecure about rsync missing files because of
clock/filesystem issues, I'd simply force it.

One cool thing about making postgres 'aware' of last backup time is
that you could warn the user in various places that the database is
not being properly backed up (pg_dump would have to monitor
last_backup_time as well then).  Good stuff, but I bet most people who
aren't backing up the database also aren't checking the log :-).

The block level case seems pretty much covered by the hot standby feature.

merlin