From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ian Dauncey <Ian.Dauncey@bankzero.co.za>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: vacuumlo
Date: Mon, 30 Aug 2021 19:15:07 +0200
Message-ID: <9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel@cybertec.at> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, Ian.Dauncey@bankzero.co.za, pgsql-admin@lists.postgresql.org
Subject: Re: vacuumlo
In-Reply-To: <9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox