How to drop all connections to a specific database without stopping the server?
Here is my answer to very similar question on StackOverflow.
Depending on your version of postgresql you might run into a bug, that makes
pg_stat_activityto 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');
The query like this should help (assuming the database is named 'db'):
select pg_terminate_backend(pid) from pg_stat_activity where datname='db';
pidused 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 PUBLICor something similar, and then
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.