I am maintaining this as a running article to document all the useful ProgreSQL commands that I encounter and use regularly.

Kill all active connections to a database

The below SQL block kills all active connection to the specified database. I use this when I want to drop a database and the active connection won’t let me do it. Of course use it with caution.

SELECT
 pg_terminate_backend(pid)
FROM
 pg_stat_activity
WHERE
 datname = 
'db_name'
AND
 leader_pid 
IS NULL;

Drop a database

This SQL statement drops the database. Please note that once you drop a database and if you do not have a backup you won’t be able to retrieve its data. Use this statement with extreme caution.

drop database essencedev;

Show database size

The sql script below shows a list of databases with their respective sizes in descending order.

select dbs.datname AS db_name,  
       pg_size_pretty(pg_database_size(dbs.datname)) as db_size
from pg_database dbs
order by pg_database_size(dbs.datname) desc;

Grant a role to a user

GRANT role_name TO <username>;