select *
from pg_stat_activity
where datname = 'mydatabasename'

See also pg-top, which acts like top except it shows postgres activity.

Install pg-top (in Debian, the package is called "ptop").
Become the postgres user (e.g. sudo su postgres)
Run pg_top