How to drop all connections to a specific database without stopping the server?

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.

How can I do that?

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.

StackExchange Database Administrators Q#16426, answer score: 156