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?
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');ERROR: missing FROM-clause entry for table "pg_stat_activity" (psql (9.6.1))
this does not work anymore....get the above error ^
This worked for me, @Szymon Guz's solution should be the accepted answer now: `select pg_terminate_backend(pid) from pg_stat_activity where datname='DB_NAME';`
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 calledprocpid, 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 thenGRANTit afterward.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.
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
8 years ago
I read somewhere that you could use http://www.lowth.com/cutter/ to achive this.