Subject: Re: Strange times in WAL files in archive directory (9.3)
To: pgsql-admin@postgresql.org
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>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <5887739D.50405@matrix.gatewaynet.com>
Date: Tue, 24 Jan 2017 17:32:45 +0200
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
In-Reply-To: <20170124145556.GR18360@tamriel.snowman.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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