Greetings,
* Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
> On 24/01/2017 16:55, Stephen Frost wrote:
> >* 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
--ignore-existing is actually a *bad* idea, really. There can be cases
where PG will end up calling archive_command on the same file, but in
those cases you should really be checking that the two WAL files are
IDENTICAL, otherwise you may have a misconfigured system and are pushing
the WAL for two different PG systems to the same directory.
> So you say that scp does not perform a sync on the destination file? So that in case of a remote crash it might
return0 while the file isn't written?
Yes, if the remote system crashes right after rsync (or scp) has
returned, the WAL file may not have been sync'd to reliable storage and
will be lost.
> 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.
I would not go on the assumption that since it's been working that it
won't ever fail in an unfortunate way.
Also, always, always, always test your backups. All of them, ideally,
otherwise they may not work when you need them most.
> Anyway, if there is any theory or confirmation on my assumptions for the main question of this thread?
At first blush, I'd guess that someone else put those files there or
that the time changed on one of the systems involved.
Thanks!
Stephen