Обсуждение: BUG #15939: Postgres database size is growing due to oraphan objects
The following bug has been logged on the website: Bug reference: 15939 Logged by: Saka Ram Dewasi Email address: svnitsakaram94@gmail.com PostgreSQL version: Unsupported/Unknown Operating system: RHEL 7.4 Description: Hi All, We have SSO based web login portal. Below is the our architecture details. User---> Load balancer(vADC)---> Tomcat Server 7.0.19(Web Server) ---> Load Balancer (vADC) --> Jboss EAP 6.2(Application Server) --> Postgres 9.3 (Database) Here Postgres will receive user request from jboss server for login. We found that each time when users logged out of system orphan objects are generating which is causing database growth. We checked our database tables which having column type is OID/bytea. We found that there are only two tables which is having column type OID/bytea. Surprisingly , These tables dont have any data(Only two rows in each tables). These tables are not generating any orphan objects(LO objects). Orphan objects are stored in pg_largeobjects table. Due to orphan objects, size of pg_largeobject is growing hence db so. We want to implement trigger based deletion of orphan objects ( LO objects) . But for that we have to know which application tables are generating orphan objects. How to identify application tables which are generating orphan objects. Kindly guide. Thanks and Regards Saka Dewasi
Hi! On 05/08/2019 11:15, PG Bug reporting form wrote: > We have SSO based web login portal. Below is the our architecture details. > > User---> Load balancer(vADC)---> Tomcat Server 7.0.19(Web Server) ---> Load > Balancer (vADC) --> > Jboss EAP 6.2(Application Server) --> Postgres 9.3 (Database) PostgreSQL 9.3 is very old, and is no longer supported. You should upgrade, although I doubt it would help with this particular problem. > Here Postgres will receive user request from jboss server for login. We > found that each time when users logged out of system orphan objects are > generating which is causing database growth. We checked our database tables > which having column type is OID/bytea. We found that there are only two > tables which is having column type OID/bytea. Surprisingly , These tables > dont have any data(Only two rows in each tables). These tables are not > generating any orphan objects(LO objects). Orphan objects are stored in > pg_largeobjects table. Due to orphan objects, size of pg_largeobject is > growing hence db so. We want to implement trigger based deletion of orphan > objects ( LO objects) . But for that we have to know which application > tables are generating orphan objects. How to identify application tables > which are generating orphan objects. Kindly guide. I don't quite understand where those Large Objects are coming from, if you're not using them in your application. Perhaps the JDBC driver is creating them behind your back? Enabling statement logging might give a clue (log_statement=all). There's a contrib module called 'vacuumlo' that might help you to clean them up, see https://www.postgresql.org/docs/current/vacuumlo.html. - Heikki
On Tue, 6 Aug 2019 at 16:29, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
Hi!
On 05/08/2019 11:15, PG Bug reporting form wrote:
> We have SSO based web login portal. Below is the our architecture details.
>
> User---> Load balancer(vADC)---> Tomcat Server 7.0.19(Web Server) ---> Load
> Balancer (vADC) -->
> Jboss EAP 6.2(Application Server) --> Postgres 9.3 (Database)
PostgreSQL 9.3 is very old, and is no longer supported. You should
upgrade, although I doubt it would help with this particular problem.
> Here Postgres will receive user request from jboss server for login. We
> found that each time when users logged out of system orphan objects are
> generating which is causing database growth. We checked our database tables
> which having column type is OID/bytea. We found that there are only two
> tables which is having column type OID/bytea. Surprisingly , These tables
> dont have any data(Only two rows in each tables). These tables are not
> generating any orphan objects(LO objects). Orphan objects are stored in
> pg_largeobjects table. Due to orphan objects, size of pg_largeobject is
> growing hence db so. We want to implement trigger based deletion of orphan
> objects ( LO objects) . But for that we have to know which application
> tables are generating orphan objects. How to identify application tables
> which are generating orphan objects. Kindly guide.
I don't quite understand where those Large Objects are coming from, if
you're not using them in your application. Perhaps the JDBC driver is
creating them behind your back? Enabling statement logging might give a
clue (log_statement=all).
No, we do some things for you but creating Large Objects is not one of them
Dave