Message-ID: <9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel@cybertec.at>
Subject: Re: vacuumlo
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ian Dauncey <Ian.Dauncey@bankzero.co.za>,
	"pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Mon, 30 Aug 2021 19:15:07 +0200
In-Reply-To: 
 <DBAPR08MB5687AE8E62995E251E23E26BC0CB9@DBAPR08MB5687.eurprd08.prod.outlook.com>
References: 
	<DBAPR08MB5687F490FBCC6065B1F6224BC0FE9@DBAPR08MB5687.eurprd08.prod.outlook.com>
	 <CAOBaU_YNAsTVHXS1UFw_XBt2YOZjwsJ3KMnZXrwqKb+86WAhpg@mail.gmail.com>
	 <DBAPR08MB5687AE8E62995E251E23E26BC0CB9@DBAPR08MB5687.eurprd08.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-1.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel%40cybertec.at>
Precedence: bulk

On Mon, 2021-08-30 at 15:08 +0000, Ian Dauncey wrote: 
> I did run the vacuumlo  against the pg_largeobject table without any issues but afterwards
> I ran a vacuum full against this table which caused lots of issues.
> Because the vacuum full takes an exclusive lock (which was my first mistake as I
> did not stop the applications accessing the database) on the table I had all the
> applications hanging.

Right.

> The next issue was it started writing out WAL logs and in the end the file system
> which housed the Wal logs filled up causing the vacuum to fail.
> Now the issue I have here is that the vacuum full created a temporary table , 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 stop 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.

VACUUM (FULL) will write a compact copy of the table, then delete the old one,
so it temporarily needs more space.  It may be a good idea to put "pg_wal" into
a different file system, so that WAL space cannot run out even if the data files
fill the file system.  Then PostgreSQL won't crash, and the files will be removed.
 
> If I backup and restore the database onto a different server this temporary table does not get restored.
> My question here is.
>    1. How do I get rid of this temporary table without a backup and restore as this is our Prod system?

You cannot :^(
If you know PostgreSQL well, and you know what you are doing, you might be able
to identify those files and manually delete them.  But that's definitely not
something for the casual user: delete the wrong file, and you have to restore
the backup.

>    2. Is there a way of finding out the name of this temp table and matching it up to files on disk?

No, unfortunately not.
You'd have to identify the files that do *not* belong to any table, and that
is tricky.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com