Comment supprimer une base de données PostgreSQL si des connexions y sont actives ?

Question

Je dois écrire un script qui abandonne une base de données PostgreSQL. Il peut y avoir beaucoup de connexions à celle-ci, mais le script doit l'ignorer.

La norme DROP DATABASE db_name La requête ne fonctionne pas lorsqu'il y a des connexions ouvertes.

Comment puis-je résoudre le problème ?

Answer 1

Cela supprimera les connexions existantes, sauf la vôtre :

Requête pg_stat_activity et obtenez les valeurs de pid que vous voulez tuer, puis lancez SELECT pg_terminate_backend(pid int) à eux.

PostgreSQL 9.2 et supérieur :

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' --  change this to your DB
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 et inférieur :

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' --  change this to your DB
  AND procpid <> pg_backend_pid();

Une fois que vous avez déconnecté tout le monde, vous devez vous déconnecter et lancer la commande DROP DATABASE à partir d'une connexion d'une autre base de données (pas celle que vous essayez de supprimer).

Notez le changement de nom de la procpid colonne à pid . Voir ce fil de la liste de diffusion .

Answer 2

Dans PostgreSQL 9.2 et plus, pour déconnecter tout sauf votre session de la base de données à laquelle vous êtes connecté :

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

Dans les anciennes versions, c'est la même chose, il suffit de modifier pid à procpid . Pour se déconnecter d'une autre base de données, il suffit de changer current_database() au nom de la base de données dont vous voulez déconnecter les utilisateurs.

Vous pouvez REVOKE le site CONNECT des utilisateurs de la base de données avant de déconnecter les utilisateurs, sinon les utilisateurs continueront à se reconnecter et vous n'aurez jamais la possibilité de supprimer la base de données. Voir ce commentaire et la question à laquelle elle est associée, Comment détacher tous les autres utilisateurs de la base de données ? .

Si vous souhaitez simplement déconnecter les utilisateurs inactifs, voir cette question .

Answer 3

Vous pouvez tuer toutes les connexions avant d'abandonner la base de données en utilisant la commande pg_terminate_backend(int) fonction.

Vous pouvez obtenir tous les backends en cours d'exécution en utilisant la vue système pg_stat_activity

Je ne suis pas tout à fait sûr, mais ce qui suit tuerait probablement toutes les sessions :

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'

Bien sûr, vous n'êtes peut-être pas connecté vous-même à cette base de données.

Answer 4

En fonction de votre version de postgresql, vous pouvez rencontrer un bug, qui rend pg_stat_activity pour omettre les connexions actives des utilisateurs abandonnés. Ces connexions ne sont pas non plus affichées dans pgAdminIII.

Si vous effectuez des tests automatiques (dans lesquels vous créez également des utilisateurs), cela peut être un scénario probable.

Dans ce cas, vous devez revenir à des requêtes comme :

 SELECT pg_terminate_backend(procpid) 
 FROM pg_stat_get_activity(NULL::integer) 
 WHERE datid=(SELECT oid from pg_database where datname = 'your_database');

NOTE : Dans la version 9.2+, vous devrez modifier procpid à pid .

Answer 5

J'ai remarqué que postgres 9.2 appelle désormais la colonne pid plutôt que procpid.

J'ai tendance à l'appeler depuis la coquille :

#!/usr/bin/env bash
# kill all connections to the postgres server
if [ -n "$1" ] ; then
  where="where pg_stat_activity.datname = '$1'"
  echo "killing all connections to database '$1'"
else
  echo "killing all connections to database"
fi

cat <<-EOF | psql -U postgres -d postgres 
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
${where}
EOF

J'espère que cela vous sera utile. Merci à @JustBob pour le sql.