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 1gO7zH-0008BX-DK for pgsql-admin@arkaria.postgresql.org; Sat, 17 Nov 2018 21:18:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gO7zF-0006PH-TB for pgsql-admin@arkaria.postgresql.org; Sat, 17 Nov 2018 21:18:41 +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 1gO7zF-0006Nx-HW for pgsql-admin@lists.postgresql.org; Sat, 17 Nov 2018 21:18:41 +0000 Received: from mtaout002-public.msg.strl.va.charter.net ([68.114.190.27]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gO7zC-00039n-I5 for pgsql-admin@lists.postgresql.org; Sat, 17 Nov 2018 21:18:40 +0000 Received: from impout005 ([68.114.189.20]) by mtaout002.msg.strl.va.charter.net (InterMail vM.9.00.023.01 201-2473-194) with ESMTP id <20181117211837.EIVV7360.mtaout002.msg.strl.va.charter.net@impout005>; Sat, 17 Nov 2018 15:18:37 -0600 Received: from cerberus.crazybean.net ([24.151.10.150]) by impout005 with charter.net id 19Jd1z0013EE3TL019Jd0A; Sat, 17 Nov 2018 15:18:37 -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=xqWC_Br6kY4A:10 a=JHtHm7312UAA:10 a=oIfrFNJ-AAAA:8 a=Rh5JslpUAAAA:8 a=A_ArwYvvkxsLfLCF59YA:9 a=YO9W4YKrevQtOpX0:21 a=IJqMRnzIjwiJcgB-:21 a=QEXdDO2ut3YA:10 a=FLHy6Wt0pcMOmtl05IUA:9 a=iI2jFPVm401NaERR:21 a=0cHyEG70zfKZmRAw:21 a=t8xb5XQc6ZNDC5Ss:21 a=_W_S_7VecoQA:10 a=Cg2NcamS17xU0rR7el6q:22 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 e728d10d (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Sat, 17 Nov 2018 16:18:37 -0500 (EST) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_098762A5-ECF7-4637-91C2-CF989835DBF1" 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 16:18:36 -0500 In-Reply-To: <291265BC-C620-4112-87E8-CCF0A0BAAA1B@crazybean.net> 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> <291265BC-C620-4112-87E8-CCF0A0BAAA1B@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=_098762A5-ECF7-4637-91C2-CF989835DBF1 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 17, 2018, at 3:47 PM, Rui DeSousa wrote: >=20 >=20 >=20 >> 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 >=20 >=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 >=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. >=20 > 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 >=20 > 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) >=20 >=20 > 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. >=20 > 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 >=20 > =20 I will also add that testing this configuration worked as expected. = When disconnecting the primary node from the network; the Postgres = processes where able to detect the fault and WAL receiver reconnected as = soon as the primary was back on line without issue. What is the version of Postgres and OS are you using?= --Apple-Mail=_098762A5-ECF7-4637-91C2-CF989835DBF1 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Nov 17, 2018, at 3:47 PM, Rui DeSousa <rui@crazybean.net> = wrote:



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.


 

I will also add that testing this = configuration worked as expected.  When disconnecting the primary = node from the network; the Postgres processes where able to detect the = fault and WAL receiver reconnected as soon as the primary was back on = line without issue.

What is the version of Postgres and OS are you = using?
= --Apple-Mail=_098762A5-ECF7-4637-91C2-CF989835DBF1--