Delete all Large Objects from PostgreSQL database

Question

I am trying to remove all large objects from a PostgreSQL database.

We work with large, image datasets. Originally, these were stored as Large Objects in postgres, along with their metadata. I am in the process of converting these databases into just storing file system references to the image in order to better manage the, sometimes conflicting, disk requirements of databases versus image data.

After exporting all of the Large Objects, creating references to them and testing, I am ready to delete the Large Objects.

I have tried to write a function which will allow me to delete all of them, but to no avail. This seemed to work but because not every number in the range corresponds to a large object, it fell over.

CREATE OR REPLACE FUNCTION "DeleteLOs"() RETURNS INTEGER AS
$$
DECLARE

BEGIN 

FOR i IN 1620762..1801116 LOOP
SELECT lo_unlink(i);

END LOOP;
RETURN 0;

END;
$$
LANGUAGE plpgsql;

Ideally, I would be able to pair a function like this with a query to ensure I got them all, rather than specifying a range which might not be complete:

SELECT DISTINCT loid FROM pg_largeobject

Community · Accepted Answer · 2017-04-13 12:42:36Z

12

To delete all existing large objects in your database:

SELECT lo_unlink(l.oid) FROM pg_largeobject_metadata l;

Related answer on dba.SE with more details:

Can I do VACUUM FULL to pg_largeobject table?

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Oct 15, 2015 at 4:52

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WonderWorker · Accepted Answer · 2017-03-28 15:30:53Z

4

Erwin Brandstetter's answer implemented like this:

SELECT lo_unlink(l.oid) FROM pg_largeobject_metadata l;

vacuum full

cluster

edited Mar 28, 2017 at 15:30

WonderWorker

9,2025 gold badges70 silver badges75 bronze badges

answered Mar 28, 2017 at 14:45

Skysnake

1191 silver badge5 bronze badges

Collectives™ on Stack Overflow

Delete all Large Objects from PostgreSQL database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related