References: <53E5603B.5040102@agliodbs.com>
 <1407777686.41146.YahooMailNeo@web122306.mail.ne1.yahoo.com>
 <WM!2e9e963ef1fa06e085aa9dbf135a205b718dad5470c1598eeabb71225f19676528ed8e4fad97f9075cbabdcbac8b68ac!@asav-1.01.com>
 <53E90488.9040103@agliodbs.com>
Message-ID: <1407782188.34521.YahooMailNeo@web122305.mail.ne1.yahoo.com>
Date: Mon, 11 Aug 2014 11:36:28 -0700
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Sample archive_command is still problematic
To: Josh Berkus <josh@agliodbs.com>,
 "pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>
In-Reply-To: <53E90488.9040103@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

Josh Berkus <josh@agliodbs.com> wrote:
> On 08/11/2014 10:21 AM, Kevin Grittner wrote:
>>> Is there some good reason why "test ! -f" was added to the
>>> sample?
>>
>> In an environment with more than one cluster archiving, it is
>> otherwise way too easy to copy a config file and have the WAL files
>> of the two systems overwriting one another.=A0 I consider a check for
>> an already existing file on the target to be very good practice.
>> The errors in the log are a clue that something went wrong, and
>> gives you a chance to fix things without data loss.
>
> It depends on what you're guarding against.=A0 In the case I was dealing
> with, the master crashed in the middle of an archive write.=A0 As a
> result, the file existed, but was incomplete, and *needed* to be
> overwritten.=A0 But because of 'test -f' archiving just kept failing.

I've seen that happen, too.=A0 It's just that the script I used sent
an email to the DBAs when that happened, so the problem was quickly
investigated and resolved.=A0 Also, our monitoring "big board" set an=20
"LED" to red if we went an hour without a new WAL appearing in the=20
archive directory.=A0 IMV the archiving script should ensure there is=20
no data loss, and you should have monitoring or alert systems in=20
place to know when things stall.

>> The problem with the recommended command is that cp is not atomic.
>> The file can be read before the contents are materialized, causing
>> early end to recovery.=A0 I have seen it happen.=A0 The right way to do
>> this is to copy to a different name or directory and mv the file
>> into place once it is complete -- or use software which does that
>> automatically, like rsync does.
>
> Yeah, realistically, I think we need to start supplying a script or two
> in /contrib and referencing that.=A0 I'm not sure how to make it work for
> the Windows users though.

That might work.=A0 We should do something, though.=A0 The example we
give in the docs is not production quality IMO, and is something of
an embarrassment.=A0 The problem is, it may be hard to get agreement
on what that should look like.=A0 As a DBA, I insisted on the check
for an existing file.=A0 I also insisted on having scripts send an
email to the DBAs on the first occurrence of a failure (but not to
spam us on each and every failed attempt).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs