PostgreSQL: Last access time to table
pg_catalog.pg_statio_all_tables is your friend. All you should need to do is periodically poll pg_statio_all_tables for the tables in question. Changing stats ~ active table, non-changing stats ~ potentially unused table. Just be careful that no one does a select pg_stat_reset () ; in the middle of your monitoring.

For example:

test_1=# create table test_stats (col1 integer);
CREATE TABLE

test_1=# select * from pg_catalog.pg_statio_all_tables 
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |              0 |             0 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)


Inserts:

test_1=# insert into test_stats (col1) select generate_series( 1, 10000000);
INSERT 0 10000000

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          44260 |      10088481 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)


Selects:

test_1=# select count (*) from test_stats where col1 between 10000 and 50000;
 count 
-------
 40001
(1 row)

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          85560 |      10091429 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)


Deletes:

test_1=# delete from test_stats where col1 between 10000 and 50000;
DELETE 40001

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |         155075 |      10136163 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)


update-- 2011-09-01

Further testing indicates that vacuum appears to increase the values in pg_statio_all_tables somewhat, which is unfortunate for your desired use. While vacuum doesn't make use of pg_statio_all_tables useless, it does make interpreting the results a bit fuzzier.

Perhaps a better place to monitor is pg_catalog.pg_stat_all_tables (at least with newer versions of Pg). I'm looking at version 8.4 and that has counts for tuples inserted, read, updated, and deleted-- ISTR 8.2 doesn't have all that and I don't know about 8.3 so YMMV depending on the version of Pg that you are using.

A third option (for insert, update, and delete activity) is to watch the file timestamps in the $PGDATA/base/$datid directory. The filename should map to the oid of the table, so you could use this to identify tables that aren't getting inserts, updates, or deletes. Unfortunately, this doesn't address tables that are still getting selected from, and using tablespaces will cause additional complications (as those files will not be under $PGDATA/base/$datid). The timestamps won't update until any pending changes are flushed, but if the file hasn't changed in months then the odds of a currently pending change are probably small.