An important tool for debugging PostgreSQL performance issues is the ability to view all the currently executing queries. With newer versions of PostgreSQL we get some more details. Here’s an example I’ve used on PostgreSQL 8.2:
SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity ;
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

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_activity

Example:
database1=# SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid ;

    datname    | procpid | current_query 
—————+———+—————
 mydatabaseabc |    2587 |
 anotherdb     |   15726 | SELECT * FROM users WHERE id=123 ;  
 mydatabaseabc |   15851 |
(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 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.
Another of my favourite queries is to show a top-like view of current queries, grouped by how many of the same query are running at that instant and the usernames belonging to each connection.

SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;

Example:
database1=# SELECT count(*) as cnt, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY cnt DESC;
 cnt |    usename    | current_query 
—–+—————+—————
   7 | freddykrueger |
   3 | freddykrueger | SELECT name FROM users WHERE id=50;
   1 | postgres      |
(3 rows)

I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases.

Method 1.
Depending on your version of postgresql you might run into a bug, that makes pg_stat_activity to omit active connections from dropped users. These connections are also not shown inside pgAdminIII.
If you are doing automatic testing (in which you also create users) this might be a probable scenario.

In this case you need to revert to queries like:
 SELECT pg_terminate_backend(pg_stat_activity.procpid)
 FROM pg_stat_get_activity(NULL::integer)
 WHERE datid=(SELECT oid from pg_database where datname = ‘your_database’);

Method 2.
The query like this should help (assuming the database is named ‘db’):
select pg_terminate_backend(pid) from pg_stat_activity where datname=’db’;

pid used to be called procpid, so if you’re using a version of postgres older than 9.2 you could try the following:
select pg_terminate_backend(procpid) from pg_stat_activity where datname=’db’;

However you have to be a superuser to disconnect other users.
It might also be useful to REVOKE CONNECT ON DATABASE FROM PUBLIC or something similar, and then GRANT it afterward.

Method 3.
This can be used to “free” a database from client connections, so that you for example can rename it:
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname=’current_db’;
ALTER DATABASE current_db RENAME TO old_db;
ALTER DATABASE new_db RENAME TO current_db;

Be aware that this might cause problematic behaviour to your client apps. Data actualy should not be currupted due to using transactions.

Method 4.
$ ps aux | grep DATABASE_NAME
          51191
 postgres: user DATABASE_NAME [local] idle
then kill the process

$ kill -9 51191