What's the resolution of the timestamps on your file system? It's always possibly that postgres writes, rsync checks,
postgreswrites again within that window--especially if the timestamp granularity is a second rather than a much smaller
window.(Heck, there have been file systems with 2-second granularity.)
Use pg_start_backup.
> On Jun 6, 2017, at 2:04 AM, Thorsten Schöning <tschoening@am-soft.de> wrote:
>
> Hi all,
>
> 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?
>
> Sounds like to be sure I need to regularly generate hashes of my
> backups and compare those to unmodified files in the backup source.
> The backups are not stored on checksumming file systems like BTRFS or
> ZFS, so silent data corruption might be an aspect.
>
> Thanks for your ideas!
>
> P.S.: Posted that on SU as well, but didn't get much attention.
>
>
https://superuser.com/questions/1216259/does-postgres-ever-write-to-tables-without-file-system-timestamps-getting-update
>
> Mit freundlichen Grüßen,
>
> Thorsten Schöning
>
> --
> Thorsten Schöning E-Mail: Thorsten.Schoening@AM-SoFT.de
> AM-SoFT IT-Systeme http://www.AM-SoFT.de/
>
> Telefon...........05151- 9468- 55
> Fax...............05151- 9468- 88
> Mobil..............0178-8 9468- 04
>
> AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
> AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
--
Scott Ribe
scott_ribe@elevated-dev.com
(303) 722-0567
