Menu
On development server I'd like to remove unused databases. To realize that I need to know if database is still used by someone or not.
Is there a way to get last access or modification date of given database, schema or table?
548
You can do it via checking last modification time of table's file. In postgresql,every table correspond one or more os files,like this: select relfilenode from pg_class where relname = 'test'; the relfilenode is the file name of table "test".
select product_id, invoice_id, amount from mytable inner join (select max(date) as last_date, product_id, invoice_id from mytable group by product_id) sub on mytable. date = sub. last_date and mytable.
There's no history in the database itself, if you're using psql you can use "\s" to see your command history there. You can get future queries or other types of operations into the log files by setting log_statement in the postgresql. conf file.
You can do it via checking last modification time of table's file. In postgresql,every table correspond one or more os files,like this:
select relfilenode from pg_class where relname = 'test'; the relfilenode is the file name of table "test".Then you could find the file in the database's directory.
in my test environment:
cd /data/pgdata/base/18976 ls -l -t | head the last command means listing all files ordered by last modification time.
144
There is no built-in way to do this - and all the approaches that check the file mtime described in other answers here are wrong. The only reliable option is to add triggers to every table that record a change to a single change-history table, which is horribly inefficient and can't be done retroactively.
If you only care about "database used" vs "database not used" you can potentially collect this information from the CSV-format database log files. Detecting "modified" vs "not modified" is a lot harder; consider SELECT writes_to_some_table(...).
If you don't need to detect old activity, you can use pg_stat_database, which records activity since the last stats reset. e.g.:
-[ RECORD 6 ]--+------------------------------ datid | 51160 datname | regress numbackends | 0 xact_commit | 54224 xact_rollback | 157 blks_read | 2591 blks_hit | 1592931 tup_returned | 26658392 tup_fetched | 327541 tup_inserted | 1664 tup_updated | 1371 tup_deleted | 246 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 blk_read_time | 0 blk_write_time | 0 stats_reset | 2013-12-13 18:51:26.650521+08 so I can see that there has been activity on this DB since the last stats reset. However, I don't know anything about what happened before the stats reset, so if I had a DB showing zero activity since a stats reset half an hour ago, I'd know nothing useful.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
