From: Thorsten Schöning <tschoening@am-soft.de>
To: pgsql-admin@postgresql.org
Subject: Re: Does Postgres ever write to tables without file system timestamps getting updated?
Date: Tue, 6 Jun 2017 15:32:03 +0200
Message-ID: <1599962113.20170606153203@am-soft.de> (raw)
In-Reply-To: <CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com>
References: <143511646.20170606100454@am-soft.de>
<CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Guten Tag scott ribe,
am Dienstag, 6. Juni 2017 um 14:57 schrieben Sie:
> 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[...]
Obviously I wasn't clear enough: Postgres is not running and my
problem is with files and their timestamps in the past. So regarding
the timestamps the files didn't change for weeks and the files have
the same size and timestamp in the source and my backup. But if I
calculate hash sums of those files the results may differ, so rsync
using checksums will back them up, although the files claim that they
haven't changed for days/weeks.
Maybe the following example makes it more clear:
Backup:
> a1171645dc187c498ce05a25b0e5157f 2613.13
> -rw------- 12 109 119 1073741824 May 21 04:58 2613.13
Production:
> f02c1c2724714af2c5c08f8b67ab0f11 2613.13
> -rw------- 1 postgres postgres 1073741824 Mai 21 04:58 2613.13
The exact same file regarding size and timestamp, but actually
different content. After using rsync with checksums, the file in the
backup still has the same size and timestamp, but new content, because
this time the calculated hash is the same as in production.
The file belongs to pg_largeobject and that table contains a lot of
data, hence the named suffixes. Most of those files in the sequence
have old timestamps like the one above, more than a few days without
any writing, and are NOT all backed up and have the same MD5 hash like
in my backup. Only few files once a while differ like the one in the
example.
Being pg_largeobject and because we actually delete large objects from
the database once a while, reusing existing files is perfectly OK by
Postgres and as expected. But all my tests showed that during writes
the timestamp of those files is actually updated and not kept or reset
that much into the past. Our file system in use is ext4, so shouldn't
have a problem with timestamps in general.
And that's what makes me wonder: If Postgres doesn't reset timestamps
to the past or freeze them somehow for some reason, this sounds like
data corruption in my backups.
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: <1599962113.20170606153203@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