On 24/01/2017 17:55, Stephen Frost wrote:
> 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.
No one else worked on this, and ntp has been running correctly for both systems. This was my first guess as well but
no.
I guess PostgreSQL just flushed them to the archive before deleting/renaming them. Does it make any sense?
>
> Thanks!
>
> Stephen
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt