One of the most popular blogs I’ve written about PostgreSQL was about the three databases that PostgreSQL creates by default (well, actually it is initdb that creates the databases). One of the points mentioned in that post was, that you can not drop a database as long as there are sessions against the database you want to drop. This will change with PostgreSQL 13, which is currently in development.

Lets start with a short demo on PostgreSQL 12. I’ll create a new database and connect to it:

postgres@centos8pg:/home/postgres/ [121] sq
psql (12.0)
Type "help" for help.

postgres=# select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
(1 row)

postgres=# create database tmp;
CREATE DATABASE
postgres=# c tmp
You are now connected to database "tmp" as user "postgres".
tmp=# 

In another session I’ll try to drop the database which was just created above:

ostgres@centos8pg:/home/postgres/ [121] psql postgres
psql (12.0)
Type "help" for help.

[local]:5433 postgres@postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tmp       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[local]:5433 postgres@postgres=# drop database tmp;
ERROR:  database "tmp" is being accessed by other users
DETAIL:  There is 1 other session using the database.
Time: 5123.981 ms (00:05.124)
[local]:5433 postgres@postgres=# 

That does not work as the session which is currently connected prevents me from dropping the database. The same test in PostgreSQL 13:

postgres@centos8pg:/home/postgres/ [pgdev] psql postgres
psql (13devel)
Type "help" for help.

[local]:5432 postgres@postgres=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
(1 row)

Time: 5.388 ms
[local]:5432 postgres@postgres=# create database tmp;
CREATE DATABASE
Time: 164.435 ms
[local]:5432 postgres@postgres=# c tmp
You are now connected to database "tmp" as user "postgres".
[local]:5432 postgres@tmp=# 

In another session dropping the database:

postgres@centos8pg:/home/postgres/ [pgdev] psql postgres
psql (13devel)
Type "help" for help.

[local]:5432 postgres@postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tmp       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[local]:5432 postgres@postgres=# drop database tmp with (force);
DROP DATABASE
Time: 153.670 ms
[local]:5432 postgres@postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

[local]:5432 postgres@postgres=# 

Nice. What happened to the session connected to that database?

[local]:5432 postgres@tmp=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Time: 6.241 ms
: @!>? 

Of course, the session is gone.