4

I've searched and tried a lot, but I can't figure out a proper solution to dropping all connections so I can properly drop a database.

Force drop db while others may be connected

This topic from 2012 discusses that we should first stop allowing connections to the database. If I execute the command in step 1 I can no longer run commands.

psql -h localhost -d postgres -U postgres -c "UPDATE pg_database SET datallowconn = 'false' where datname = 'postgres';"
psql -h localhost -d postgres -U postgres -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'postgres';"
dropdb -h localhost -U postgres postgres

The result of all commands after the first is:

psql: FATAL:  database "postgres" is not currently accepting connections

Once I'm at this point I have no idea how to recover other than stopping postgresql, deleting the database folder and recreating the database using initdb.

PostgreSQL DROP DATABASE

This tutorial doesn't include anything on disallowing connections. So I run the following:

psql -h localhost -d postgres -U postgres -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'postgres';"
FATAL:  terminating connection due to administrator command
FATAL:  terminating connection due to administrator command
connection to server was lost
dropdb -h localhost -U postgres postgres
dropdb: database removal failed: ERROR:  database "postgres" is being accessed by other users
DETAIL:  There are 6 other sessions using the database.

I've also tried revoking access like so, but this doesn't stop PGAdmin III from connecting so I assume I messed up the command.

psql -h localhost -d postgres -U postgres -c "REVOKE CONNECT ON DATABASE postgres FROM public;"

I'm not sure how to continue. I see a lot of topics on this issue, but nothing works. Note, I'm running these commands in a shell script, but if I run them from the CLI I get the same results.

Improve this question
3
  • Once you've set datallowconn = 'false', it does exactly what it says on the tin, and doesn't allow connections to that database. Have you tried connecting as the postgres user to template1 instead of the postgres database to drop the postgres database?
    – Kassandry
    Commented Oct 14, 2015 at 20:09
  • @Kassandry - That works perfectly. I knew about template0, but by default template0 doesn't allow connections. Did not know template1 existed. I am now able to do exactly what I require. Thank you! If you submit this as a answer I can mark it as answered.
    – quickblueblur
    Commented Oct 15, 2015 at 11:32
  • Postgres 13 (to be release in Q4/2020) will have an option to do this.
    – user1822
    Commented Nov 13, 2019 at 11:06

1 Answer 1

Reset to default
2

Once you've set datallowconn = 'false', it does exactly what it says on the tin, and doesn't allow connections to that database.

Connecting as the postgres user to the template1 database should allow you to drop that database.

For more information check the documentation on template databases. They're quite useful. =)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.