Re: Differential backup

On Tue, 2010-04-27 at 08:59 -0500, Kevin Grittner wrote:
> Simon Riggs <simon@2ndQuadrant.com> 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.
>  
> Who would say otherwise?  Under what circumstances would PostgreSQL
> modify a file without changing the "last modified" timestamp or the
> file size?  If you're concerned about the converse, with daemon-
> based rsync you can copy just the modified portions of a file on
> which the directory information has changed.  Or is this targeting
> platforms which don't have rsync?

I see the main value when doing pg_dump based backups and being able to
know if the table was modified by DML (insert/update/delete/truncate) or
by something "invisible" like vacuum or setting hint bits.

Currently the only way to keep this info is by having triggers on all
tables on all DML 

> > An explicit mechanism where Postgres could authoritatively say
> > which files have changed would make many feel safer, especially
> > when other databases also do this.
>  
> Why?  I must be missing something, because my feeling is that if you
> can't trust your OS to cover something like this, how can you trust
> any application *running* under that OS to do it?
>  
> > Is this route worthwhile?
>  
> I'm not seeing it, but I could be missing something.  Can you
> describe a use case where this would be beneficial?
>  
> -Kevin
> 


-- 
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability   Services, Consulting and Training