There are multiple ways to drop a database in postgres .
DEMO:
In this article, we will drop the database named: EXATREE.
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
EXATREE | postgres | UTF8 | C | C | --- >>>>>>> THIS ONE
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Method 1: ( using drop database command).
$psql -d postgres
password:
postgres=# drop database "EXATREE";;
ERROR: database "EXATREE" is being accessed by other users
DETAIL: There is 1 other session using the database.
Drop command failed, because the some sessions are already connected to the database. Lets clear them.
postgres=# select application_name,client_hostname,pid,usename from pg_stat_activity where datname='EXATREE';
application_name | client_hostname | pid | usename
-------------------------+-----------------+-------+----------
pgAdmin 4 - DB:EXATREE | | 12755 | postgres
(1 row)
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where pid='12755';
pg_terminate_backend
----------------------
t
(1 row)
postgres=#
postgres=#
postgres=# select application_name,client_hostname,pid,usename from pg_stat_activity where datname='EXATREE';
application_name | client_hostname | pid | usename
------------------+-----------------+-----+---------
(0 rows)
Now no sessions are present on the database, So we can proceed with drop command.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5432".
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
EXATREE | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# drop database "EXATREE";
DROP DATABASE
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Method 2: (using dropdb executable command)
Go to bin location of the postgres home_location at os level and run dropdb command.
postgres$ pwd
/Library/PostgreSQL/10/bin
postgres$ ./dropdb -e "EXATREE"
Password:
SELECT pg_catalog.set_config('search_path', '', false)
DROP DATABASE "EXATREE";
Method 3 : (Using pgadmin tool)
In this method, we will drop the database using pgadmin gui tool.
postgres POSTGRES