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

7 Steps to configure BDR replication in postgresql


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

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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory