Dropping DB when there are active connections

Question

I am trying to script the back up of a database and part of the flow is that I need to drop and then recreate the database from a backup.

When there are connections to the database I get a failure message like this:

DETAIL: There are 2 other sessions using the database.

I have been looking for a way to either ignore this or kill connections prior to dropping the database. I am trying to use the query below but it doesnt seem to actually allow me to drop the db after running it.

-- Drop connections
\set database_name `echo $DB_NAME`

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = ':database_name'
AND pid <> pg_backend_pid();

I took the query from an SO post which seemed to suggest it could be used, is there a more reliable way to do this or a way to force the dropping of a database regardless of whether there are active connections?

Bozuwa

did you exit psql after running that query? Otherwise you'll still have one open connection to that database - the one that your query used – Considerate

I am running psql remotely from a docker container and I am connecting to postgres management database and not the one I am trying to drop. Will I still need to run a disconnect and is this the correct database to connect to? – Bozuwa

Answer 1

Postgres 13 or hgigher

SQL DDL statement while connected to a different DB of the same cluster:

 DROP DATABASE database_name WITH (FORCE);

From the shell:

dropdb database_name --force

See:

• Force drop db while others may be connected

For older versions

Connect to a different database than the one you are going to drop - in the same db cluster. Else, your own connection will be in the way. You might use the default maintenance database "postgres" for this:

psql -h localhost -U postgres postgres

Then make sure, clients don't reconnect:

UPDATE pg_database SET datallowconn = 'false' WHERE datname = :"database_name";

Finally:

SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  datname = :"database_name";  -- escape to avoid errors / sql injection

This :"database_name" is the syntax for SQl interpolation in psql. Double quotes for identifiers.

Hixson

Thanks, I am trying to run this from the postgres database, so is the query I have incorrect for postgres 9.6? Should I remove the AND clause (like in your example)? – Bozuwa

AND pid <> pg_backend_pid() in your example excludes the current session - which is irrelevant while the current session is to a different database ... – Hixson