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?

  • I read somewhere that you could use lowth.com/cutter to achive this.
    – Krister Andersson
    Commented Apr 11, 2012 at 14:51

4 Answers 4


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_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');
  • 25
    ERROR: missing FROM-clause entry for table "pg_stat_activity" (psql (9.6.1)) Commented Jul 19, 2017 at 14:51
  • 6
    this does not work anymore....get the above error ^ Commented Dec 6, 2017 at 18:25
  • 3
    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';
    – Chris
    Commented Apr 26, 2020 at 14:53

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.

  • This is the working answer May 2021
    – aafirvida
    Commented May 3, 2021 at 22:53
  • This should be accept as correct answer. It's still working.
    – Shad
    Commented Oct 2, 2021 at 20:46

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.

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db_name'
  AND pid <> pg_backend_pid();

This should work and your current connection should stay intact

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.