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 1gMbDO-000344-I4 for pgsql-admin@arkaria.postgresql.org; Tue, 13 Nov 2018 16:06:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gMbDM-0007U6-BK for pgsql-admin@arkaria.postgresql.org; Tue, 13 Nov 2018 16:06:56 +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 1gMbDM-0007Tz-3I for pgsql-admin@lists.postgresql.org; Tue, 13 Nov 2018 16:06:56 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gMbDE-0000KR-L3 for pgsql-admin@lists.postgresql.org; Tue, 13 Nov 2018 16:06:55 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.14.3/8.14.3/Debian-9.4) with ESMTP id wADG6fKE001521 for ; Tue, 13 Nov 2018 18:06:46 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id wADG6fxu013737 for ; Tue, 13 Nov 2018 18:06:41 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC in pg_wal "No space left on device" To: pgsql-admin@lists.postgresql.org References: <838a3914-a078-88eb-ee5e-4059b7d14620@matrix.gatewaynet.com> <0BBC4BB3-4A59-4040-B065-82D09268A4F7@crazybean.net> From: Achilleas Mantzios Message-ID: <48415b2a-ea0f-cf5e-1145-17e8797a6e79@matrix.gatewaynet.com> Date: Tue, 13 Nov 2018 18:06:41 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.0.1 MIME-Version: 1.0 In-Reply-To: <0BBC4BB3-4A59-4040-B065-82D09268A4F7@crazybean.net> Content-Type: multipart/alternative; boundary="------------7F306A111C0570AD91682516" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.98.7 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-38.0 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_IT_PARTNERS,DYN_HAM_NAUTICAL,HTML_MESSAGE autolearn=unavailable version=3.3.1 X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------7F306A111C0570AD91682516 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 13/11/18 5:35 μ.μ., Rui DeSousa wrote: > > >> On Nov 13, 2018, at 7:00 AM, Achilleas Mantzios > wrote: >> >> Is there a way for the WAL receiver to not have detected the termination of the replication stream? > > The teardown of the network socket on the upstream server should send a reset packet to the downstream server and at that point the WAL receiver would close its connection.  Is there any firewalls, > router, rules, etc between the nodes that could have dropped the packet? No > >> >> Shouldn't normally the WAL receiver detect this and try again in wal_retrieve_retry_interval ? > > Not really… if the connection has already been torn down; the upstream server would send another reset packet on the next request and in this case it would.  However, if request packets at not > reaching the upstream server; i.e. due to firewall silently dropping the packets (personally I believe firewall should always set reset packets to friendly hosts) then what happens is the TCP/IP > send queue builds up with the requests packets instead — a t this point waiting on the OS to terminate the connection which can day or two depending on your TCP/IP setting. > Again no dropping, no firewall. > What you want to use instead is wal_receiver_timeout to detect the given case where upstream server either no longer exists or the firewall, etc is silently dropping packets. Once again from my original message : "while setting up logical replication since August we had seen early on the need to increase max_receiver_timeout and max_sender_timeout from 60sec to 5mins" So with wal_receiver_timeout='5 min', the receiver never detected any timeout. > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------7F306A111C0570AD91682516 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 13/11/18 5:35 μ.μ., Rui DeSousa wrote:


On Nov 13, 2018, at 7:00 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

Is there a way for the WAL receiver to not have detected the termination of the replication stream?

The teardown of the network socket on the upstream server should send a reset packet to the downstream server and at that point the WAL receiver would close its connection.  Is there any firewalls, router, rules, etc between the nodes that could have dropped the packet?

No



Shouldn't normally the WAL receiver detect this and try again in wal_retrieve_retry_interval ?

Not really… if the connection has already been torn down; the upstream server would send another reset packet on the next request and in this case it would.  However, if request packets at not reaching the upstream server; i.e. due to firewall silently dropping the packets (personally I believe firewall should always set reset packets to friendly hosts) then what happens is the TCP/IP send queue builds up with the requests packets instead — a t this point waiting on the OS to terminate the connection which can day or two depending on your TCP/IP setting.


Again no dropping, no firewall.

What you want to use instead is wal_receiver_timeout to detect the given case where upstream server either no longer exists or the firewall, etc is silently dropping packets.

Once again from my original message :
"while setting up logical replication since August we had seen early on the need to increase max_receiver_timeout and max_sender_timeout from 60sec to 5mins"

So with wal_receiver_timeout='5 min', the receiver never detected any timeout.





-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------7F306A111C0570AD91682516--