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 1gO7Un-0006dZ-8V for pgsql-admin@arkaria.postgresql.org; Sat, 17 Nov 2018 20:47:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gO7Uk-0001He-VO for pgsql-admin@arkaria.postgresql.org; Sat, 17 Nov 2018 20:47:10 +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 1gO7Uk-0001HW-Nm for pgsql-admin@lists.postgresql.org; Sat, 17 Nov 2018 20:47:10 +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 1gO7Uh-0003GM-NY for pgsql-admin@lists.postgresql.org; Sat, 17 Nov 2018 20:47:10 +0000 Received: from impout002 ([68.114.189.17]) by mtaout004.msg.strl.va.charter.net (InterMail vM.9.00.023.01 201-2473-194) with ESMTP id <20181117204704.SGFQ3685.mtaout004.msg.strl.va.charter.net@impout002>; Sat, 17 Nov 2018 14:47:04 -0600 Received: from cerberus.crazybean.net ([24.151.10.150]) by impout002 with charter.net id 18n31z0053EE3TL018n3YW; Sat, 17 Nov 2018 14:47:04 -0600 X-Authority-Analysis: v=2.2 cv=C6eZ8UH+ 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=p1CM8RNvlCSnha8jAQgA:9 a=D03CFfYsw8zZO0D0:21 a=t57wazjzLQTecwEC:21 a=QEXdDO2ut3YA:10 a=z3lbFsrCN7RlkCfLVVEA:9 a=dwADgmqu1VoiCnoe:21 a=x-5wkpq9c6LuCZa3:21 a=QcOgv7TOIFC1yYxK:21 a=_W_S_7VecoQA:10 a=CRjGpuKUiiIuQkCM3vJl:22 Received: from sauron.crazybean.net (sauron.crazybean.net [IPv6:2001:470:e117:3::36]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id a32fd2d1 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Sat, 17 Nov 2018 15:47:03 -0500 (EST) From: Rui DeSousa Message-Id: <291265BC-C620-4112-87E8-CCF0A0BAAA1B@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_81C168E7-3BF2-41B4-A6DE-DC783DB89E87" 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: Sat, 17 Nov 2018 15:47:02 -0500 In-Reply-To: <356b8aba-6ce1-567c-e0fb-9660bdfc7ebe@matrix.gatewaynet.com> 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> <30938331-D245-4B50-AF3F-51D3EFB71A67@crazybean.net> <356b8aba-6ce1-567c-e0fb-9660bdfc7ebe@matrix.gatewaynet.com> X-Mailer: Apple Mail (2.3445.101.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_81C168E7-3BF2-41B4-A6DE-DC783DB89E87 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 17, 2018, at 6:07 AM, Achilleas Mantzios = wrote: >=20 > You may read the PostgreSQL backend sources (grep for SO_KEEPALIVE), = the code supports KEEPALIVE. >=20 >=20 >=20 Postgres supports it; but the question is it on for the given = connection?=20 > I checked on a bare minimal default installation, (after tweaking the = kernel tunables to smaller values of course), keepalive msgs are sent = and ACK'ed at the specified intervals, checked with wireshark, port = 5432. You should test this yourself. >=20 >=20 >=20 I just configured Postgres with streaming replication using the = following versions and TCP keep alive was enabled by default for the WAL = receiver connection and also psql connections. Linux debian 4.9.0-7-amd64 #1 SMP Debian 4.9.110-3+deb9u2 (2018-08-13) = x86_64 GNU/Linux PostgreSQL 10.6 (Debian 10.6-1.pgdg90+1) on x86_64-pc-linux-gnu, = compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit root@debian:~# netstat -anp --timers | grep -e Timer -e EST | grep -e = Timer -e 5432 Proto Recv-Q Send-Q Local Address Foreign Address = State PID/Program name Timer tcp 0 0 10.6.6.101:47546 10.6.6.100:5432 = ESTABLISHED 989/telnet off (0.00/0/0) tcp 0 0 10.6.6.101:47544 10.6.6.100:5432 = ESTABLISHED 953/psql keepalive (7103.36/0/0) tcp 0 0 10.6.6.101:47542 10.6.6.100:5432 = ESTABLISHED 922/postgres: 10/ma keepalive (7088.03/0/0) As you can see from above; telnet does not enable keep alive on the = connection. I would check the troubled system with the above netstat = command to verify that keep alive is in fact enabled on the WAL receiver = connection. If it=E2=80=99s enabled the connection should have terminated after the = 18 hours and hopefully less now with your new setting. I have no idea = why it wouldn=E2=80=99t terminate and reconnect other than tcp keep live = is either off or a bug in Linux/Postgres. =20= --Apple-Mail=_81C168E7-3BF2-41B4-A6DE-DC783DB89E87 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Nov 17, 2018, at 6:07 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

=20 =20

You may read the PostgreSQL backend = sources (grep for SO_KEEPALIVE), the code supports KEEPALIVE.


Postgres supports it; but the question is it on for the = given connection? 


I checked = on a bare minimal default installation, (after tweaking the kernel tunables to smaller values of course), keepalive msgs are sent and ACK'ed at the specified intervals, checked with wireshark, port 5432. You should test this yourself.



I just configured Postgres with streaming replication = using the following versions and TCP keep alive was enabled by default = for the WAL receiver connection and also psql = connections.

Linux debian = 4.9.0-7-amd64 #1 SMP Debian 4.9.110-3+deb9u2 (2018-08-13) x86_64 = GNU/Linux
PostgreSQL 10.6 (Debian 10.6-1.pgdg90+1) on = x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 = 20170516, 64-bit

root@debian:~# netstat = -anp --timers | grep -e Timer -e  EST | grep -e Timer -e = 5432
Proto Recv-Q Send-Q Local Address         =   Foreign Address         State     =   PID/Program name     Timer
tcp     =    0      0 10.6.6.101:47546     =    10.6.6.100:5432         ESTABLISHED = 989/telnet           off = (0.00/0/0)
tcp        0      0 = 10.6.6.101:47544        10.6.6.100:5432   =       ESTABLISHED 953/psql         =     keepalive (7103.36/0/0)
tcp        0   =    0 10.6.6.101:47542       =  10.6.6.100:5432         ESTABLISHED = 922/postgres: 10/ma  keepalive (7088.03/0/0)


As you can see from above; telnet does = not enable keep alive on the connection.  I would check the = troubled system with the above netstat command to verify that keep alive = is in fact enabled on the WAL receiver connection.

If it=E2=80=99s enabled the connection should have terminated = after the 18 hours and hopefully less now with your new setting.  I = have no idea why it wouldn=E2=80=99t terminate and = reconnect other than tcp keep live is either off or a bug in = Linux/Postgres.


 
= --Apple-Mail=_81C168E7-3BF2-41B4-A6DE-DC783DB89E87--