Thread: Last modification time of a database?
So, I've got loads of databases and I'd really like some way to see what the last actual modification time was for them. I can't just check the time stamp on the file in the database directory as autovacuum fudges that and trying to match up values for either last_autovacuum or last_autoanalyze in pg_stat_all_tables/indexes with the files' time stamps is no good as the time stamp on a file that was last written to by autovacuum is for when autovacuum stopped writing to it whereas the values in pg_stat_all_tables/indexes are from when autovacuum started the given operation and the difference between the two can vary with the size of the table. Am I missing something obvious here? If not, has anyone come up with a reliable way to do this? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones wrote: > Am I missing something obvious here? If not, has anyone come up with a > reliable way to do this? Triggers on all your tables that append to a logging table? Have the client do it? Note that you do *NOT* want to have triggers that attempt to UPDATE a table to record the last modified time for that table. They'll cause transactions that touch the same table to block waiting until the first one commits/rolls back, so they'll ruin your concurrency. They may also cause unexpected deadlock aborts of transactions. -- Craig Ringer
On Mar 23, 2009, at 5:00 PM, Craig Ringer wrote: > Erik Jones wrote: > >> Am I missing something obvious here? If not, has anyone come up >> with a reliable way to do this? > > Triggers on all your tables that append to a logging table? > > Have the client do it? > > Note that you do *NOT* want to have triggers that attempt to UPDATE > a table to record the last modified time for that table. They'll > cause transactions that touch the same table to block waiting until > the first one commits/rolls back, so they'll ruin your concurrency. > They may also cause unexpected deadlock aborts of transactions. These are all client databases at the web hosting company I work at. I can't go putting triggers on all of their tables. I think I'll just start taking snapshots of pertinent data from pg_stat_activity and after I've been collecting data for a while run a report of dbs that haven't seen connections in X long since what I'm really after is inactive databases. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones <ejones@engineyard.com> writes:
> These are all client databases at the web hosting company I work at.
> I can't go putting triggers on all of their tables. I think I'll just
> start taking snapshots of pertinent data from pg_stat_activity and
> after I've been collecting data for a while run a report of dbs that
> haven't seen connections in X long since what I'm really after is
> inactive databases.
It seems like a pretty low-precision result would be sufficient for what
you need. Have you tried just tracking the last file mod time within
each database directory? This would be later than the real last use
due to delayed vacuum, etc, but it might be good enough.
regards, tom lane
Erik Jones wrote: > These are all client databases at the web hosting company I work at. > I can't go putting triggers on all of their tables. I think I'll just > start taking snapshots of pertinent data from pg_stat_activity and > after I've been collecting data for a while run a report of dbs that > haven't seen connections in X long since what I'm really after is > inactive databases. Did you think about just setting log_connections to ON and grep'ing the server logs? Otherwise it looks like a use-case for ON CONNECT triggers, unfortunately we don't have them yet. The TODO list links that message: http://archives.postgresql.org/pgsql-hackers/2008-03/msg00451.php Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org