Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKLA2-0001by-GJ for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 10:34:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKL9z-0005D9-If for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 10:34:07 +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 1gKL9z-0005D2-B2 for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 10:34:07 +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 1gKL9v-0002W6-Ox for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 10:34:06 +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 wA7AXu3L007994 for ; Wed, 7 Nov 2018 12:34:01 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id wA7AXurO007122 for ; Wed, 7 Nov 2018 12:33:56 +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: <93155f7d-21e3-7442-5d76-acdaf57f0dae@matrix.gatewaynet.com> Date: Wed, 7 Nov 2018 12:33:56 +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. Thank you Tom. Should I also re-run configure --enable-debug && make clean install ? Initially PostgreSQL was built without --enable-debug, so currently it does not show source code line numbers in gdb bt . > > regards, tom lane > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt