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

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.

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