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 1gMClp-0001zL-6K for pgsql-admin@arkaria.postgresql.org; Mon, 12 Nov 2018 14:00:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gMCln-0003sn-2g for pgsql-admin@arkaria.postgresql.org; Mon, 12 Nov 2018 14:00:51 +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_SHA384:256) (Exim 4.89) (envelope-from ) id 1gMClm-0003sZ-Qe for pgsql-admin@lists.postgresql.org; Mon, 12 Nov 2018 14:00:50 +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 1gMClj-00061j-P9 for pgsql-admin@lists.postgresql.org; Mon, 12 Nov 2018 14:00:50 +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 wACE0egE025698; Mon, 12 Nov 2018 16:00:45 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id wACE0d0a026723; Mon, 12 Nov 2018 16:00:40 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: PostgreSQL 10.5 : Strange pg_wal fill-up, solved with the shutdown checkpoint To: pgsql-admin@lists.postgresql.org References: <0d25bb8c-9cc7-10bc-71ba-6a8a8c33ec49@matrix.gatewaynet.com> <46D70340-2172-406A-A346-B2D4C6E947E4@crazybean.net> <6031b670-52d1-b823-6b41-35d2fa1873c6@matrix.gatewaynet.com> <23169.1541516303@sss.pgh.pa.us> From: Achilleas Mantzios Message-ID: <91736c68-3837-cf51-19cb-11fe0f096cef@matrix.gatewaynet.com> Date: Mon, 12 Nov 2018 16:00:39 +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: <23169.1541516303@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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,DYN_HAM_SIGNAT1 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 On 6/11/18 4:58 μ.μ., Tom Lane wrote: > Achilleas Mantzios writes: >> Remember : postgresql checkpointer decided to remove 5000+ files before shutdown. If any conditions were keeping those files afloat should also hold at this point, right. >> The question is why didn't Postgresql removed them earlier. > WAL files get removed/recycled after completion of a checkpoint. So > apparently, checkpoints were not finishing during normal operation, > but the shutdown checkpoint managed to terminate normally. That > eliminates a lot of the usual theories about why checkpoints might > not be succeeding (like a dirty buffer that always fails to be > written, say as a result of broken permissions on its file). > > The only theory that comes to mind is that the checkpointer process > was stuck somehow, but just "soft" stuck, in a way that allowed the > postmaster's time-to-shut-down-please signal to unstick it. No, > I have no idea how that could happen exactly. If it happens again, > it'd be really interesting to attach to the checkpointer with a > debugger and collect a stack trace. Hello Tom, I enabled debugging as you suggested. In the meantime yesterday (also Sunday) we had an identical incident as far symptoms are concerned , only that in this (yesterday) case I fully explained it, and this is reported here : https://www.postgresql.org/message-id/ae8812c3-d138-73b7-537a-a273e15ef6e1%40matrix.gatewaynet.com What happened is that walsender died on Friday, replication slot stuck, server kept wal files, pg_wal disk was full (on Sunday morning), system PANIC'ed : 10.9.0.77(48650) [65253] 5be2ca1d.fee5 2018-11-09 15:06:11.052 EET data_for_testsmadb_pub repmgr(at)dynacom line:9 LOG:  terminating walsender process due to replication timeout 10.9.0.77(48650) [65253] 5be2ca1d.fee5 2018-11-09 15:06:11.052 EET data_for_testsmadb_pub repmgr(at)dynacom line:10 CONTEXT:  slot "data_for_testsmadb_pub", output plugin "pgoutput", in the change callback, associated LSN 13DF/393BF7F0 10.9.0.77(48650) [65253] 5be2ca1d.fee5 2018-11-09 15:06:11.066 EET data_for_testsmadb_pub repmgr(at)dynacom line:11 LOG: disconnection: session time: 49:47:17.937 user=repmgr database=dynacom host=10.9.0.77 port=48650 (so this problem appeared 2 days after walsender died) So, I tried to find evidence that would connect the two incidents (this and previous Sunday) but could not find anything in either the publisher (primary) or the subscriber logs that would match wal sender or logical replication worker terminate or exit. *but* I found this on the primary's log back from 2018-11-01 : 10.9.0.77(47489) [73151] 5bd96f82.11dbf 2018-11-01 16:51:58.424 EET data_for_testsmadb_pub repmgr@dynacom line:9 LOG:  could not send data to client: Connection reset by peer 10.9.0.77(47489) [73151] 5bd96f82.11dbf 2018-11-01 16:51:58.424 EET data_for_testsmadb_pub repmgr@dynacom line:10 CONTEXT:  slot "data_for_testsmadb_pub", output plugin "pgoutput", in the commit callback, associated LSN 1290/6ADB518 10.9.0.77(47489) [73151] 5bd96f82.11dbf 2018-11-01 16:51:58.434 EET data_for_testsmadb_pub repmgr@dynacom line:11 LOG:  disconnection: session time: 29:50:03.592 user=repmgr database=dynacom host=10.9.0.77 port=47489 10.9.0.77(47612) [126667] 5bdb130e.1eecb 2018-11-01 16:51:58.476 EET [unknown] [unknown]@[unknown] line:1 LOG:  connection received: host=10.9.0.77 port=47612 10.9.0.77(47612) [126667] 5bdb130e.1eecb 2018-11-01 16:51:58.477 EET [unknown] repmgr@dynacom line:2 LOG:  replication connection authorized: user=repmgr 10.9.0.77(47612) [126667] 5bdb130e.1eecb 2018-11-01 16:51:58.478 EET data_for_testsmadb_pub repmgr@dynacom line:3 LOG:  received replication command: IDENTIFY_SYSTEM ...... above pattern repeats 9 more times (so this problem appeared 3 days after this connection reset) In this case the wal sender did not terminate, but I wonder if it caused this "soft" stuck in the replication slot? In this primary server we have only one replication slot (the one to the logical subscriber node), and archiving works without problems for years. So I cannot think of another reason that wals were accumulating in pg_wal . > > regards, tom lane > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt