Re: Does Postgres ever write to tables without file system timestamps getting updated?

Greetings,

* scott ribe (scott_ribe(at)elevated-dev(dot)com) wrote:
> 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--especially if the timestamp granularity is a second rather than a much smaller window. (Heck, there have been file systems with 2-second granularity.)

Thorsten said that the database was shut down before performing the
backup, which I would generally think to be sufficient. It is, perhaps,
barely possible that a scripted "shutdown, rsync, startup" would result
in a file being modified before and after the rsync but within the
1-second typical granularity that rsync uses. That is certainly one of
the risks of using an rsync-based backup solution, though I've only
actually seen corruption happen due to rsync when using it with the
pg_start/stop_backup methodology (and only in a controlled environment,
so far), but then, I don't run into many cases where people are willing
to shut down their database to do a backup.

> Use pg_start_backup.

Just using pg_start_backup, of course, is insufficient if the database
is running and is impossible if the database is not running. To
properly backup a running PG system, pg_start/stop_backup needs to be
used *and* the WAL created during the backup must be preserved (through
archive_command or pg_receivewal or similar).

Generally speaking, I'd strongly recommend against trying to write your
own backup solution for PG and would, instead, suggest using one of the
existing solutions, such as pg_basebackup, pgBackRest, or barman.

Thanks!

Stephen