Date: Tue, 6 Jun 2017 09:10:08 -0400
From: Stephen Frost <sfrost@snowman.net>
To: scott ribe <scott_ribe@elevated-dev.com>
Cc: Thorsten =?iso-8859-1?Q?Sch=F6ning?= <tschoening@am-soft.de>,
	pgsql-admin@postgresql.org
Subject: Re: Does Postgres ever write to tables without file system
 timestamps getting updated?
Message-ID: <20170606131008.GR3151@tamriel.snowman.net>
References: <143511646.20170606100454@am-soft.de>
 <CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="M5NuIqbBgnS7wB7q"
Content-Disposition: inline
In-Reply-To: <CB5E9FC3-94CE-4F12-B140-01E630474CD2@elevated-dev.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--M5NuIqbBgnS7wB7q
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* scott ribe (scott_ribe@elevated-dev.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

--M5NuIqbBgnS7wB7q
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJZNqmwAAoJEO1sijiDR2RVe9sP/ROtmzQN2WBSOGr4jkQ4Ff7T
pKnwoQC1XZaFhehW69s/nH7PEBkxa8EWENycoUZ0OXVzFaMx1SO2zF1fEKB3IiOo
bcT8yzaNztYHXn3B2z0KJANEwTWS/UMdI7StMN3hM3hddvLGgaf9B4s97vwfCb7Y
t97ftQJv9yEruI/XBIUayvzewGkqxD4aTUCjCFHkJ0h274pxBOg/jnwxZNqqsrZ5
J6seazohSW87znznTZ55pqAECwLAiby6mgVteUxVUw/c1aPD5QBX8TMDRIcA6bIH
DabdmRmQRZo8DLYiuQpXNFB9622H16gTH+wAYB3bOETDEFcM4CvCQgNS6l+6u17s
+PjkyLOdy0xi01hEhjkyvevmCu4JA29PxbIl/WPg7TaxpMQ3Dr/fSiBrxFc5Zscn
rSQuEbqUj4RkvwioGaz2mTyAnPFbP7PESkyIHAgIj7yPwK5v1E0TO1Q/v+wn8QPi
12++ijyEEHu6D6ACIVMN5eM5LCLgG5rZY25+1kB1fR4ewqipGit+kbtMGXX7+psU
q0ULChvM2Hx5Zs6wepXHbfIlbx6WPtqrL4hrODyeWfXTubMiluO8TnNQdiTxtzpP
iB2HN/jLols3QsK8w9xAX73lkfXEwTJzGUnpHnYKeRDmqYO8bVBlSAo2kUqrusc9
Lr/mItFZTIWxiBBeUQMc
=lJXo
-----END PGP SIGNATURE-----

--M5NuIqbBgnS7wB7q--