Unable to delete a postgresql database

Question

I'm trying to delete a database. I've restart Postgresql service:

sudo systemctl stop postgresql
sudo systemctl start postgresql

And yet:

# drop database my_db1;
ERROR:  database "my_db1" is being accessed by other users

Why is that and how to delete it?

This isn't working either:

select pg_terminate_backend(pg_stat_activity.pid) 
from pg_stat_activity 
where pg_stat_activity.datname = 'my_db1' and pid <> pg_backend_pid();

because active connections still exist after I've run it;

maybe this help dba.stackexchange.com/questions/11893/…

Juan Carlos Oropeza
– Juan Carlos Oropeza

2016-12-20 04:29:00 +00:00
Commented Dec 20, 2016 at 4:29 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 20, 2016 at 4:29

Evan Carroll · Accepted Answer · 2016-12-20 05:17:34Z

2

have you tried in terminal ?

sudo dropdb my_db1

View man dropdb for more information.

edited Dec 20, 2016 at 5:17

Evan Carroll

1

answered Dec 20, 2016 at 4:39

Bhavik Kheni

1151 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Angel F Syrus · Accepted Answer · 2019-12-19 08:12:57Z

0

REVOKE CONNECT ON DATABASE YourDbName FROM PUBLIC;
    SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = 'YourDbName';
    DROP DATABASE YourDbName;

edited Dec 19, 2019 at 8:12

Angel F Syrus

2,0408 gold badges26 silver badges45 bronze badges

answered Dec 19, 2019 at 6:59

Karen

91 silver badge4 bronze badges

Collectives™ on Stack Overflow

Unable to delete a postgresql database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related