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 1gNg3f-0007LO-DW for pgsql-admin@arkaria.postgresql.org; Fri, 16 Nov 2018 15:29:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gNg3c-00014Y-KA for pgsql-admin@arkaria.postgresql.org; Fri, 16 Nov 2018 15:29:20 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1gNg3c-00014R-CC for pgsql-admin@lists.postgresql.org; Fri, 16 Nov 2018 15:29:20 +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 1gNg3U-0002A4-Ob for pgsql-admin@lists.postgresql.org; Fri, 16 Nov 2018 15:29:19 +0000 Received: from impout001 ([68.114.189.16]) by mtaout004.msg.strl.va.charter.net (InterMail vM.9.00.023.01 201-2473-194) with ESMTP id <20181116152910.CSLX3685.mtaout004.msg.strl.va.charter.net@impout001>; Fri, 16 Nov 2018 09:29:10 -0600 Received: from cerberus.crazybean.net ([24.151.10.150]) by impout001 with charter.net id 0fV81z0033EE3TL01fV8Cj; Fri, 16 Nov 2018 09:29:10 -0600 X-Authority-Analysis: v=2.2 cv=ffHd8wYF c=1 sm=1 tr=0 a=D2HTw/M6/8MmcHighGO7rg==:117 a=D2HTw/M6/8MmcHighGO7rg==:17 a=xqWC_Br6kY4A:10 a=JHtHm7312UAA:10 a=Rh5JslpUAAAA:8 a=bVP7E8VSAAAA:8 a=Q6Ojrw04AAAA:8 a=Pf5Wj_Fgw9w-zU0OuVMA:9 a=QEXdDO2ut3YA:10 a=qr0vJSWK3xYA:10 a=oIfrFNJ-AAAA:8 a=5T9lMo4vYJMdBxoZqJ8A:9 a=xzEhNhNJLsXX-TLu:21 a=_W_S_7VecoQA:10 a=CRjGpuKUiiIuQkCM3vJl:22 a=Ly10aoh6cllcPZKPqPQE:22 a=Cg2NcamS17xU0rR7el6q:22 Received: from [IPv6:2001:470:e117:4:9539:77d5:cebf:e680] (2001:470:e117:4:9539:77d5:cebf:e680 [IPv6:2001:470:e117:4:9539:77d5:cebf:e680]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 556d325a (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Fri, 16 Nov 2018 10:29:07 -0500 (EST) From: Rui DeSousa Message-Id: <30938331-D245-4B50-AF3F-51D3EFB71A67@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_E389BFCF-B6A4-491C-B2A3-0CB85342A773" Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\)) Subject: Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC in pg_wal "No space left on device" Date: Fri, 16 Nov 2018 10:29:07 -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> <52EE6BE4-73B4-4BDB-9CDF-1F1C099BA0AD@crazybean.net> X-Mailer: Apple Mail (2.3445.102.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_E389BFCF-B6A4-491C-B2A3-0CB85342A773 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 16, 2018, at 3:18 AM, Achilleas Mantzios = wrote: >=20 >> net.inet.tcp.always_keepalive=3D1 >=20 > This setting is from FreeBSD. I have tested changing the settings on = my PostgreSQL 11.1 on my FreeBSD 11.2-RELEASE-p3, and this would have no = effect at all to the postgresql settings, they remained all three of = them at zero. This is completely irrelevant with my problem but anyway. >=20 That is what I stated; you don=E2=80=99t need it. It is that in Linux = the application has to enable it and I don=E2=80=99t know of a kernel = setting for Linux like the one in FreeBSD >>=20 >> 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. >=20 > http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html = > So according to the official Linux docs, three are the parameters that = govern TCP keepalive in Linux, which in both the said systems are set as = : > root@TEST-smadb:/var/lib/pgsql# sysctl -a | grep keep > net.ipv4.tcp_keepalive_intvl =3D 75 > net.ipv4.tcp_keepalive_probes =3D 9 > net.ipv4.tcp_keepalive_time =3D 7200 > root@TEST-smadb:/var/lib/pgsql#=20 >=20 That does not mean the connection has TCP keep alive enabled; it just = means that if an application requests it those would be the defaults = setting if it doesn=E2=80=99t provide its own. Those setting would be = too large anyway; you want to be able to detect a broken connection much = quicker than 18 hours. >>=20 >> 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. >=20 > So from looks of this, keep alive is enabled. (Also don't confuse WAL = receiver with logical worker, different programs, albeit similar). I don=E2=80=99t believe it=E2=80=99s enabled; have you check to see that = you getting keep alive packets? If it was enabled it would have = terminated after 18 hours. > Is there any way (by network means?) to mock this behavior in order to = fool the replication worker like the sender is there? Put a firewall in-between the servers and drop the packets without = sending resets. Have a read here: Section 4.2 http://www.tldp.org/HOWTO/html_single/TCP-Keepalive-HOWTO/ = The RFC states TCP keep alive should be off by default; FreeBSD changed = that back in 1999 and I believe Linux still follows the RFC: = https://serverfault.com/questions/671710/why-does-freebsd-net-inet-tcp-alw= ays-keepalive-violate-rfc1122#671749 = --Apple-Mail=_E389BFCF-B6A4-491C-B2A3-0CB85342A773 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Nov 16, 2018, at 3:18 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

=20 =20
net.inet.tcp.always_keepalive=3D1

This setting is from FreeBSD. I have tested changing the settings on my PostgreSQL 11.1 on my FreeBSD 11.2-RELEASE-p3, and this would have no effect at all to the postgresql settings, they remained all three of them at zero. This is completely irrelevant with my problem but anyway.


That is what I stated; you don=E2=80=99t need it. =  It is that in Linux the application has to enable it and I don=E2=80= =99t know of a kernel setting for Linux like the one in FreeBSD


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.

htt= p://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html
So according to the official Linux docs, three are the parameters that govern TCP keepalive in Linux, which in both the said systems are set as :
root@TEST-smadb:/var/lib/pgsql# sysctl -a | grep keep
net.ipv4.tcp_keepalive_intvl =3D 75
net.ipv4.tcp_keepalive_probes =3D 9
net.ipv4.tcp_keepalive_time =3D 7200
root@TEST-smadb:/var/lib/pgsql#


That does not mean the connection has TCP keep = alive enabled; it just means that if an application requests it those = would be the defaults setting if it doesn=E2=80=99t provide its own. =  Those setting would be too large anyway; you want to be able to = detect a broken connection much quicker than 18 hours.


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.

So from looks of this, keep alive is enabled. (Also don't confuse WAL receiver with logical worker, different programs, albeit similar).

I don=E2=80=99t believe it=E2=80=99s enabled; have = you check to see that you getting keep alive packets?  If it was = enabled it would have terminated after 18 hours.

Is there any way (by = network means?) to mock this behavior in order to fool the replication = worker like the sender is there?

Put a firewall in-between the servers and = drop the packets without sending resets.


Have= a read here:

Section 4.2

http://www.tldp.org/HOWTO/html_single/TCP-Keepalive-HOWTO/<= /div>

The RFC = states TCP keep alive should be off by default; FreeBSD changed that = back in 1999 and I believe Linux still follows the RFC:

https://serverfault.com/questions/671710/why-does-freebsd-net-i= net-tcp-always-keepalive-violate-rfc1122#671749


= --Apple-Mail=_E389BFCF-B6A4-491C-B2A3-0CB85342A773--