Обсуждение: FW: vacuumlo

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.

Ian Dauncey <Ian.Dauncey@bankzero.co.za> writes:
> I did run the vacuumlo  against the pg_largeobject table without any issues but afterwards I ran a vacuum full
againstthis 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
accessingthe database) on the table I had all the applications hanging. The next issue was it started writing out WAL
logsand 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
tabledid not get deleted. I did rerun the vacuum full against the pg_largeobject table (and yes, I did stop all the
applicationsfirst). It did complete successfully but it did not drop the previous temporary table. This table is taking
closeto 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