Date: Tue, 24 Jan 2017 10:55:48 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Strange times in WAL files in archive directory (9.3)
Message-ID: <20170124155548.GV18360@tamriel.snowman.net>
References: <58871A66.306@matrix.gatewaynet.com>
 <58873609.4030004@matrix.gatewaynet.com>
 <13126.1485264550@sss.pgh.pa.us>
 <588759C8.3090209@matrix.gatewaynet.com>
 <14804.1485267261@sss.pgh.pa.us>
 <588768FF.20906@matrix.gatewaynet.com>
 <20170124145556.GR18360@tamriel.snowman.net>
 <5887739D.50405@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="cECC7KiMB9I1evin"
Content-Disposition: inline
In-Reply-To: <5887739D.50405@matrix.gatewaynet.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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

Greetings,

* Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
> On 24/01/2017 16:55, Stephen Frost wrote:
> >* Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
> >>I provided the archive_command in the 1st post. The copy is against another host (called sma in the command) :
> >>archive_command = '/usr/bin/scp %p sma:/smadb/pgsql/pitr/%f'
> >Note that this is not a recommended archive command- there is no
> >guarantee that the copied WAL files are sync'd to disk on the 'sma' host
> >and you could end up losing, potentially, a significant amount of your
> >WAL on a failure.
> I had changed that already to
> /usr/bin/rsync -a --ignore-existing %p sma:/smadb/pgsql/pitr/%f

--ignore-existing is actually a *bad* idea, really.  There can be cases
where PG will end up calling archive_command on the same file, but in
those cases you should really be checking that the two WAL files are
IDENTICAL, otherwise you may have a misconfigured system and are pushing
the WAL for two different PG systems to the same directory.

> So you say that scp does not perform a sync on the destination file? So that in case of a remote crash it might return 0 while the file isn't written?

Yes, if the remote system crashes right after rsync (or scp) has
returned, the WAL file may not have been sync'd to reliable storage and
will be lost.

> Thanks for the suggestions. We have been using a wal archiving +
> base backups + streaming replication combination for years, so I
> guess we'll be alright for the time being. Point is that we recently
> moved to a cloud environment and we have to "port" our traditional
> operations to the utilities/tools provided by the cloud provider.

I would not go on the assumption that since it's been working that it
won't ever fail in an unfortunate way.

Also, always, always, always test your backups.  All of them, ideally,
otherwise they may not work when you need them most.

> Anyway, if there is any theory or confirmation on my assumptions for the main question of this thread?

At first blush, I'd guess that someone else put those files there or
that the time changed on one of the systems involved.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYh3kEAAoJEO1sijiDR2RVuoQP/0+eerf6YKUA5umhv6EthI8x
ywAXdXbM4sSLlBHnMy9VbmYWy6DEkQ00dbPGA7cgcSxUOAyeuBzns9t3GoFIWDQn
MpVOSV/sH2jds9Dh4EMXz+BrjbHlKc6WXrCTIbAdVcpce13mFKAOy0ykzKANmfJY
F4BDEXnoxTzwe/Ax5+t1FFjKEaNSga5Y3orpta50/O09GUsyx9JsYLsyYDf8NSXH
GGSNhhol/pW4C2QrbA/JXXDonrEcQZYGUeLlDPF7J6p7HPdWS7+jKKGLLjMwLQ3t
ut2/t7+Ey/uCeYlU0cmz+QnpSYFzaDGzXjBm/Ya0IhvCijOF6S/PS6AnoQW6WvTu
CHU3hUbq7HK/vgbeD6d4RA2vDs73j+oOgG8HRMu//7n+aynSBz8GtyYE0nwK4Bd3
XTv4/CcXkRaEiLvvu+VWWt+iuCho0wMNT6bKCa/DDvGpgnLNKc2Ltvo/PhpKYx5l
joTuaeyfy2eqldg9OhT0lmL1pIZbDgA4501ieZfSkdHVw//9qbdim9S5+g354uHE
Hn9otR3U2whBEvMLqVQ6Y0XyV9+ZP5RHVCsR2s67uJGGB0S2kfqY5OAgmhS+G2+w
87IkASIav/TIHaTN1ctEYyRCperYTGduFJ8j7FE4IukSNlxFXPM6/Y3Or9w3di1p
5yAjhmd9bkg29di8dOAQ
=Xlor
-----END PGP SIGNATURE-----

--cECC7KiMB9I1evin--