Always shows all sessions / connections / processes / activities globally for the server regardless to which database are you connected to.
This one shows all processes – also your own connection:
select * from pg_stat_activity
In PostgreSQL 9.3 result already contains query text.
To see query text in PostgreSQL 8.4 you need to use this:
If you want to exclude your own connection:
PostgreSQL 9.3:
select * from pg_stat_activity where pid<>pg_backend_pid()
PostgreSQL 8.4:
select * from pg_stat_activity where procpid<>pg_backend_pid()