2

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
berimbolo
  • 3,319
  • 8
  • 43
  • 78
  • 1
    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 –  Apr 18 '19 at 13:44
  • 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? – berimbolo Apr 18 '19 at 13:47

1 Answers1

4

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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)? – berimbolo Apr 18 '19 at 14:04
  • `AND pid <> pg_backend_pid()` in your example excludes the current session - which is irrelevant while the current session is to a different database ... – Erwin Brandstetter Apr 18 '19 at 14:11