How to disconnect all Postgres database connections and Drop database and create fresh Database

Some time ago I was doing data analysis related to some requirements. To complete this I need to grab the latest production dump and update the staging server with production latest dump.

It looks like an easy task to perform just drop the existing database and create a new database and restore the new database with the latest dump file.

Once I tried to do so I keep on getting the error saying

ERROR: database “xxxabc_staging” is being accessed by other users 
DETAIL: There are 10 other session(s) using the database

After googling a bit and trying different ways I found the solid way to do this.
Here are the two main things which we need to take care

Disconnect all running connections by specifying a database name
Need to REVOKE the CONNECT privileges to avoid new connections by specifying a database name

To disconnect all running connections to the database you need to run the below SQL script

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'xxxabc_staging';

After running the above SQL script there might be chances that your database might get a new connection and the database drop again fail.

To avoid this behavior you can revoke the connect privilege from a specific database and after running the SQL script grant the privilege again to the database.

Below is the script which does it all

REVOKE CONNECT ON DATABASE xxxabc_staging FROM PUBLIC, rakeshverma;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'xxxabc_staging';

GRANT CONNECT ON DATABASE xxxabc_staging TO PUBLIC, rakeshverma;

In nutshell, we have two solid ways to disconnect all Postgres running connections and Drop the Database and Create a fresh Database

1.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'xxxabc_staging'; 
DROP DATABASE xxxabc_staging;

1. If your database is getting a new connection more frequently than

REVOKE CONNECT ON DATABASE xxxabc_staging FROM PUBLIC, rakeshverma;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'xxxabc_staging';

DROP DATABASE xxxabc_staging;

GRANT CONNECT ON DATABASE xxxabc_staging TO PUBLIC, rakeshverma;

I have verified it with Postgres version 9.4.8 and I believe It will work for all Postgres versions > 9.0 (still need to verify this)

• You can see rakeshverma in SQL script this is my user name for the database
• xxxabc_staging is the database name where I want to perform the actions
• You can skip the username but keep it to be a safe side