From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: Strange times in WAL files in archive directory (9.3)
Date: Tue, 24 Jan 2017 18:04:35 +0200
Message-ID: <58877B13.2000008@matrix.gatewaynet.com> (raw)
In-Reply-To: <20170124155548.GV18360@tamriel.snowman.net>
References: <58871A66.306@matrix.gatewaynet.com>
<58873609.4030004@matrix.gatewaynet.com>
<13126.1485264550@sss.pgh.pa.us>
<588759C8.3090209@matrix.gatewaynet.com>
<14804.1485267261@sss.pgh.pa.us>
<588768FF.20906@matrix.gatewaynet.com>
<20170124145556.GR18360@tamriel.snowman.net>
<5887739D.50405@matrix.gatewaynet.com>
<20170124155548.GV18360@tamriel.snowman.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 return 0 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Strange times in WAL files in archive directory (9.3)
In-Reply-To: <58877B13.2000008@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox