Configuration

Storing of query strings is usually disabled in PostgreSQL by default.  To enable it, use this line in your postgresql.conf
stats_command_string = true
This setting can be changed on a running database without restarting or effecting open connections by telling the PostgreSQL parent process, postmaster, to reload its config.  Send it a SIGHUP or use the safer pg_ctl command with the reload option.  Example:
pg_ctl reload

(variable stats_command_string has been deprecated from 8.3 onwards)

Queries

When stats_command_string is enabled the pg_stat_activity table holds all currently active query strings.  The simplest query will show all current query strings along with which database they refer to and the process ID (PID) of the process serving that query.
SELECT datname,procpid,current_query FROM pg_stat_activityExample:
database1=# SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid ;
    datname    | procpid | current_query
-----------------------------------
 mydatabaseabc |    2587 | <IDLE>
 anotherdb     |   15726 | SELECT * FROM users WHERE id=123 ;
 mydatabaseabc |   15851 | <IDLE>
(3 rows)
Each row of pg_stat_activity represents one PostgreSQL process (PostgreSQL uses one server process per connection).
Any processes that are not currently performing any queries will show <IDLE> as the current_query.
Note that queries from all databases within the server will be shown.  If the user querying pg_stat_activity does not have privileges to access a database then it will not show the current_query.
The query_start column can also be used to show when the query started executing.

Update

With newer versions of PostgreSQL  PostgreSQL 8.2 and above, we get some more details.SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity ;