#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Jamaica
    Posts
    2
    Rep Power
    0

    Date of a database??


    I was just wondering... does a database in postgres have some sort of date attribute attached to it? What I want to do is check the last time a database was modified... that is ... data added/removed from it. I'm running postgresql 7.2 on a Linux Box.

    Does anyone know if this is possible?
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    I suppose there are a couple ways:

    1. Check the log. In most normal configurations, there is at least some information logged for each query. If you turn on more logging features you can even make it store the text of each query, so you know exactly what was done. (the logfile is usually in /usr/local/pgsql/data/serverlog)

    2. File access time. The data in a database is stored in files. If you turn off the postgres daemon, and then examine the last access time of the files, you might be able to figure this out. Can't guarantee, though. For example, the postgres deamon itself does certain things in the background from time to time.

    Perhaps if you spend more time in the documentation and mailling lists, you would find another way, but I don't know of any.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Jamaica
    Posts
    2
    Rep Power
    0
    Thanks rycamor ... was hoping there was a simpler solution such as an attribute in the pg_database system table or some other system table perhaps... that would be so cool.

    I want to check the modification dates of about 70+ Databases to see when work was last done on them. Different users will work on them. So as superuser .... wanted to check when a database was last modified. If there was a date attribute in some sytem table and a corresponding database .... then I could just do a couple of select statments to see when they were last modified

    *Sigh* :-)
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Yeah, that would be nice, but think of the overhead to update the system tables for each insert/update/delete.
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    In general I agree with rod k. But, there are a couple things you might think about. First, I don't know whether you want to check for changes to the data in your DB, or for users creating changes in the structure, so #1 is regarding the data, and #2 regards structure.

    1. Set up a special logging table. If you are familiar with PostgreSQL's RULE system, you would realize that on any table or view, you can create a rule (similar to a trigger, but more lightweight) which also executes whatever additional query you want. So, create a rule that saves some data in another table, whenever anyone does anything with the main table or tables you are concerned with.

    2. Set up a rule on the appropriate system table. PostgreSQL is the most extensible DMBS in existence, IMHO. Almost everything about your database is availabe in the system tables, just like any regular data table. It should be possible, theoretically, to identify a few important system tables having to do with database structure, and set a rule on each, to log queries to a separate table. (disclaimer: I have not tested this, so I don't know what the results would be)

    Again, rod k's warning about overhead applies.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo