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

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.

PostgreSQL 9.5 let us to track last modified commit.

1. Check track commit is on or off using the following query

   show track_commit_timestamp;
   

2. If it return "ON" go to step 3 else modify postgresql.conf

   cd /etc/postgresql/9.5/main/
   vi postgresql.conf
   

   Change

   track_commit_timestamp = off
   

   to

   track_commit_timestamp = on
   

   Restart the postgres / system

   Repeat step 1.

3. Use the following query to track last commit

   SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;
   
   SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;
   

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.