Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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:
Note: Step one and two require superuser permission and only database owner can drop this database.


Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Monitoring Tools

PostgreSQL pgBadger