Postgres ERROR: database “dev” is being accessed by other users

If you are having issues dropping a postgres database the issue is more than likely that a user is accessing the database you are attempting to drop. There isn’t technically a way to force drop the postgres database however there are steps you can take to prevent others from accessing the database and if needed kick the users whom are accessing the database.

If you receive an error similar to the below the issue with your attempt to drop the database is the fact that either a user is accessing the database or an application of some sort has an open connection to the database. In the second scenario PostgreSQL considers the application the same as a user. In the code below the name of the example database is dev.

Please note that dropping the database will destroy all data located within the database. I strongly suggest always backing up your data before dropping the database. You can backup the data with pg_dump and regardless of if you think you will need the data or not it is best to keep a copy just in case.

postgres=# drop database dev;

ERROR:  database "dev"is being accessed by other users

So again the issue is there is some form of active connection open to the database and as a safety measure PostgreSQL will not allow the database to be dropped. The first thing to do is get a list of any active connections to the database you are attempting to drop. The command below will provide these results to you. First connect to the database and shown and issue the command below. Also shown below is the command without the WHERE statement which lists all active connections for all of the databases located on this PostgreSQL server.

[root@server pgsql]# psql -U postgres

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type: copyright fordistribution terms

hforhelp with SQL commands

?forhelp with psql commands

g or terminate with semicolon to execute query

q to quit

postgres=#

postgres=# select * from pg_stat_activity where datname='dev';

datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port

-------+---------+---------+----------+----------+---------------+---------+------------+-------------------------------+-------------------------------+-------------+-------------

28091 | dev | 8481 | 10 | postgres | | f | | 2008-11-12 09:12:50.277096+00 | 2008-11-12 09:11:10.328231+00 | 127.0.0.1 | 43152

28091 | dev | 8621 | 10 | postgres | | f | | 2008-11-12 09:13:01.097768+00 | 2008-11-12 09:12:57.347203+00 | 127.0.0.1 | 43174

28091 | dev | 8557 | 10 | postgres | | f | | 2008-11-12 09:12:12.43479+00 | 2008-11-12 09:12:11.71946+00 | 127.0.0.1 | 43163

(3 rows)

postgres=#

postgres=# select * from pg_stat_activity;

datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port

-------+----------+---------+----------+----------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------

28091 | dev | 8481 | 10 | postgres | | f | | 2008-11-12 09:12:01.288466+00 | 2008-11-12 09:11:10.328231+00 | 127.0.0.1 | 43152

11511 | postgres | 7101 | 10 | postgres | select* from pg_stat_activity; | f | 2008-11-12 09:12:32.092138+00 | 2008-11-12 09:12:32.092138+00 | 2008-11-12 08:59:39.016751+00 | | -1

28091 | dev | 8583 | 10 | postgres | | f | | 2008-11-12 09:12:31.391735+00 | 2008-11-12 09:12:28.394225+00 | 127.0.0.1 | 43169

28091 | dev | 8557 | 10 | postgres | | f | | 2008-11-12 09:12:12.43479+00 | 2008-11-12 09:12:11.71946+00 | 127.0.0.1 | 43163

(4 rows)

postgres=#

As you can see above the second field in the pg_stat_activity table is the database name field. The primary clue to use will be the procpid field which stands for process PID (Process ID). Also note the client_addr field because that will be another clue to the location of the user accessing the database. If you see 127.0.0.1 as the client address then its either an application accessing the database or someone is connected to the local server and accessing the database.

Initially you will want to attempt to let users know to not access Postgres on this server or block access. If you think that people are accessing the database through an application you could maybe shutdown the web server in an attempt to block the access to the application. So regardless the initial goal will be to be as sane as possible about blocking access or letting other users know that they need to stop accessing the database at this time.

If you are unable locate the users or if users have not stopped accessing the database and you are not worried about corrupting this data since you are dropping it anyway then you can kill the PID associated with the connection. The command to kill a process ID is “kill -9
” where PID is the number located in the procpid field of the pg_stat_activity table. Once you run the select query and there are no more active connections you should be able to drop the database without issue as shown below.

[root@server pgsql]# psql -U postgres

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type: copyright fordistribution terms

hforhelp with SQL commands

?forhelp with psql commands

g or terminate with semicolon to execute query

q to quit

postgres=#

postgres=# select * from pg_stat_activity where datname='dev';

datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port

-------+---------+---------+----------+---------+---------------+---------+------------+-------------+---------------+-------------+-------------

(0 rows)

postgres=#

postgres=# drop database dev;

DROP DATABASE

postgres=#

Now the database and all of the data located in that database has been deleted you can recreate the database or just be done with that database. The PostgreSQL database can be recreated using the below syntax.

postgres=# create database dev;

CREATE DATABASE

postgres=#

Now you have a new empty database called dev. At this point you can start manually adding tables or use the psql command to import database from a data dump or whatever else you choose. 

Reference: http://www.question-defense.com/2008/11/12/postgres-error-database-dev-is-being-accessed-by-other-users