Cannot Drop The Currently Open Database PostgreSQL
In this post, I will walk you through the troubleshooting steps for deleting a PostgreSQL database. I will show you how I fix ERROR: database “linux” is being accessed by other users in PostgreSQL, This error occurred when you had an active connection to the database. PostgreSQL will not allow you to delete a database until you terminate the active connection to a running PostgreSQL database. Here is a step-by-step guide for deleting a database in PostgreSQL.
Error :
I got this error when I was trying to delete my running PostgreSQL database, because I want to import new backup after deleting the old database.
ERROR: database "db_name" is being accessed by other users
DETAIL: There is 10 other session using the database.Solution :
Check the active connection on PostgreSQL database.
SELECT * FROM pg_stat_activity WHERE datname = 'db_name';If you discover any active connections on the database, Disable and terminate them using the steps below.
Disable New connections On PostgreSQL
You can disable new database connections by running the command listed below. A new connection will not be created after running this command.
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'db_name';The above command disables the new connection, but old connections remain active on the database. To terminate an existing connection, follow the steps below.
Terminate Connections PostgreSQL database
By running this command, all active connections to the PostgreSQL database will be terminated.
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db_name';We have now terminated all active connections to the PostgreSQL database, and we are ready to delete it.
Delete PostgreSQL Database.
DROP DATABASE db_name;You can now delete the database by running the following command. Please let me know if you found this post helpful.
You may like this post : Resolving PostgreSQL pg_dump server version mismatch