From: scott ribe <scott_ribe@elevated-dev.com>
To: Thorsten Schöning <tschoening@am-soft.de>
Cc: pgsql-admin@postgresql.org
Subject: Re: Does Postgres ever write to tables without file system timestamps getting updated?
Date: Tue, 6 Jun 2017 06:57:29 -0600
Message-ID: <CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com> (raw)
In-Reply-To: <143511646.20170606100454@am-soft.de>
References: <143511646.20170606100454@am-soft.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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@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-times...
>
> 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, tschoening@am-soft.de
Subject: Re: Does Postgres ever write to tables without file system timestamps getting updated?
In-Reply-To: <CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox