From: Thorsten Schöning <tschoening@am-soft.de>
To: pgsql-admin@postgresql.org
Subject: Does Postgres ever write to tables without file system timestamps getting updated?
Date: Tue, 6 Jun 2017 10:04:54 +0200
Message-ID: <143511646.20170606100454@am-soft.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
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: tschoening@am-soft.de
Subject: Re: Does Postgres ever write to tables without file system timestamps getting updated?
In-Reply-To: <143511646.20170606100454@am-soft.de>
* 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