#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0

    Finding out what is writing to pg_largeobject


    I am using PostgreSQL x64 9.2.6 on Windows 2008 Server Standard R2 with IBM Rational Focal Point application. The backup of the DB is taken on daily basis at 5 in the morning. The backup file size is growing by 30MB every day, even on the days when there is almost no activity on the application.

    On investigation, I found out that the table pg_largeobject is growing on daily basis. I now want to find out what writes to this table, which particular query or which particular objects are being stored in this table. How can I enable audit or some other function through which I can find out what is causing this table to grow?

    I have also used vacuumlo.exe, but even that does not claim the size any more than few MBs.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    You can always enable the PostgreSQL query log, and filter that for "lo_" function calls.

    But large objects are usually binary data and that suggests something like backups, daily reports etc.

    Comments on this post

    • akbarmunir agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by Vinny42
    You can always enable the PostgreSQL query log, and filter that for "lo_" function calls.

    But large objects are usually binary data and that suggests something like backups, daily reports etc.
    Thanks Vinny42. Your suggestion worked, though I am still struggling to find out what these queries mean. You have mentioned backup. Does this mean that taking the DB backup causes LOs to be written to this table? This particular DB is backed up daily, using batch script.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    You have mentioned backup. Does this mean that taking the DB backup causes LOs to be written to this table?
    No,takng a backup will not write LO's. It may increase the size of the db slightly if you issue a "backup start" command because that causes the DB tostart tracking cahnges to the data, but if you use pg_dump your database will not grow by taking a backup.

    What I meant is that the application itself may create a backup of some data, or a report of the day's activity.It may even generate a report of changes, anything really.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Do you know of a way to find out which tables from the main schema actually refer to the tuples in the pg_largeobject? There is no direct referential constraint. Based on your suggestion, I see a lot of open write and close calls on lo_, however I have not been able to figure out what data is being written there.

    I have a feeling that the application is causing writes to the pg_largeobject table, and though the original tuples get deleted by the app, the pg_largeobject tuples remain orphan. I have used vacuumlo, but it does not claim any space other than ~ 20 MB.

    There should be a way to find out who is referencing the pg_largeobject tuples.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Large objects are not necessarily linked to tables.

    I think you should probably ask this question on one of the mailinglists: http://www.postgresql.org/list/

    Those are monitorred by the lead programmers,if anybody knows how to work out where the objects are coming from,it's them.

IMN logo majestic logo threadwatch logo seochat tools logo