From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: Strange times in WAL files in archive directory (9.3)
Date: Tue, 24 Jan 2017 17:32:45 +0200
Message-ID: <5887739D.50405@matrix.gatewaynet.com> (raw)
In-Reply-To: <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>
<20170124145556.GR18360@tamriel.snowman.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 24/01/2017 16:55, Stephen Frost wrote:
> 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.
I had changed that already to
/usr/bin/rsync -a --ignore-existing %p sma:/smadb/pgsql/pitr/%f
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?
>
> 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 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.
Anyway, if there is any theory or confirmation on my assumptions for the main question of this thread?
> Thanks!
>
> Stephen
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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: achill@matrix.gatewaynet.com
Subject: Re: Strange times in WAL files in archive directory (9.3)
In-Reply-To: <5887739D.50405@matrix.gatewaynet.com>
* 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