PostgreSQL 13 New Feature: DROP DATABASE Forcefully

DROP DATABASE was supported from the early versions of Postgres. But until now, DROP DATABASE was not allowed if any of the sessions connected to the database being dropped was active. A recent commit (1 & 2) by Pavel Stehule with the help of Amit Kapila has added a feature to allow DROP DATABASE even if there are any active session(s) that are connected to the database.

This new feature can be used either using the DROP DATABASE SQL statement or by using the dropdb utility. It terminates the other sessions connected to the target database and then drops it. To terminate other sessions, the current user must have the desired permissions (same as pg_terminate_backend()). It doesn't allow sessions to terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database. It will fail if the current user has no permissions to terminate other connections. 

Syntax

SQL Syntax:

DROP DATABASE [ IF EXISTS ] name [ [ WITH ] (FORCE) ] 

Dropdb UTILITY Syntax:

dropdb [-f/--force] DBNAME

Examples

Example using drop database SQL:

postgres=# DROP DATABASE testdb WITH (FORCE)

DROP DATABASE

Example using drop database dropdb:

[dbadmin@localhost bin]$ ./dropdb --force testdb

[dbadmin@localhost bin]$

Design

The server takes a lock on the process array. It will access the process array members and check if the process is connected to the specified target database. It makes a list of all the processes that are currently using the target database. Then the server releases the lock on process array. It verifies that the user has sufficient privilege to terminate the process (the user should be either superuser or must be a member of the role whose process is being terminated or member of pg_signal_backend). The server then sends a SIGTERM signal to the process list. Finally, the server performs the cleanup activities of the database.

Limitations

• DROP DATABASE cannot be executed by connecting to the same database that the user wants to drop using DROP DATABASE.
• DROP DATABASE doesn't terminate if prepared transactions, active logical replication slots, or subscriptions are present in the target database.

Note: This feature has been added as part of the current development version and is planned to be released as part of PostgreSQL 13.