RE: vacuumlo

Hi,

Just an update on my vacuumlo issue.

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. 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.

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?
2. Is there a way of finding out the name of this temp table and matching it up to files on disk?

Any help will be appreciated

Regards
Ian.

From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Sent: Tuesday, 17 August 2021 14:18
To: Ian Dauncey <Ian(dot)Dauncey(at)bankzero(dot)co(dot)za>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: vacuumlo

External email - treat with caution
Hi,

On Tue, Aug 17, 2021 at 7:52 PM Ian Dauncey <Ian(dot)Dauncey(at)bankzero(dot)co(dot)za<mailto:Ian(dot)Dauncey(at)bankzero(dot)co(dot)za>> wrote:
>
> I need to run the vacuumlo command against our production database.
>
> Being a PostgresQL database utility, it should be 100% safe to run and should not delete/drop active data.

It's safe as long as you're aware of what this tool is doing. As
mentioned in https://www.postgresql.org/docs/current/vacuumlo.html<https://www.postgresql.org/docs/current/vacuumlo.html>

> vacuumlo is a simple utility program that will remove any “orphaned” large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database.

So:

> I have run it in our QA environment with success, but now they are having a few application issues and I have told them that the issues cannot be related to the vacuumlo utility as it is a PostgresQL utility.

The most likely explanation is that your database somehow has large
object that are not referenced in an "oid" or "lo" column. If that's
the case, vacuumlo will delete some of your data, as you didn't you
your part of the contract required to use that tool, which is to
properly reference large objects reference.

Disclaimer

The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been automatically archived by Mimecast, a leader in email security and cyber resilience. Mimecast integrates email defenses with brand protection, security awareness training, web security, compliance and other essential capabilities. Mimecast helps protect large and small organizations from malicious activity, human error and technology failure; and to lead the movement toward building a more resilient world. To find out more, visit our website.