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 for distribution terms
- \h for help with SQL commands
- \? for help 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 for distribution terms
- \h for help with SQL commands
- \? for help 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.
Beginning Databases with PostgreSQL: From Novice to Professional (Beginning From Novice to Professional) (Paperback)
|
||||||||
PostgreSQL 8.4 Official Documentation – Volume I. The SQL Language (Paperback)
|
||||||||
nice, thanks a lot
but is there something to resolve this case: I’m unique user. Found IDLE process in pg_stat_activity table, this process isn’t run any longer, so can’t kill it. btw, on this box I never started db process at 4:00 a.m. At that time computer is off, so I think it’s record ported by dump from another box, yes this one is used by night. Restart pg server don’t resolve problem. How can I cope with it? Tnx in advance.
Hello pietro,
Not really sure here. Can you list the contents of the pg_stat_activity table as well as the processes running on the server?
Thanks.
alex
Problem resolved.
As usually happens source of this problem was in front of keyboard :)
On another screen I had terminal window with forgotten connession to db.
Thank you for your interest anyway, it is not so common that someone responds to discussion on old items.
Have a nice day
Pietro
Hello pietro,
No problem man. I had been behind in responding. I appreciate you letting us know that the issue was resolved and the cause.
Have a good one.
Thanks.
alex
Hi,
I also had the same problem, your soultion is good, but there is a quicker way.
If you have the rights to restart the PostgreSQL, just restart it, this will automatically delete all of the locks.
Regards.
Hello kodcanavari,
I agree you could just restart PostgreSQL but the problem with that is if you have other databases also on the same server that service real customers or data that cannot be down. Restarting Postgres would cause an interuption of service for users on other databases.
I definitely do agree though if you do not have other LIVE databases on this PSQL database server that just restarting Postgres is definitely an option. Thanks so much for taking the time to leave feedback!
Thanks.
alex
formatting seems to be broken here – one code block closing tag has a typo : “[/sourcode]”
Hello Richlv,
Thanks for the heads up. You were absolutely correct… the issue has been resolved. Thanks again for taking the time to let us know.
Thanks.
alex
Hi,
My rpoblem is that I don’t have the rigths to either kill a process or restart my server, do you know if it’s possible to delete the connections with a simple ‘DELETE FROM ??? WHERE procpId = 99999;’
thanks
Hello seber,
Unfortunately I do not know what the effect of that would be. Anyhow would be curious if that is what you tried and what the outcome was.
Thanks.
alex
I had to do it another way by changing the name of the database to my new one in my connection string,
fortunatly it wasn’t on a pruduction server
I know it’s kind of dirty but I had to!
cheers
Hello seber,
Hey whatever works.. :) Glad it worked out and thanks for following up.
Thanks.
alex
I’ve tried everything mentioned above and it still doesn’t work
1) Restarting the db – no luck, it keeps adding more “idle” procids
2) when I kill pids, there are new ones that keep getting added and I’m in a never ending loop.
Btw, I’m on Postgres 8.3
Hello Mansi,
Sounds like you have something external making a connection to the database. I would look to see what IP those connections are initiating from and either turn off whatever is making those connections or block the IP/IP’s from making a connection.
Thanks.
alex
I know this is an old thread but I have the same problem as Mansi. The 10 idle processes are all on 127.0.0.1 so I don’t think it is something external.
Was this ever resolved?
Thanks,
Tom
Nevermind. I forgot to kill a previously running instance of my application. Yeesh!