How to Stop all Connections and Force to Drop the Database

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:

UPDATE pg_database 
SET datallowconn = 'false' 
WHERE datname = 'DatabaseName';

Second, execute this script to terminate all connections of a database
(For postgres < 9.2):`above 9.2 use pid instead of procpid

SELECT pg_terminate_backend(procpid) 
FROM pg_stat_activity 
WHERE datname = 'DatabaseName';

Drop now:

DROP DATABASE DatabaseName;

Note: Step one and two require superuser permission and only database owner can drop this database.