Unable to drop PostgreSQL database – Fixed

Spread the love

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.

Solution :

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.

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.

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.

See also  Resolving PostgreSQL pg_dump server version mismatch
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

Leave a Comment