This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to stop all running connections of a database and create the force to drop the database in PostgreSQL.
In our organization, I created one database for production report only. This database works as a backup and report server and only one or two services using this database server.
I also created this server for temporary purposes, and now I need to drop that database, but I was facing the problem of running connections.
I know that running connections are not that much important for me because this setup is only for our internal data report purpose.
I worked around this and found a script to stop all connections of a Postgres database and drop that Postgres database.
As a Database Administrator, we are also responsible for managing or removing this kind of unwanted objects or databases.
Below are the steps::
First, Make sure that no one can connect to this database:
|
1 2 3 |
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'DatabaseName'; |
Second, execute this script to terminate all connections of a database
(For postgres < 9.2):
|
1 2 3 |
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'DatabaseName'; |
Drop now:
|
1 |
DROP DATABASE DatabaseName; |
Note: Step one and two require superuser permission and only database owner can drop this database.
