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
