Date: Tue, 24 Jan 2017 09:55:56 -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: <20170124145556.GR18360@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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="8zLhQ/8aZxqGwKat"
Content-Disposition: inline
In-Reply-To: <588768FF.20906@matrix.gatewaynet.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--8zLhQ/8aZxqGwKat
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
> I provided the archive_command in the 1st post. The copy is against anoth=
er host (called sma in the command) :
> archive_command =3D '/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.

It's also, sadly, quite slow, with no way to be parallelized (PG will
not start a new archive_command until the prior one completes, meaning
that you could end up with a large backlog of un-archived WAL if the
archive_command can't keep up, potentially leading to an out-of-space
situation on your WAL filesystem, leading to a PANIC and crash).=20

There's also no compression with the proposed approach (in transit or at
rest).

Some of this is fixable with SSH (using ControlMaster, adding
compression options, though that would only compress in transit, not on
disk; if you wanted on-disk compression then you'd have to re-compress
it again), but not all of it (you aren't going to get scp to fsync the
WAL on the remote side, at least not by itself), but as noted by David
already, using a third-party solution which has addressed all of these
issues is really a better option than hand-rolling your own system.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYh2r7AAoJEO1sijiDR2RV6GAQAMQV1ebk4NX6sJqcMn/R9Q9M
9H13wQf5q+0VFfa3Ntfq4yHwMmTz6I0q2VtVIVDWsLvOrjW8Oid8HqPzMdumSdeu
mGvYVbPSAJly8KUuGfUzBXxV9ESa40IEXVVQPGPyEKiu6kVbk0n80dUeKJRX1m1R
e7yAcX2tVB4LtJYHXh+o6XDyce4nRf+lGDoWL09dQKOb629mRl/bTG1XwgKlQXhq
k6epCityT+fHxso/uet0JvR1n2W79WoFjYLRBhI4UipGBvc/xxnWa5+5Cg/nGQ3x
wsPvmnhoZnRVcrlQfMKIGDFBSqznLAd2nxWnCLeFQIYHr6T7CAwOeIKxgMAcKDeI
NPIm9OAQMC/fvyI9ha9f/RyXy1MwRK06J+vZBvnvVUMf0PBTfj8I1rn0+/8B+jJj
nNNzhClpFcmiTSJ4VQm53IHihuBLliHNqC7ZtkhQulEqUW+HG+2E1d4PncofCf8G
ZmsjN4Z7Dpvfs6f4uRvuWPPLTdLjpZofxzFLZe60aNqngutf/vbQ5M80AYazBg6a
ZDXzq5yqpqTx3az6cQUefcr4HA3SwxQXbPnPWDERzRYpN6PRQPv0gtzn0a9odDum
VvSsqTQ50KPSh7l73YmHTgdsRrhc8dKl4+XITmRXjJYgPGSykFhFZJV42Dzu+68i
1JhsJ+/aPYjxE3+sms8J
=7YYt
-----END PGP SIGNATURE-----

--8zLhQ/8aZxqGwKat--