Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WN11c-0003v5-Jn for pgsql-admin@arkaria.postgresql.org; Mon, 10 Mar 2014 14:17:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WN11b-0003Um-Uk for pgsql-admin@arkaria.postgresql.org; Mon, 10 Mar 2014 14:17:51 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WN11a-0003US-Lc for pgsql-admin@postgresql.org; Mon, 10 Mar 2014 14:17:50 +0000 Received: from nm19-vm1.bullet.mail.ne1.yahoo.com ([98.138.91.56]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WN11X-0000No-DY for pgsql-admin@postgresql.org; Mon, 10 Mar 2014 14:17:50 +0000 Received: from [98.138.100.111] by nm19.bullet.mail.ne1.yahoo.com with NNFMP; 10 Mar 2014 14:17:46 -0000 Received: from [98.138.226.161] by tm100.bullet.mail.ne1.yahoo.com with NNFMP; 10 Mar 2014 14:17:46 -0000 Received: from [127.0.0.1] by omp1062.mail.ne1.yahoo.com with NNFMP; 10 Mar 2014 14:17:46 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 293542.68659.bm@omp1062.mail.ne1.yahoo.com Received: (qmail 7774 invoked by uid 60001); 10 Mar 2014 14:17:46 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1394461066; bh=7Wnaf3iVE1pRlIcNHKbtW0CmhAyQ4jANNVwvu8uvt6U=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=o8AOlJe4VfhlyJn6DtQBo4s7/7iGAgOrxEfRVz2GnhQpYUtTZaX1s73mezGHoH4JCQbvozBlP2BcJ8NPAA1I/QUYsjLRn8fxXGgkd0+sUIYQKpXAVE72+SYmGv8Eqpzlie0XEr5M2OS4ufUugfAUHOMWgN+7vUVe4MqRcpP4qvI= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=D9oXLKN9M6Johce0UHV+6xGMGnRFaueD/PiWqQUa7FPQPYvdo2oT9mhN00gyzqBIGI4wWCITV7sNWVNghZe/DKjhVbjQWWCJVr7bbxfPfxHd+LFmg2pj18MdHPscEET7hoAcTDWuWYoFQ3g2zCEoj8PqnDUfoBn4eBLrHE/fQIQ=; X-YMail-OSG: z_wkik0VM1nG6Q0ArUlcliQB1ooQmkvcHXXmSPg8riP.dK2 8f2nKHZuaA0gzmcerU05wZ6Y9myyoTGM_j_eq4Bk5lO0P6F43szylGkoFgHw Wo_u6444oIMbQX3U8KC9du4_eNQnD1BsYTnTI36mR_Jfcx2TUlhG.MpKfslh 15lPtv_34RYjpW9zPN17qHzn3XTv1J3gY1W5kbtA92JLWugCQTpzKfq7zZUN fxI3epnzl1c9cqDQt4iFhyCD_QZpNiSBquGVGuWnfqlF2ya2YcdusBiSMlS. 5vHA5vLZdJ7YpBfdeMgZ5p.yxo8HVFIu1Sao7FC9zrwfPKljyXUe7kGs5xR7 pjevRlaydM.u83SEom_KSKvMLO1yOoa9t_jxsdajWJJk4WuRqDUepg1WoxCJ nQSAoXHgiz5uuLRvtWILHgFKZb4aTznmmq.pJ.C3Q0fgApSpDJug5NNT8Z.U sYn6z9jnEtT4NhoMqL1MG3MLmusgBI3xW92paSh4iedMAK8iSoJfD3tNMpfI NUCMpVanSqZA3hNgir6zMZ4VOIbRS_mVRg8sPFKcIVR.ns0MB0Iy2AxKPINY WF_Ho3FBBz2GQDw-- Received: from [64.236.128.42] by web120303.mail.ne1.yahoo.com via HTTP; Mon, 10 Mar 2014 07:17:46 PDT X-Rocket-MIMEInfo: 002.001, CgpUaGFuayB5b3UgZm9yIHlvdXIgcmVzcG9uc2UuCkknbSBmb2xsb3dpbmcgdGhlIGh5YnJpZCBtb2RlbCAtIG9uZSB0aGluZyBJJ3ZlIG5vdGljZWQgaXMgd2hlbiB0aGUgc3RhbmRieXMgZmFsbCBiYWNrIHRvIFdBTCBzaGlwcGluZywgdGhleSBzdG9wIHRha2luZyByZWFkIHJlcXVlc3RzIChpbiB0aGlzIGNhc2Ugd2Ugd291bGQndmUgbG9zZSBhbG1vc3QgaGFsZiB0aGUgY2FwYWNpdHkpLCBpcyB0aGVyZSBzb21ldGhpbmcgdGhhdCBJIGNhbiBkbyB0byBwcmV2ZW50IHRoYXQ_ClRoZSB3YWxfa2VlcF9zZWcBMAEBAQE- X-Mailer: YahooMailWebService/0.8.177.636 References: <1394153429.37920.YahooMailNeo@web120301.mail.ne1.yahoo.com> <86wqg6os2k.fsf@jerry.enova.com> Message-ID: <1394461066.49064.YahooMailNeo@web120303.mail.ne1.yahoo.com> Date: Mon, 10 Mar 2014 07:17:46 -0700 (PDT) From: pgorg_aav Reply-To: pgorg_aav Subject: Re: streaming replication sent location not advancing - feature request? To: Jerry Sievers Cc: "pgsql-admin@postgresql.org" In-Reply-To: <86wqg6os2k.fsf@jerry.enova.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="983643977-1399473399-1394461066=:49064" X-Pg-Spam-Score: 1.7 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --983643977-1399473399-1394461066=:49064 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable =0A=0AThank you for your response.=0AI'm following the hybrid model - one t= hing I've noticed is when the standbys fall back to WAL shipping, they stop= taking read requests (in this case we would've lose almost half the capaci= ty), is there something that I can do to prevent that?=0AThe wal_keep_segme= nts was slowly increased to 8000 during this issue.=0A=0AHence my "feature = request?" comment...=0ALike mysql (advance apologies for mentioning it :) )= has a slave_io_thread, can one be implemented natively in PostgreSQL?=0AOr= is that left to the individual to implement in the restore_command.=0AI'm = going to try to play around more with the restore command.=0A=0A=0AThanks,= =0AAmit=0A=0A=0A=0AOn Friday, March 7, 2014 12:12 PM, Jerry Sievers wrote:=0A =0Apgorg_aav = writes:=0A>=0A>> Hi,=0A>>=0A>> With wal shipping it is possible to pull th= e WAL files in advance from the master.=0A>> Is it possible to do something= similar for streaming replication?=0A>=0A>wal_keep_segments=A0 =0A>=0A>Otherwise just use the already supported hybrid of WAL archi= ving and=0A>streaming.=0A>=0A>> This morning due to VM hardware issues, a c= ouple of standbys lagged (A LOT) behind master=0A>>=0A>> # select * from pg= _stat_replication ;=0A>>=A0 pid=A0 | usesysid | usename=A0 | application_n= ame |=A0 client_addr=A0 | client_hostname | client_port |=A0 =A0 =A0 =A0 b= ackend_start=A0 =A0 =A0 =A0 |=A0 state=A0 | sent_location |=0A>> write_l= ocation | flush_location | replay_location | sync_priority | sync_state=0A>= > -------+----------+----------+------------------+---------------+--------= ---------+-------------+-------------------------------+-----------+-------= --------+----------------+----------------+-----------------+--------------= -+------------=0A>> ...=3Dx8 snipped =0A>>=A0 1758 |=A0 =A0 =A0 10 | post= gres | walreceiver=A0 =A0 =A0 | 172.26.149.47 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 |=A0 =A0 =A0 34798 | 2014-02-04 14:48:51.318344-05 | streaming | 1D0/= C49A9BE0=A0 | 1D0/=0A>> C49A9BE0=A0 | 1D0/C49A9BE0=A0 | 1D0/C49A9B38=A0 = =A0 |=A0 =A0 =A0 =A0 =A0 =A0 0 | async=0A>>=A0 16072 |=A0 =A0 =A0 10 | po= stgres | walreceiver=A0 =A0 =A0 | 172.26.115.48 |=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 |=A0 =A0 =A0 57152 | 2013-12-07 09:25:21.713723-05 | streaming | = 1C8/11B40000=A0 | 1C8/=0A>> 119A0000=A0 | 1C8/11900000=A0 | 1C8/117EDAB8= =A0 =A0 |=A0 =A0 =A0 =A0 =A0 =A0 0 | async=0A>> (9 rows)=0A>>=0A>> Thanks = much in advance,=0A>> Amit=0A>>=0A>=0A>-- =0A>Jerry Sievers=0A>Postgres DBA= /Development Consulting=0A>=0A>e: postgres.consulting@comcast.net=0A>p: 312= .241.7800=0A>=0A>=0A>-- =0A>Sent via pgsql-admin mailing list (pgsql-admin@= postgresql.org)=0A>To make changes to your subscription:=0A>http://www.post= gresql.org/mailpref/pgsql-admin=0A>=0A>=0A>=0A> --983643977-1399473399-1394461066=:49064 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

Thank you for you= r response.
I'm following the hybrid model - one thing I've notic= ed is when the standbys fall back to WAL shipping, they stop taking read re= quests (in this case we would've lose almost half the capacity), is there s= omething that I can do to prevent that?
The wal_keep_segments was= slowly increased to 8000 during this issue.

Hence= my "feature request?" comment...
Like mysql (advance apologies f= or mentioning it :) ) has a slave_io_thread, can one be implemented nativel= y in PostgreSQL?
Or is that left to the individual to implement i= n the restore_command.
I'm going to try to play around more with = the restore command.

Thanks,
Amit


On Friday, Marc= h 7, 2014 12:12 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
=
pgorg_aav <k= ahitarich-postgresorg@yahoo.com> writes:

> Hi,
>
> With wal s= hipping it is possible to pull the WAL files in advance from the master.
> Is i= t possible to do something similar for streaming replication?

wal_keep_segments  <something higher>
Otherwise just use the already supported= hybrid of WAL archiving and
streaming.

> This morning due to VM hardware issues, a couple o= f standbys lagged (A LOT) behind master
>
> # select * from pg_stat_replication ;
>&nbs= p; pid  | usesysid | usename  | application_name |  client_= addr  | client_hostname | client_port |        ba= ckend_start        |  state  | sent_locati= on |
> write_location | flush_location | replay_locati= on | sync_priority | sync_state
> -------+----------+----------+------------------+---------------+---------= --------+-------------+-------------------------------+-----------+--------= -------+----------------+----------------+-----------------+---------------= +------------
> ...=3Dx8 snipped
&g= t;  1758 |      10 | postgres | walreceiver  &nb= sp;   | 172.26.149.47 |             = ;   |      34798 | 2014-02-04 14:48:51.318344-05 | st= reaming | 1D0/C49A9BE0  | 1D0/
> C49A9BE0  = | 1D0/C49A9BE0  | 1D0/C49A9B38    |      &nb= sp;     0 | async
>  16072 |  &nb= sp;   10 | postgres | walreceiver      | 172.26.115.48= |                |    &= nbsp; 57152 | 2013-12-07 09:25:21.713723-05 | streaming | 1C8/11B40000&nbs= p; | 1C8/
> 119A0000  | 1C8/11900000  | 1C8/= 117EDAB8    |            0 | async=
> (9 rows)
>
&= gt; Thanks much in advance,
> Amit
&= gt;

--
Jerry Siever= s
Postgres DBA/Development Consulting

e:
postgres.consulting@comcast.net
p: 312.241.7800


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



--983643977-1399473399-1394461066=:49064--