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

Thorsten Schöning wrote:
> I'm running Postgres 9.6 and backing it up once a while simply by
> stopping the cluster and using rsync on file level. One day I've
> recognized that some files for tables in my backup and prod system
> have the same size and last written timestamp, while the data itself
> actually differs. I recognized that using rsync with checksums and
> wondered why much more data gets transferred than expected. So I
> calculated hash sums for those files and those were different.
>
> The important thing is that after rsync with checksums transmitted
> those changed files with unmodified timestamps, the hash sums of the
> files were back in sync again. So it seems very unlikely that the
> problem is during rsync copying data itself.
>
> I can only think of two reasons: Either Postgres has some behaviour
> where data is actually written to files without changing timestamps or
> my backed up data gets modified somehow, which sounds like corruption,
> because as a backup, it shouldn't get modified of course.
>
> So, is there any such functionality in Postgres, writing data without
> changes to timestamps of the file in the file system? Any other ideas
> on where those hash differences could come from?

PostgreSQL doesn't know or care on which file system it is running,
it just opens files and writes to them. So it won't do anything to
bypass the update of file access times.

Yours,
Laurenz Albe