Re: Strange times in WAL files in archive directory (9.3)

On 24/01/2017 16:55, Stephen Frost wrote:
> Greetings,
>
> * Achilleas Mantzios (achill(at)matrix(dot)gatewaynet(dot)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