Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42571=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1WzrNt-0007lP-Uq
 for pgsql-admin@arkaria.postgresql.org; Wed, 25 Jun 2014 17:53:26 +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+M42571=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1WzrNt-0002cv-EU
 for pgsql-admin@arkaria.postgresql.org; Wed, 25 Jun 2014 17:53:25 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1WzrNs-0002cj-MW
 for pgsql-admin@postgresql.org; Wed, 25 Jun 2014 17:53:24 +0000
Received: from mail-yh0-x230.google.com ([2607:f8b0:4002:c01::230])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1WzrNo-00052O-Ox
 for pgsql-admin@postgresql.org; Wed, 25 Jun 2014 17:53:23 +0000
Received: by mail-yh0-f48.google.com with SMTP id b6so1387158yha.21
 for <pgsql-admin@postgresql.org>; Wed, 25 Jun 2014 10:53:18 -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=QHtSDhUrvEbIZI2FR2hMDde63OnrSSO+FJ1pRF1vECQ=;
 b=Icyhh5tqj+SOPIoAN7UCUIHKhMQSDLcoT9R5JtWDYQY2oVkfA/2kNYKB34s7e4XZ5c
 aJaeoHFQfdBIIvoJgehdFb8D08qAl3kbdzFGVpwsrvl0FeTvGyWzu0+rvHL5h6imA3A9
 oyubVSdXyLVe5jYrP5fX0+atHc3vQwOlQ3UGwKA3W6F+OaIhtoqMMWp50AQBnHzkQsY7
 OThLwseyj5NMqZ6d6F4Dsv4/JH5i6t4DBWlxkbNmQ+2kLMdYATX5NpV8k/qxZkOR0B1h
 rQAL5wvPgAA+5+VzkiDxoHzMCfX7YQjREWqin6QbGlUQ1D5JFQpuNQq5WdhzjcN7Nkp8
 aGgw==
X-Received: by 10.236.29.7 with SMTP id h7mr14128579yha.42.1403718798741;
 Wed, 25 Jun 2014 10:53:18 -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 d98sm6144647yhq.21.2014.06.25.10.53.17
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Wed, 25 Jun 2014 10:53:18 -0700 (PDT)
Message-ID: <53AB0C89.9050800@gmail.com>
Date: Wed, 25 Jun 2014 13:53:13 -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: Jerry Sievers <gsievers19@comcast.net>
CC: Andrew Krause <andrew.krause@breakthroughfuel.com>,
 "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>
	<53AADF59.50000@gmail.com>
 <86egydndoq.fsf@jerry.enova.com>
In-Reply-To: <86egydndoq.fsf@jerry.enova.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

Well, having read and understanding the source code in pgarch.c, I saw noth=
ing dangerous for performing these steps. Maybe there is something deeper i=
n there, but it just seemed=20
odd that the standby isn't recognizing the new files considering they arriv=
ed by traditional means. I'm going to go back and study the source a little=
 more.

On 6/25/2014 12:42 PM, Jerry Sievers wrote:
> John Scalia <jayknowsunix@gmail.com> writes:
>
>> A little examination of the pgarch.c file showed what the archive
>> process on the primary is doing. Anyway, to ensure that the primary
>> knows that it has transmitted all the up to date WALs, I went into the
>> primary's data/pg_xlog/archive_status directory and performed "touch
>> 00000003000000900000036.ready" and repeated this command for the other
>> WALs up to *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 and transmitted them to the standbys. At least I
>> saw them appear on the standby in the directory specified in the
>> recovery.conf file.
>>
>> Now, what I really don't understand is the standby's behavior. After
>> the WALs arrived, I saw nothing in today's pg_log/Wed.log file showing
>> it saw them. I then issued a service 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 000000003000000009000=
00035, 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=
 newly arrived WAL files, and second. what's with the FATAL: in the logfile?
> I'd suggest you ...
>
> 1. Toss out that standby instance.
> 2. Re-read all manual sections regarding hot backup/PITR/streaming
>     replication etc.
> 3. Start fresh.
>> --
> I would not trust a standby instance after possibly corrupting it by
> having to frob the .ready files on master.
>
> A standby server configured as hybrid streamer/WAL shipper should...
>
> 1. Stream and/or read WAL segments from master's xlog directory when
>     wal_keep_segments permits it.
> 2. Fetch WALs from a remote repository when it can't get  a feed
>     directly from master.
>
> There is no manual touching of .ready files needed and I can imagine
> doing so   could be harmful in certain situations.
>
> HTH
>
>
>
>> 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 compre=
ssed.  You may have to restart the standby instance to get the recovery to =
begin though.  I=92d suggest tailing your instance log and restarting the s=
tandby instance.  It should show that the logs from the gap are applying ri=
ght 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 specif=
ieddirectory. The standby servers seem fine with that, however, just copyin=
g does not inform the primary that
>>>> the copy has happened. The archive_status directory under pg_xlog on t=
he primary still thinks the last WAL sent was *B7 and yet it's now writing =
*C9. 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=
 know if this is from an in-memory
>>>> queue or if it Postgresql reads the contents of this regularly in orde=
r 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 yo=
u place them in the correct location (directory designated for restore) the=
instance will automatically apply them all.
>>>>>
>>>>>
>>>>> Andrew Krause
>>>>>
>>>>>
>>>>>
>>>>> On Jun 23, 2014, at 9:24 AM, John Scalia <jayknowsunix@gmail.com> wro=
te:
>>>>>
>>>>>> Came in this morning to numerous complaints from pgpool about the st=
andby servers being behind from the primary. Looking into it, no WAL files =
had been transferred since late Friday. All I did was restart the primaryan=
d the WAL archving resumed, however, looking at the WAL files on the standb=
y servers, this is never going to catch up. Now, I've got the archive_timeo=
ut on the primary =3D 600 or 10 minutes and I see WAL files in pg_xlog ever=
y 10 minutes. As they show up on the standby servers, they're also 10 minut=
es apart, but the primary is writing *21 and the standby's areonly up to *1=
0. Now, like I said prior, with there being 10 minutes (600seconds) between=
 transfers (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 =
files over to the standbys without waiting 10 minutes to copy them one at a=
 time?
>>>>>> --
>>>>>> 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