Re: Does Postgres ever write to tables without file system timestamps getting updated?

On 6/6/17 10:39 AM, Thorsten Schöning wrote:
> Guten Tag scott ribe,
> am Dienstag, 6. Juni 2017 um 16:28 schrieben Sie:
>
>> You seem to be assuming that the files were changed close to the
>> time you found the problem;[...]
>
> No, I assume that the files were last changed at the time of the last
> written timestamp in the production system. Which would make my
> backup corrupt if it has the same size/timestamp but different data.
> And that change in my backup could have been introduced anytime
> between the last backup and now, I simply don't know, because I don't
> calculate hash sums for my backups or use some checksumming file
> system like BTRFS or ZFS. So it might be that my backups are simply
> broken for various reasons and I need to have a closer look at this.

If you used rsync to do the backup originally then it is very possible
to have timestamp/size be the same and the checksums be different.

This is because PostgreSQL may modify a file while rsync is copying it.
If this happens in the same second that rsync built the manifest then
the timestamp will not be updated but the data may change.

This is why it is not safe to do incremental backups of PostgreSQL with
rsync without using checksums.

--
-David
david(at)pgmasters(dot)net