PostgreSQL – How to Drop Database with Connected Users

postgresql

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.

Best Answer

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. =)

Related Question