Postgresql - unable to drop database because of some auto connections to DB

Question

Whenever I try to drop database I get the following error:

ERROR:  database "pilot" is being accessed by other users
DETAIL:  There is 1 other session using the database.

When I use:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

I terminated the connection from that DB, but if I try to drop database after that somehow someone automatically connects to that database and gives this error. What could be doing that? No one uses this database, except me.

Craig Ringer · Accepted Answer · 2023-04-04T23:25:46.013

333

Postgres 13+

Use WITH (force)

See https://stackoverflow.com/a/68982312/398670 instead

Postgres 12 and older

You can prevent future connections with:

REVOKE CONNECT ON DATABASE thedb FROM public;

(and possibly other users/roles; see \l+ in psql)

You can then terminate all connections to this db except your own:

SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = current_database() AND pid <> pg_backend_pid();

On older versions pid was called procpid so you'll have to deal with that.

Since you've revoked CONNECT rights, whatever was trying to auto-connect should no longer be able to do so.

You'll now be able to drop the DB.

This won't work if you're using superuser connections for normal operations, but if you're doing that you need to fix that problem first.

After you're done dropping the database, if you create the database again, you can execute below command to restore the access

GRANT CONNECT ON DATABASE thedb TO public;

edited Apr 04 '23 at 23:25

answered Jul 04 '13 at 04:03

Craig Ringer

307,061
76
688
778

24

If you import another database with the same name later, grant connect capability to public back: `GRANT CONNECT ON DATABASE thedb TO public;` – Mikhail Vasin Sep 12 '17 at 11:03
4

REVOKE CONNECT will not prevent the connections from the db owner or superuser. So if you don't want anyone to connect the db, execute command `alter database pilot allow_connections = off`. https://stackoverflow.com/a/61746796/1314077 – inferno Dec 22 '20 at 03:28
4

