From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: AW: running vacuumlo periodically? |
Date: | 2021-01-29 09:24:18 |
Message-ID: | 7ab85369ae7d01b21f780d04be2bc2fe22624b5f.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Markhof, Ingolf | 2021-01-29 12:56:18 | Foreign table performance issue / PostgreSQK vs. ORACLE |
Previous Message | Tatsuo Ishii | 2021-01-29 07:47:41 | Re: Does pg_ctl promote wait for pending WAL? |