Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gK8bq-0006ua-MQ for pgsql-admin@arkaria.postgresql.org; Tue, 06 Nov 2018 21:10:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gK8bp-0005ht-4A for pgsql-admin@arkaria.postgresql.org; Tue, 06 Nov 2018 21:10:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gK8bo-0005hQ-Sf for pgsql-admin@lists.postgresql.org; Tue, 06 Nov 2018 21:10:00 +0000 Received: from mtaout004-public.msg.strl.va.charter.net ([68.114.190.29]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gK8bl-0000yY-W9 for pgsql-admin@postgresql.org; Tue, 06 Nov 2018 21:10:00 +0000 Received: from impout003 ([68.114.189.18]) by mtaout004.msg.strl.va.charter.net (InterMail vM.9.00.023.01 201-2473-194) with ESMTP id <20181106210952.SLZB3685.mtaout004.msg.strl.va.charter.net@impout003>; Tue, 6 Nov 2018 15:09:52 -0600 Received: from cerberus.crazybean.net ([24.151.10.150]) by impout003 with charter.net id wl9r1y0023EE3TL01l9r7V; Tue, 06 Nov 2018 15:09:52 -0600 X-Authority-Analysis: v=2.2 cv=HqsGIwbS c=1 sm=1 tr=0 a=D2HTw/M6/8MmcHighGO7rg==:117 a=D2HTw/M6/8MmcHighGO7rg==:17 a=xqWC_Br6kY4A:10 a=JHtHm7312UAA:10 a=bmULpPF2AAAA:8 a=xsFC1jerUM9Gqeh0ZK4A:9 a=Th8-ECaUtwRNXDpG:21 a=aOClGmmAmiDvcok4:21 a=QEXdDO2ut3YA:10 a=gFSXUfAGfUv-qAglkSUA:9 a=hxzUxiJ_wgvxomoh:21 a=UTbLMP542dKE7cZb:21 a=at4_DivaptEZmNgl:21 a=_W_S_7VecoQA:10 a=LwXMS3FhJVUSL8bnO8p6:22 Received: from [IPv6:2001:470:e117:4:34e1:48f1:615:a801] (2001:470:e117:4:34e1:48f1:615:a801 [IPv6:2001:470:e117:4:34e1:48f1:615:a801]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id cec1a766 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Tue, 6 Nov 2018 16:09:50 -0500 (EST) From: Rui DeSousa Message-Id: <8BB7D7D1-8AD0-4350-8A1F-D28ABCDF3AB7@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_95781CE1-78D5-40B5-8F95-3066AD83A504" Mime-Version: 1.0 (Mac OS X Mail 12.1 \(3445.100.42\)) Subject: Re: Another streaming replication question Date: Tue, 6 Nov 2018 16:09:49 -0500 In-Reply-To: Cc: pgsql-admin To: Mark Steben References: X-Mailer: Apple Mail (2.3445.100.42) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_95781CE1-78D5-40B5-8F95-3066AD83A504 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 6, 2018, at 12:13 PM, Mark Steben = wrote: >=20 > 1. Can streaming replication work in this high-load situation? Yes, given you have low latency and the bandwidth to support it. =20 Need to plan for cases when streaming does fall behind too; don=E2=80=99t = want streaming to just break. Some options: 1. Replication slot on the upstream sever 2. Allow streaming to fallback to restoring WALs from archive 3. Both of the above options > 2. What, if anything, can I do to make it work better? Perhaps = convert to cascade? Master -->standby1 ---> standby2 That would help in managing you bandwidth between nodes. There is a lot to consider and it really depends on your use cases and = your application/queries. A few rhetorical questions:=20 What is the replicas being used for? =20 What an acceptable replication lag? What an acceptable apply lag? Should queries be killed to apply replica transactions? What=E2=80=99s your vacuum strategy as that impacts replicas? These two parameters I find invaluable (their validity depends on your = use case).=20 hot_standby_feedback =3D true max_standby_streaming_delay =3D 30s The other big thing is query performance and I/O as each has a large = impact on replication lag. So plan for tuning.= --Apple-Mail=_95781CE1-78D5-40B5-8F95-3066AD83A504 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8


On Nov = 6, 2018, at 12:13 PM, Mark Steben <mark.steben@drivedominion.com> wrote:

  =  1.  Can streaming replication work in this high-load = situation?

Yes, = given you have low latency and the bandwidth to support it. =  

Need to plan for cases when = streaming does fall behind too; don=E2=80=99t want streaming to just = break.

Some options:

1. Replication slot on the upstream = sever
2. Allow streaming to fallback to restoring WALs from = archive
3. Both of the above options


   2.  What, if = anything, can I do to make it work better?  Perhaps convert to = cascade? Master -->standby1 ---> = standby2

That would help in managing = you bandwidth between nodes.


There is a lot to consider and it really depends on your use = cases and your application/queries.

A few rhetorical = questions: 

What = is the replicas being used for?  
What an acceptable replication = lag?
What = an acceptable apply lag?
Should queries be killed to apply replica = transactions?
What=E2=80=99s your vacuum strategy as that impacts = replicas?

These= two parameters I find invaluable (their validity depends on your use = case). 

hot_standby_feedback =3D = true
max_standby_streaming_delay =3D 30s

The other big thing is query = performance and I/O as each has a large impact on replication lag. =  So plan for tuning.
= --Apple-Mail=_95781CE1-78D5-40B5-8F95-3066AD83A504--