On Thu, 2021-01-28 at 17:03 +0000, Zwettler Markus (OIZ) wrote: > We didn't recognize that an application is using large objects and didn't delete them. > Now we found >100G dead large objects within the database. :-( > > Is there any _GENERIC_ query which enables monitoring for orphaned objects (dead LO)? > > select oid from pg_largeobject_metadata m where not exists > (select 1 from ANY_EXISTING_TABLE_WITHIN_THE_DATABASE where m.oid = ANY_COLUMN_CONTAINING_OIDs); > > check_postgres.pl doesn't have any generic check for it. :-( You'll have to examine the application and see which tables reference the large objects. Looking for columns with type "oid" will help. I recommend that you add an "ON UPDATE OR DELETE" trigger that unlinks orphaned large objects. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
