Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: Does Postgres ever write to tables without file system
 timestamps getting updated?
From: scott ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <143511646.20170606100454@am-soft.de>
Date: Tue, 6 Jun 2017 06:57:29 -0600
Cc: pgsql-admin@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com>
References: <143511646.20170606100454@am-soft.de>
To: =?utf-8?Q?Thorsten_Sch=C3=B6ning?= <tschoening@am-soft.de>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

What's the resolution of the timestamps on your file system? It's always po=
ssibly that postgres writes, rsync checks, postgres writes again within tha=
t window--especially if the timestamp granularity is a second rather than a=
 much smaller window. (Heck, there have been file systems with 2-second gra=
nularity.)

Use pg_start_backup.

> On Jun 6, 2017, at 2:04 AM, Thorsten Sch=C3=B6ning <tschoening@am-soft.de=
> wrote:
>=20
> Hi all,
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> 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?
>=20
> 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.
>=20
> Thanks for your ideas!
>=20
> P.S.: Posted that on SU as well, but didn't get much attention.
>=20
> https://superuser.com/questions/1216259/does-postgres-ever-write-to-table=
s-without-file-system-timestamps-getting-update
>=20
> Mit freundlichen Gr=C3=BC=C3=9Fen,
>=20
> Thorsten Sch=C3=B6ning
>=20
> --=20
> Thorsten Sch=C3=B6ning       E-Mail: Thorsten.Schoening@AM-SoFT.de
> AM-SoFT IT-Systeme      http://www.AM-SoFT.de/
>=20
> Telefon...........05151-  9468- 55
> Fax...............05151-  9468- 88
> Mobil..............0178-8 9468- 04
>=20
> AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
> AG Hannover HRB 207 694 - Gesch=C3=A4ftsf=C3=BChrer: Andreas Muchow
>=20
>=20
>=20
> --=20
> 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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin