Re: vacuumlo

Hi,

You may restart the postgres services. That temp file will be deleted
automatically then.

Regards
Atul

On Monday, August 30, 2021, Ian Dauncey <Ian(dot)Dauncey(at)bankzero(dot)co(dot)za> wrote:

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