How to get last access/modification date of a PostgreSQL database?

Question

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?

Answer 1

I guess you should activate some log options. You can get information about logging on postgreSQL here.

Answer 2

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.