What's the resolution of the timestamps on your file system? It's always possibly that postgres writes, rsync checks, postgres writes 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@xxxxxxxxxx> 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@xxxxxxxxxx > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin -- Scott Ribe scott_ribe@xxxxxxxxxxxxxxxx (303) 722-0567 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
