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()