This is a way to do this, but Postgresql 13 update with `FORCE`, shortens the way a lot. Check [my answer](https://stackoverflow.com/a/68982312/7954021) or the [manual](https://www.postgresql.org/docs/current/sql-dropdatabase.html). – Renis1235 Aug 30 '21 at 10:12
current_database() not working for me but this is ok (db_name in single quotes): SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'db_name' AND pid <> pg_backend_pid(); – alboforlizo Nov 10 '22 at 14:34
1

As of Postgres 13 there is a MUCH easier way using `WITH (FORCE)`: https://stackoverflow.com/a/68982312/482115 – degenerate Apr 04 '23 at 15:45

score 251 · Answer 2 · answered Jul 17 '14 at 05:46

251

Whenever I try to drop database I get:

ERROR:  database "pilot" is being accessed by other users
DETAIL:  There is 1 other session using the database.

First You need to revoke

REVOKE CONNECT ON DATABASE TARGET_DB FROM public;

Then use:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

It will surely work.

answered Jul 17 '14 at 05:46

Suneel Kumar

5,621
3
31
44

3

The final SQL statement, may require to be run multiple times, depending on the the number of sessions using the database at the time. Overall this approach work perfectly. – Zeeng Mar 16 '21 at 21:57
under docker-compose if you use the db dbname, this doesn't help as psql is connecting to the db it seems, so you still can't drop. i am unsure. – Eugene Gr. Philippov Jun 30 '21 at 23:39

Renis1235 · Answer 3 · 2023-08-11T06:21:11.330

60

Update in Postgresql 13

You could just use this command to drop a Database forcefully, thus disconnecting each user/app connected to it.

DROP DATABASE db_name WITH (FORCE)

You could check the manual for more.

FORCE - Attempt to terminate all existing connections to the target database. It doesn't terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.

PS: You cannot delete the database that you are connected to while executing this query.

edited Aug 11 '23 at 06:21

answered Aug 30 '21 at 10:07

Renis1235

4,116
3
15
27

2

This works great, as long as you are connected to a different database when you run it. Otherwise you get an error that you can't delete the currently connected DB. – degenerate Apr 04 '23 at 15:44

score 52 · Answer 4 · answered Oct 17 '17 at 11:05

52

I found a solution for this problem try to run this command in terminal

ps -ef | grep postgres

kill process by this command

sudo kill -9 PID

answered Oct 17 '17 at 11:05

Dinesh Pallapa

1,172
18
20

1

No, it's too hardcode, what if you can't kiil pg process because you have other databases that are being accessed? – Vladimir Stazhilov Mar 13 '18 at 11:19
2

@VladimirStazhilov It will show the database name and pid of that database. someone can select specific pid kill only that particular database. – Dinesh Pallapa Mar 13 '18 at 11:25
1

ps -ef | grep postgres | grep YourAppName | cut -w -f3 | xargs kill -9 – dezman May 18 '22 at 19:50

score 50 · Answer 5 · edited Jun 29 '20 at 11:26

50

It means another user is accessing the database. Simply restart PostgreSQL. This command will do the trick

root@kalilinux:~#sudo service postgresql restart

Then try dropping the database:

postgres=# drop database test_database;

This will do the trick.

edited Jun 29 '20 at 11:26

Mark Amery

143,130
81
406
459

answered Mar 12 '18 at 09:34

Suman Astani

1,181
11
16

1

hey tysm this worked for me. I will like to add if you have a hyphen `-` in the name of the db put it in quotation marks like this `"my-database"` – destroyer22719 Sep 14 '20 at 14:59
2

This is a great and simple solution! – David Alford Aug 01 '21 at 01:04
1

This would be good if it is used in a dev environment. It is exactly what you need in this case. However, as far as scripting for production, there may be other considerations as have been pointed out in above solutions. – Harlin Apr 06 '22 at 21:25

score 45 · Answer 6 · edited Sep 11 '20 at 20:41

45

Simply check what is the connection, where it's coming from. You can see all this in:

SELECT * FROM pg_stat_activity WHERE datname = 'TARGET_DB';

Perhaps it is your connection?

edited Sep 11 '20 at 20:41

Carolus

477
4
16

answered Jul 03 '13 at 13:35

6

sudo kill -9 PID in terminal after seeing the result – Dan Rey Oquindo Aug 17 '15 at 03:40

score 30 · Answer 7 · edited Sep 11 '20 at 20:41

30

GUI solution using pgAdmin 4

First enable show activity on dashboard if you haven't:

File > Preferences > Dashboards > Display > Show Activity > true

Now disable all the processes using the db:

Click the DB name
Click Dashboard > Sessions
Click refresh icon
Click the delete (x) icon beside each process to end them

You should now be able to delete the db.

edited Sep 11 '20 at 20:41

Carolus

477
4
16

answered Jul 16 '18 at 14:00

Andrew

18,680
13
103
118

This works well - I tested it with PgAdmin 4.5 and with PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit (Windows). – vab2048 May 01 '19 at 13:27
2

This is the best solution I think. This works really well! – Lahiru Jayaratne Jan 09 '20 at 13:14
2

To view the `Dashboard`, right-click on the top menu and choose `Add Panel -> Dashboard` – kkuilla Oct 17 '20 at 21:19

score 14 · Answer 8 · answered May 15 '17 at 10:54

14

If no potential impact on other services on your machine, simply service postgresql restart

answered May 15 '17 at 10:54

ScotchAndSoda

3,811
4
34
38

score 12 · Answer 9 · answered Dec 04 '18 at 07:29

12

Solution:
1. Shut down Pg server

2. It will disconnect all active connection
3. Restart Pg Server
4. Try your command

answered Dec 04 '18 at 07:29

amoljdv06

2,646
1
13
18

this worked for me too with Postgress.app on a Mac. In that case you stop/start the server – Juan José Ramírez Jan 30 '20 at 15:42

score 12 · Answer 10 · edited Feb 04 '22 at 17:43

12

In macOS try to restart postgresql database through the console using the command:

brew services restart postgresql

edited Feb 04 '22 at 17:43

A.L

10,259
10
67
98

answered Jul 16 '20 at 04:21

Felipe Corredor

549
4
9

yea, I use postgres.app and restarting the server fixed it. – Pencilcheck May 04 '23 at 04:22

score 10 · Answer 11 · answered Oct 15 '18 at 22:57

10

Simple as that

sudo service postgresql restart

answered Oct 15 '18 at 22:57

OdkoPP

442
6
14

score 5 · Answer 12 · answered May 12 '20 at 08:13

5

REVOKE CONNECT will not prevent the connections from the db owner or superuser. So if you don't want anyone to connect the db, follow command may be useful.

alter database pilot allow_connections = off;

Then use:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'pilot';

answered May 12 '20 at 08:13

inferno

684
6
21

1

Thanks... REVOKE CONNECT was not enough on my scenario. – volpato Jun 04 '20 at 14:19
`# alter database dbname allow_connections = off;` `ERROR: cannot disallow connections for current database` – Eugene Gr. Philippov Jun 30 '21 at 23:36
@Eugene Gr. Philippov you can not alter database that you are logged in, so you should change to other database and retry. – inferno Jul 01 '21 at 13:00

score 5 · Answer 13 · answered Jan 04 '22 at 09:32

5

first:

sudo systemctl restart postgresql

then:

drop database DATABASE_NAME;

answered Jan 04 '22 at 09:32

Mohammad Amin Eskandari

324
5
7

2

While this may be factually correct, adding explanation would vastly improve the answer. – Andrew Jan 05 '22 at 18:13

score 5 · Answer 14 · answered Apr 05 '22 at 13:16

5

This is what worked for us on postgres 12. Using pgadmin, pgbouncer, and multiple client applications.

REVOKE CONNECT ON DATABASE <mydbname> FROM public;
ALTER DATABASE <mydbname> allow_connections = off;
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<mydbname>';
DROP DATABASE <mydbname>;

answered Apr 05 '22 at 13:16

emdog4

1,975
3
20
25

1

You may need `GRANT pg_signal_backend TO ; ` to be able to do that. – iMx Jul 25 '22 at 09:08

Sagan · Answer 15 · 2018-09-24T22:57:59.693

In my case, I am using AWS Redshift (based on Postgres). And it appears there are no other connections to the DB, but I am getting this same error.

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

In my case, it seems the database cluster is still doing some processing on the database, and while there are no other external/user connections, the database is still internally in use. I found this by running the following:

SELECT * FROM stv_sessions;

So my hack was to write a loop in my code, looking for rows with my database name in it. (of course the loop is not infinite, and is a sleepy loop, etc)

SELECT * FROM stv_sessions where db_name = 'XYZ';

If rows found, proceed to delete each PID, one by one.

SELECT pg_terminate_backend(PUT_PID_HERE);

If no rows found, proceed to drop the database

DROP DATABASE XYZ;

Note: In my case, I am writing Java unit/system tests, where this could be considered acceptable. This is not acceptable for production code.

Here is the complete hack, in Java (ignore my test/utility classes).

  int i = 0;
  while (i < 10) {
    try {
      i++;
      logStandardOut("First try to delete session PIDs, before dropping the DB");
      String getSessionPIDs = String.format("SELECT stv_sessions.process, stv_sessions.* FROM stv_sessions where db_name = '%s'", dbNameToReset);
      ResultSet resultSet = databaseConnection.execQuery(getSessionPIDs);
      while (resultSet.next()) {
        int sessionPID = resultSet.getInt(1);
        logStandardOut("killPID: %s", sessionPID);
        String killSessionPID = String.format("select pg_terminate_backend(%s)", sessionPID);
        try {
          databaseConnection.execQuery(killSessionPID);
        } catch (DatabaseException dbEx) {
          //This is most commonly when a session PID is transient, where it ended between my query and kill lines
          logStandardOut("Ignore it, you did your best: %s, %s", dbEx.getMessage(), dbEx.getCause());
        }
      }

      //Drop the DB now
      String dropDbSQL = String.format("DROP DATABASE %s", dbNameToReset);
      logStandardOut(dropDbSQL);
      databaseConnection.execStatement(dropDbSQL);
      break;
    } catch (MissingDatabaseException ex) {
      //ignore, if the DB was not there (to be dropped)
      logStandardOut(ex.getMessage());
      break;
    } catch (Exception ex) {
      logStandardOut("Something went wrong, sleeping for a bit: %s, %s", ex.getMessage(), ex.getCause());
      sleepMilliSec(1000);
    }
  }

This should be correct answer, others those who restart service, why not restart your computer? — Siwei, May 24 '22 at 22:21

Joweria · Answer 16 · 2019-10-25T16:28:49.873

In my opinion there are some idle queries running in the backgroud.

Try showing running queries first

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

kill idle query ( Check if they are referencing the database in question or you can kill all of them or kill a specific using the pid from the select results )

SELECT pg_terminate_backend(procpid);

Note: Killing a select query doesnt make any bad impact

score 2 · Answer 17 · answered Jan 26 '22 at 11:40

2

If you are using docker to run postgresql server, restart the container.

answered Jan 26 '22 at 11:40

ian

1,505
2
17
24

score 1 · Answer 18 · answered Apr 26 '21 at 08:22

1

If you encounter this error in IntelliJ make sure you close the connection in all windows by clicking the button shown below

answered Apr 26 '21 at 08:22

simibac

7,672
3
36
48

score 1 · Answer 19 · answered Jun 19 '21 at 20:06

In my case, I continued to get the error even after using the below command - because another user connection was immediately created after execution.

REVOKE CONNECT ON DATABASE <db_name> FROM public;

What fixed it for me was using inferno's solution above (also below) to prevent connections.

ALTER DATABASE <db_name> allow_connections = off

That allowed me to terminate the process without the process immediately being recreated.

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB AND pid <> pg_backend_pid();

score 0 · Answer 20 · answered Dec 24 '18 at 06:44

0

While I found the two top-upvoted answers useful on other occasions, today, the simplest way to resolve the issue was to realize that PyCharm might be keeping a session open, and if I clicked Stop in PyCharm, that might help. With pgAdmin4 open in the browser, I did so, and almost immediately saw the Database sessions stats drop to 0, at which point I was able to drop the database.

answered Dec 24 '18 at 06:44

hlongmore

1,603
24
28

"PyCharm might be keeping a session open" ? How? I run unit tests in PyCharm's terminal (frontend Python with peewee, backend Postgres) , i.e. the "Stop" button is greyed out and I keep these errors nevertheless... – András Aszódi Mar 11 '20 at 14:51
@LaryxDecidua I believe that, in my case, I must have had an instance of a service running in PyCharm that used the db. If you exit PyCharm, does the number of instances drop to 0, allowing you to drop the db? If so, there must be something (database explorer, SQL query, something else) that is still connected. – hlongmore Mar 13 '20 at 06:51

score 0 · Answer 21 · answered Oct 30 '21 at 01:45

0

For me, I just restart postgresql.

systemctl restart postgresql

answered Oct 30 '21 at 01:45

Barret Wang

11
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 30 '21 at 02:56

Aleksey Mikhailov · Answer 22 · 2022-07-01T06:40:36.127

0

Postgresql12

Postgresql - unable to drop database because of some auto connections to DB

25 Answers25

Postgres 13+

Postgres 12 and older

Update in Postgresql 13

Linked