PostgreSQL: How to drop the existing connections of the database except myself?



I want to drop all connections to my database except myself; there are many scenarios where we need to drop all connections except myself. Suppose I want to copy my production database into a new database so that my QA team can do testing on a live database. While you will start to copy your live database through query/command, you can face the issue of existing connections, and due to these connections, you are not able to create a copy of the database.

The following query may help you to drop the existing connection of the database except myself. 

PostgreSQL 9.2 and later: 


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


If you are using PostgreSQL 9.2 and later version, then you have to use the above query to remove the connections except yours for a database. 

PostgreSQL 9.1 and earlier: 


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


If you are using PostgreSQL 9.1 and earlier version, then you have to use the above query to remove the connections except yours for a database.

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...