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

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(at)am-soft(dot)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(dot)Schoening(at)AM-SoFT(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
(303) 722-0567