Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oFH8s-00035I-DM for pgsql-bugs@arkaria.postgresql.org; Sat, 23 Jul 2022 15:34:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oFH8r-0007cM-8e for pgsql-bugs@arkaria.postgresql.org; Sat, 23 Jul 2022 15:34:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oFH8r-0007cD-0V for pgsql-bugs@lists.postgresql.org; Sat, 23 Jul 2022 15:34:09 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oFH8o-0005Ns-LR for pgsql-bugs@lists.postgresql.org; Sat, 23 Jul 2022 15:34:08 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 26NFXtYN883946; Sat, 23 Jul 2022 11:33:57 -0400 From: Tom Lane To: Marco Boeringa cc: Bruce Momjian , pgsql-bugs@lists.postgresql.org Subject: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4? In-reply-to: <6c9bf4fd-f22b-4894-d7c9-30bf68ffcf6d@boeringa.demon.nl> References: <85acb61d-4cbf-7544-c734-429993257547@boeringa.demon.nl> <791d1850-f337-8914-1ba0-512f4e0fef26@boeringa.demon.nl> <2d7ff154-d0db-502d-aca8-a605fafcf395@boeringa.demon.nl> <6c9bf4fd-f22b-4894-d7c9-30bf68ffcf6d@boeringa.demon.nl> Comments: In-reply-to Marco Boeringa message dated "Fri, 22 Jul 2022 23:17:25 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <883944.1658590435.1@sss.pgh.pa.us> Date: Sat, 23 Jul 2022 11:33:55 -0400 Message-ID: <883945.1658590435@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Marco Boeringa writes: > Ok, I found it out using 'sudo baobab'. It is the: > 'var/lib/postgresql/14/main/pg_wal' > folder that is filled up with 890 GB of data... causing the file system > root to run out of space and Ubuntu opening the disk usage analyzer and > a warning as a consequence. The most likely explanations for this are (a) misconfiguration of WAL archiving, so that the server thinks it should keep WAL files till they've been archived, only that never happens. (b) inability to complete checkpoints for some reason, preventing WAL files from being recycled. It doesn't look like you have wal_archiving on, so (a) *should* be ruled out, but you never know. If there are a ton of "nnn.ready" files underneath pg_wal then trouble here would be indicated. As for (b), you might try enabling log_checkpoints and seeing if the log messages give any clue. regards, tom lane