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)
Date: Tue, 24 Jan 2017 09:55:56 -0500
Message-ID: <20170124145556.GR18360@tamriel.snowman.net> (raw)
In-Reply-To: <588768FF.20906@matrix.gatewaynet.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Greetings,
* 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.
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).
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, achill@matrix.gatewaynet.com
Subject: Re: Strange times in WAL files in archive directory (9.3)
In-Reply-To: <20170124145556.GR18360@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox