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 1mKkXT-0008D6-OQ for pgsql-general@arkaria.postgresql.org; Mon, 30 Aug 2021 16:53:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mKkXS-0008GL-EO for pgsql-general@arkaria.postgresql.org; Mon, 30 Aug 2021 16:53:38 +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 1mKkXS-0008GD-5g for pgsql-general@lists.postgresql.org; Mon, 30 Aug 2021 16:53:38 +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 1mKkXO-00083c-1H for pgsql-general@postgresql.org; Mon, 30 Aug 2021 16:53:37 +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 17UGrPeI1414882; Mon, 30 Aug 2021 12:53:26 -0400 From: Tom Lane To: Ian Dauncey cc: "pgsql-general@postgresql.org" Subject: Re: FW: vacuumlo In-reply-to: References: Comments: In-reply-to Ian Dauncey message dated "Mon, 30 Aug 2021 16:23:55 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1414880.1630342405.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 30 Aug 2021 12:53:25 -0400 Message-ID: <1414881.1630342405@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ian Dauncey writes: > I did run the vacuumlo against the pg_largeobject table without any iss= ues but afterwards I ran a vacuum full against this table which caused lot= s of issues. > Because the vacuum full takes an exclusive lock (which was my first mist= ake as I did not stop the applications accessing the database) on the tabl= e I had all the applications hanging. The next issue was it started writin= g out WAL logs and in the end the file system which housed the Wal logs fi= lled up causing the vacuum to fail. > Now the issue I have here is that the vacuum full created a temporary ta= ble , and when it crashed this temporary table did not get deleted. I did = rerun the vacuum full against the pg_largeobject table (and yes, I did sto= p all the applications first). It did complete successfully but it did not= drop the previous temporary table. This table is taking close to 100 Gig = of disk space. I think you mean "file", not "temporary table". You're going to have to remove the file by hand, likely, as there is not (I think) any live reference to it in the catalogs. Do select pg_relation_filenode(oid) from pg_class; and then match up the numbers it prints out with the filenames you find in the database's directory. You should find matches to everything except the problem file(s). Once you've identified which is the orphaned file, you can remove it. If there seem to be a lot of orphaned files with different base names, STOP ... you probably are looking at the wrong database or some other mistake. But if there's just one base name that's not accounted for, and the sum of the sizes of the files with that base name looks about right, then you've probably got it right. I strongly suggest reading https://www.postgresql.org/docs/current/storage.html before you go messing with any files manually, so you know what you are looking at. regards, tom lane