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.