Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42568=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1WzoNZ-0007dz-W4
 for pgsql-admin@arkaria.postgresql.org; Wed, 25 Jun 2014 14:40:54 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42568=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1WzoNY-0004gP-4T
 for pgsql-admin@arkaria.postgresql.org; Wed, 25 Jun 2014 14:40:52 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1WzoNK-0004KQ-5v
 for pgsql-admin@postgresql.org; Wed, 25 Jun 2014 14:40:38 +0000
Received: from mail-yk0-x22b.google.com ([2607:f8b0:4002:c07::22b])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1WzoNE-0005kt-7j
 for pgsql-admin@postgresql.org; Wed, 25 Jun 2014 14:40:33 +0000
Received: by mail-yk0-f171.google.com with SMTP id 200so1135333ykr.2
 for <pgsql-admin@postgresql.org>; Wed, 25 Jun 2014 07:40:31 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=DJaSyZzOaY8pYfd3FwTscZ4nUgSpq/9EbC0Yv/GX52M=;
 b=NnBCTfQzwzv8E9dLUWc17AafJWT1Tt3hN3NbgaJlygd2ZX29AQh7HI5EwRU5sABupQ
 hEhpwe7opyxAyonbpJ1vJ2KAPpmUPLVfJ/bU/ggwBqPSvD0xI3SQyg5KHzux+Ss7aCav
 SWel1zQmNa4VTXuBD8sH0doUt19AGkJreSduwMrXuWiXQf67QZ5TDDpydC2f0cZehJcQ
 y2fjnL/fFHNRmo9YorYF75xQop3ytBior10n66HN0Qsc3t//KkeK7+0Hbpeb2aNRpVoK
 a2HJVH/raINUtiHoo0p+JJRlF+FtZPzLi79KV3vLuwvXSyoeaF6ufjynSxXR/KKu7701
 7DjA==
X-Received: by 10.236.25.234 with SMTP id z70mr11898855yhz.107.1403707231503;
 Wed, 25 Jun 2014 07:40:31 -0700 (PDT)
Received: from [192.168.254.4] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id b2sm5348543yhi.47.2014.06.25.07.40.30
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Wed, 25 Jun 2014 07:40:31 -0700 (PDT)
Message-ID: <53AADF59.50000@gmail.com>
Date: Wed, 25 Jun 2014 10:40:25 -0400
From: John Scalia <jayknowsunix@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Andrew Krause <andrew.krause@breakthroughfuel.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Wal archive way behind in streaming replication
References: <53A9B567.5070301@gmail.com> <53A9C139.2040704@gmail.com>
 <3709F6AF-032E-4095-B0CB-DC1CE36F6B4B@breakthroughfuel.com>
In-Reply-To: <3709F6AF-032E-4095-B0CB-DC1CE36F6B4B@breakthroughfuel.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

A little examination of the pgarch.c file showed what the archive process o=
n the primary is doing. Anyway, to ensure that the primary knows that it ha=
s transmitted all the up to=20
date WALs, I went into the primary's data/pg_xlog/archive_status directory =
and performed "touch 00000003000000900000036.ready" and repeated this comma=
nd for the other WALs up to=20
*44.ready. This really shouldn't have been a problem as the most recent WAL=
 file in pg_xlog was *45. The archiver then picked up all those WAL files a=
nd transmitted them to the=20
standbys. At least I saw them appear on the standby in the directory specif=
ied in the recovery.conf file.

Now, what I really don't understand is the standby's behavior. After the WA=
Ls arrived, I saw nothing in today's pg_log/Wed.log file showing it saw the=
m. I then issued a service=20
postgresql-9.3 restart and this is what was spit out in the log:

LOG: entering standby mode
LOG: restored log file "00000000300000000900000035" from archive
LOG: unexpected pageaddr 9/1B000000 in log segment 000000003000000009000000=
35, offset 0
LOG: started streaming WAL from primary at 9/35000000 on timeline 3
FATAL: the database system is starting up
LOG: consistent recovery state reached at 9/350000C8
LOG: redo starts at 9/350000C8
LOG: database system is ready to accept read only connections

Two things stand out here. First, the standby didn't seem to process the ne=
wly arrived WAL files, and second. what's with the FATAL: in the logfile?
--
Jay

On 6/24/2014 2:52 PM, Andrew Krause wrote:
> You shouldn=92t have to touch the files as long as they aren=92t compress=
ed.  You may have to restart the standby instance to get the recovery to be=
gin though.  I=92d suggest tailing your instance log and restarting the sta=
ndby instance.  It should show that the logs from the gap are applying righ=
t away at startup.
>
>
> Andrew Krause
>
>
>
>
> On Jun 24, 2014, at 1:19 PM, John Scalia <jayknowsunix@gmail.com> wrote:
>
>> Ok, I did the copy from pg_xlog directory into the restore.conf specifie=
ddirectory. The standby servers seem fine with that, however, just copying =
does not inform the primary that
>> the copy has happened. The archive_status directory under pg_xlog on the=
 primary still thinks the last WAL sent was *B7 and yet it's now writing *C=
9. When I did the copy it was
>> only up to *C7 and nothing else has shown in the standby's directory.
>>
>> Now, the *.done files in archive_status are just zero length, but I'm a =
bit hesitant to just do a touch for the ones I manually copied as I don't k=
now if this is from an in-memory
>> queue or if it Postgresql reads the contents of this regularly in order =
to decide what to copy.
>>
>> Is that safe to do?
>>
>> On 6/24/2014 9:56 AM, Andrew Krause wrote:
>>> You can copy all of the WAL logs from your gap to the standby.  If you =
place them in the correct location (directory designated for restore) thein=
stance will automatically apply them all.
>>>
>>>
>>> Andrew Krause
>>>
>>>
>>>
>>> On Jun 23, 2014, at 9:24 AM, John Scalia <jayknowsunix@gmail.com> wrote:
>>>
>>>> Came in this morning to numerous complaints from pgpool about the stan=
dby servers being behind from the primary. Looking into it, no WAL files ha=
d been transferred since late Friday. All I did was restart the primaryand =
the WAL archving resumed, however, looking at the WAL files on the standby =
servers, this is never going to catch up. Now, I've got the archive_timeout=
 on the primary =3D 600 or 10 minutes and I see WAL files in pg_xlog every =
10 minutes. As they show up on the standby servers, they're also 10 minutes=
 apart, but the primary is writing *21 and the standby's areonly up to *10.=
 Now, like I said prior, with there being 10 minutes (600seconds) between t=
ransfers (the same pace as the WALs are generated) it will never catch up. =
Is this really the intended behavior? How would I get the additional WAL fi=
les over to the standbys without waiting 10 minutes to copy them one at a t=
ime?
>>>> --
>>>> Jay
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>>
>>
>>
>>
>> --=20
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>




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