CODEWITHSUNDEEP
postgresqldatabase-connectionpostgresql-9.6drop-database
berimbolo
berimbolo

Reputation: 3829

Dropping DB when there are active connections

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?

Upvotes: 3

Views: 5311

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656221

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:

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.

Upvotes: 6

Related Questions