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.
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.
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
template1instead of the
postgresdatabase to drop the