Infrastructure at your Service

Daniel Westermann

PostgreSQL 13 will allow to drop databases even when there are connections

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:

[email protected]:/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:

[email protected]:/home/postgres/ [121] psql postgres
psql (12.0)
Type "help" for help.

[local]:5433 [email protected]=# \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 [email protected]=# 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 [email protected]=# 

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

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

[local]:5432 [email protected]=# 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 [email protected]=# create database tmp;
CREATE DATABASE
Time: 164.435 ms
[local]:5432 [email protected]=# \c tmp
You are now connected to database "tmp" as user "postgres".
[local]:5432 [email protected]=# 

In another session dropping the database:

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

[local]:5432 [email protected]=# \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 [email protected]=# drop database tmp with (force);
DROP DATABASE
Time: 153.670 ms
[local]:5432 [email protected]=# \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 [email protected]=# 

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

[local]:5432 [email protected]=# 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure