On 24/01/2017 16:55, Stephen Frost wrote:
Greetings,
* Achilleas Mantzios (achill@xxxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
