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?

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.

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* :-)

Yeah, that would be nice, but think of the overhead to update the system tables for each insert/update/delete.

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.