PostgreSQL 13 New Feature: dropdb –force

There have been many big features added to PostgreSQL 13, like Parallel Vacucim, D-Duplication of B-Tree index, etc., and a complete list can be found at PostgreSQL 13 release notes. Along with the big features, there are also small ones added, including dropdb –force.

Dropdb –force

A new command-line option is added to dropdb command, and a similar SQL option “FORCE” is also added in DROP DATABASE. Using the option -f or –force with dropdb command or FORCE with DROP DATABASE to drop the database, it will terminate all existing connections with the database. Similarly, DROP DATABASE FORCE will do the same.

In the first terminal, create a test database and a database test, and connect to the database.

vagrant@vagrant:~$ createdb test;
vagrant@vagrant:~$ psql test
psql (13.0)
Type "help" for help.

In the second terminal, try to drop the test database and you will get the error message that the test database is being used by another user.

vagrant@vagrant:/usr/local/pgsql.13/bin$ psql postgres
psql (13.0)
Type "help" for help.
postgres=# drop database test;
ERROR:  database "test" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Now try the same command with the FORCE option. You will see that the database is dropped successfully.

postgres=# drop database test WITH ( FORCE );
DROP DATABASE

Note: you can also use the command line dropdb test -f.

The session on the first terminal will be terminated.

test=# \d
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.
!?> 

Looking for more info on other PostgreSQL 13 changes? Check out Ibrar’s previous post, Postgresql_fdw Authentication Changes in PostgreSQL 13!

by Ibrar Ahmed via Percona Database Performance Blog