Re: Differential backup

On Tue, 2010-04-27 at 10:32 -0400, Michael Tharp 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. 

To be on the safe side you need to record the latest table data change
time _after_ the backup anyway, it is easy to wait a few secs to be
sure.

> 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.
> 
> -- m. tharp
> 


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