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