Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gNIwL-0004bI-95 for pgsql-admin@arkaria.postgresql.org; Thu, 15 Nov 2018 14:48:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gNIwJ-0005m2-Ev for pgsql-admin@arkaria.postgresql.org; Thu, 15 Nov 2018 14:48:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gNIwJ-0005fa-4A for pgsql-admin@lists.postgresql.org; Thu, 15 Nov 2018 14:48:15 +0000 Received: from mtaout004-public.msg.strl.va.charter.net ([68.114.190.29]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gNIwG-0004Ck-Gm for pgsql-admin@lists.postgresql.org; Thu, 15 Nov 2018 14:48:13 +0000 Received: from impout005 ([68.114.189.20]) by mtaout004.msg.strl.va.charter.net (InterMail vM.9.00.023.01 201-2473-194) with ESMTP id <20181115144810.RGUD3685.mtaout004.msg.strl.va.charter.net@impout005>; Thu, 15 Nov 2018 08:48:10 -0600 Received: from cerberus.crazybean.net ([24.151.10.150]) by impout005 with charter.net id 0EoA1z00C3EE3TL01EoAvT; Thu, 15 Nov 2018 08:48:10 -0600 X-Authority-Analysis: v=2.2 cv=Ts+WvHfh c=1 sm=1 tr=0 a=D2HTw/M6/8MmcHighGO7rg==:117 a=D2HTw/M6/8MmcHighGO7rg==:17 a=JHtHm7312UAA:10 a=Rh5JslpUAAAA:8 a=jPYqsnj06IE2WmP9k1EA:9 a=QEXdDO2ut3YA:10 a=Coz3CPjMlnkTvxgq5eEA:9 a=SZ-SYyMSPxq_KMy4:21 a=_W_S_7VecoQA:10 a=CRjGpuKUiiIuQkCM3vJl:22 Received: from [10.25.227.242] (144.121.139.193.lightower.net [144.121.139.193]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id a84279f3 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Thu, 15 Nov 2018 09:48:10 -0500 (EST) From: Rui DeSousa Message-Id: <52EE6BE4-73B4-4BDB-9CDF-1F1C099BA0AD@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_03C9E1C1-4F38-4C81-BD67-E5BBEBB1E883" Mime-Version: 1.0 (Mac OS X Mail 12.1 \(3445.101.1\)) Subject: Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC in pg_wal "No space left on device" Date: Thu, 15 Nov 2018 09:48:09 -0500 In-Reply-To: Cc: pgsql-admin@lists.postgresql.org To: Achilleas Mantzios References: <838a3914-a078-88eb-ee5e-4059b7d14620@matrix.gatewaynet.com> <0BBC4BB3-4A59-4040-B065-82D09268A4F7@crazybean.net> <48415b2a-ea0f-cf5e-1145-17e8797a6e79@matrix.gatewaynet.com> <739822AC-E20F-493B-8B4F-8AF78A8276D0@crazybean.net> <946FBD41-7BAF-4414-8FB9-C0F16A0680F6@crazybean.net> X-Mailer: Apple Mail (2.3445.101.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_03C9E1C1-4F38-4C81-BD67-E5BBEBB1E883 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 15, 2018, at 2:20 AM, Achilleas Mantzios = wrote: >=20 > On 14/11/18 6:24 =CE=BC.=CE=BC., Rui DeSousa wrote: >>=20 >>> On Nov 14, 2018, at 3:31 AM, Achilleas Mantzios = wrote: >>>=20 >>> Our sysadms (seasoned linux/network guys : we have been working here = for more than 10 yrs) were absolute in that we run no firewall or other = traffic shaping system between the two hosts. (if we did the problem = would manifest itself earlier). Can you recommend what to look for = exactly regarding both TCP stacks ? The subscriber node is a clone of = the primary. We have : >>>=20 >>> # sysctl -a | grep -i keepaliv >>> net.ipv4.tcp_keepalive_intvl =3D 75 >>> net.ipv4.tcp_keepalive_probes =3D 9 >>> net.ipv4.tcp_keepalive_time =3D 7200 >>>=20 >> Those keep alive settings are linux=E2=80=99s defaults and work out = to be 18 hours before the abandon connection is dropped. So, the WAL = receiver should have corrected itself after that time. For reference, I = run terminating abandon session within 15 mins as they take-up valuable = database resources and could potentially hold on to locks, snapshots, = etc. >=20 > Didn't happen. Friday 15:01 + '18 hrs' =3D Saturday 09:01 . The = subscription was stuck on Friday 15:01, unfortunately I didn't keep the = whole output of the pg_stat_subscription (which I took on Sunday = morning). And in Sunday morning the primary, having run out of space, = PANIC'ed . Apparently TCP keep alive is not enabled; looks like we=E2=80=99re = missing the following but don=E2=80=99t look for it :). So the = connection would stick around until it gets a reset packet which could = be days if not longer especially if packets are being dropped. I know = you say there is no firewall; but the upstream server would send a reset = packet if connection was terminated and/or Postgres was down =E2=80=94 = so there is something dropping packets. net.inet.tcp.always_keepalive=3D1 A quick google and it looks like Linux defaults to not enabling keep = alive whereas FreeBSD enables it by default and globally regardless of = application request. For Linux, Postgres will need to request it. You = will need to setup the keep alive parameters in the Postgres = configuration and restart the server. The keep alive setup will allow WAL receiver to detect the broken = connection resulting in it terminating the current connection and = attempt to establish a new connection. --Apple-Mail=_03C9E1C1-4F38-4C81-BD67-E5BBEBB1E883 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Nov 15, 2018, at 2:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

On 14/11/18 6:24 =CE=BC.=CE=BC., = Rui DeSousa wrote:

On Nov 14, 2018, at 3:31 AM, Achilleas Mantzios = <achill@matrix.gatewaynet.com> wrote:

Our sysadms (seasoned linux/network guys : we have been = working here for more than 10 yrs) were absolute in that we run no = firewall or other traffic shaping system between the two hosts. (if we = did the problem would manifest itself earlier).  Can you recommend = what to look for exactly regarding both TCP stacks ? The subscriber node = is a clone of the primary. We have :

# = sysctl -a | grep -i keepaliv
net.ipv4.tcp_keepalive_intvl = =3D 75
net.ipv4.tcp_keepalive_probes =3D 9
net.ipv4.tcp_keepalive_time =3D 7200

Those keep alive settings are linux=E2=80=99s = defaults and work out to be 18 hours before the abandon connection is = dropped.  So, the WAL receiver should have corrected itself after = that time.  For reference, I run terminating abandon session within = 15 mins as they take-up valuable database resources and could = potentially hold on to locks, snapshots, etc.

Didn't happen. Friday 15:01 + '18 hrs' =3D Saturday 09:01 . = The subscription was stuck on Friday 15:01, unfortunately I didn't keep = the whole output of the pg_stat_subscription (which I took on Sunday = morning). And in Sunday morning the primary, having run out of space, = PANIC'ed .

Apparently TCP keep alive is not enabled; looks like we=E2=80=99= re missing the following but don=E2=80=99t look for it :).  So the = connection would stick around until it gets a reset packet which could = be days if not longer especially if packets are being dropped.  I = know you say there is no firewall; but the upstream server would send a = reset packet if connection was terminated and/or Postgres was down =E2=80=94= so there is something dropping packets.

net.inet.tcp.always_keepalive=3D1

A quick google and it = looks like Linux defaults to not enabling keep alive whereas FreeBSD = enables it by default and globally regardless of application request. =  For Linux, Postgres will need to request it. You will need to = setup the keep alive parameters in the Postgres configuration and = restart the server.

The keep alive setup will allow WAL receiver to detect the = broken connection resulting in it terminating the current connection and = attempt to establish a new connection.


= --Apple-Mail=_03C9E1C1-4F38-4C81-BD67-E5BBEBB1E883--