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 old, already backed up files for tables still have the same file size and timestamp like their counterpart in the source, but rsync tries to back them up because contents of those files haven changed. It's important to note that regarding timestamps in the source the files didn't change for days or even weeks. rsync is using checksums and calculating MD5 hashes for the files in question reveals different hashes as well. The following is one example:


a1171645dc187c498ce05a25b0e5157f  2613.13

-rw------- 12 109 119 1073741824 May 21 04:58 2613.13


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.

From the following very old data files, mostly unchanged for days/weeks, e.g. 2613.13 got transferred because of different checksums, while 2613.10 didn't:

-rw------- 1 postgres postgres 1073741824 Jun  4 04:40 2613
-rw------- 1 postgres postgres 1073741824 Mai 21 04:42 2613.1
-rw------- 1 postgres postgres 1073741824 Mai 21 04:56 2613.10
-rw------- 1 postgres postgres 1073741824 Mai 21 04:57 2613.11
-rw------- 1 postgres postgres 1073741824 Mai 21 04:57 2613.12
-rw------- 1 postgres postgres 1073741824 Mai 21 04:58 2613.13
-rw------- 1 postgres postgres 1073741824 Mai 21 04:59 2613.14
-rw------- 1 postgres postgres 1073741824 Mai 28 04:40 2613.15
-rw------- 1 postgres postgres  686645248 Jun  4 04:42 2613.16
-rw------- 1 postgres postgres 1073741824 Mai 21 04:44 2613.2
-rw------- 1 postgres postgres 1073741824 Mai 21 04:46 2613.3
-rw------- 1 postgres postgres 1073741824 Mai 21 04:47 2613.4
-rw------- 1 postgres postgres 1073741824 Mai 21 04:49 2613.5
-rw------- 1 postgres postgres 1073741824 Mai 21 04:50 2613.6
-rw------- 1 postgres postgres 1073741824 Mai 21 04:52 2613.7
-rw------- 1 postgres postgres 1073741824 Mai 21 04:53 2613.8
-rw------- 1 postgres postgres 1073741824 Jun  4 04:40 2613.9
-rw------- 1 postgres postgres    4407296 Jun  4 04:42 2613_fsm
-rw------- 1 postgres postgres     548864 Jun  4 04:42 2613_vm

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.

So, is there any such functionality in Postgres, writing data without changes to timestamps of the file in the file system?

Because of a lack of response, I asked my question on the mailing list of Postgres as well.

  • Interesting conundrum. Is it possible that your rsync config has the options "--size-only" which ignores the last time change and looks only at the size of the file. I could certainly see postgres changing the contents of files without changing the file size. – davidgo Jun 6 '17 at 8:25
  • Maybe I was not clear enough: The last written timestamp of the file is the problem, not the size. In my case the size in deed might not change for a long time or even ever, but I guess the timestamp of the file should whenever Postgres writes data. But that timestamp is the same in my back up, while the data in the source is actually different. – Thorsten Schöning Jun 6 '17 at 10:18
  • In my experience file-timestamps (I presume you mean "last modified time") get updated when the file is "closed", "flushed" and "on writes". The last premise is not always true and depends on a combination of the OS, the file-system used and the exact mechanism used by the software doing the writes. It is possible to have writes without last modified time being updated. That timestamp should ALWAYS be updated on a close or flush though. – Tonny Jun 7 '17 at 14:40

This might not be a feature of Postgres, but the 'noatime' mount option of your file system that is often used to increase performance of disks.

  • Good idea, but not the case for me as timestamps are updated on writes. – Thorsten Schöning Jun 12 '17 at 10:26
  • @ThorstenSchöning from the mount manpage: noatime Do not update the file access time when reading from a file. This option is useful on file systems where there are large numbers of files and performance is more criti- cal than updating the file access time (which is rarely ever important). – bbaassssiiee Jun 12 '17 at 12:17
  • I think it's mtime here, not atime. – Kamil Maciorowski Jun 12 '17 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.