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

Question

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 so that?

Answer 1

The query like this should help (assuming the database is named 'db'):

select pg_terminate_backend(procpid) from pg_stat_activity where datname='db';

However you have to be a superuser to disconnect other users.

Answer 2

This can be used to "free" a database from client connections, so that you for example can rename it. One-Line-Example:

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.