Comment supprimer une base de données PostgreSQL s'il y a des connexions actives?
je dois écrire un script qui supprimera une base de données PostgreSQL. Il y a peut-être beaucoup de connexions, mais le script devrait l'ignorer.
la requête standard DROP DATABASE db_name ne fonctionne pas lorsqu'il y a des connexions ouvertes.
comment résoudre le problème?
10 réponses
ceci supprimera les connexions existantes sauf la vôtre:
Requête pg_stat_activity et obtenir le pid valeurs que vous voulez tuer, puis le problème SELECT pg_terminate_backend(pid int) .
PostgreSQL 9.2 et plus:
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 aurez déconnecté tout le monde, vous devrez déconnecter et lancer la base de données DROP commande à partir d'une connexion à partir d'une autre base de données alias pas celui que vous essayez de laisser tomber.
Note le changement de nom de la procpid colonne pid . Voir ce fil de liste de diffusion .
dans PostgreSQL 9.2 et au-dessus, 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 versions plus anciennes c'est la même chose, il suffit de changer pid en procpid . Pour déconnecter d'une base de données différente, il suffit de changer current_database() au nom de la base de données dont vous voulez déconnecter les utilisateurs.
vous pouvez vouloir REVOKE le CONNECT droit des utilisateurs de la base de données avant déconnecter les utilisateurs, sinon les utilisateurs vont juste continuer à se reconnecter et vous n'aurez jamais la chance de laisser tomber la base de données. Voir ce commentaire et la question qui lui est associée, Comment puis-je détacher tous les autres utilisateurs de la base de données .
si vous voulez simplement déconnecter les utilisateurs inactifs, voir cette question .
vous pouvez tuer toutes les connexions avant de laisser tomber la base de données en utilisant la fonction pg_terminate_backend(int) .
vous pouvez obtenir tous les retours en arrière 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 ne pouvez pas être connecté vous-même à cette base de données
j'ai remarqué que postgres 9.2 appelle maintenant la colonne pid plutôt que procpid.
j'ai tendance à l'appeler du shell:
#!/usr/bin/env bash
# kill all connections to the postgres server
if [ -n "" ] ; then
where="where pg_stat_activity.datname = ''"
echo "killing all connections to database ''"
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
l'Espérance qui est utile. Merci à @JustBob pour le sql.
selon votre version de postgresql, vous pourriez rencontrer un bug, qui fait pg_stat_activity pour omettre les connexions actives des utilisateurs abandonnés. Ces connexions ne sont pas non plus indiquées dans pgAdminIII.
si vous faites des tests automatiques (dans lesquels vous créez aussi des utilisateurs), cela pourrait ê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: En 9.2+ vous aurez une modification procpid à pid .
dans L'invite de commande Linux, j'arrêterais d'abord tous les processus postgresql qui tournent en liant cette commande sudo / etc / init.d / PostgreSQL redémarrage
, tapez la commande bg pour vérifier si d'autres processus postgresql sont toujours en cours
suivi de dropdb dbname pour supprimer la base de données
sudo /etc/init.d/postgresql restart
bg
dropdb dbname
Cela fonctionne pour moi sur linux invite de commande
je viens de redémarrer le service à Ubuntu pour déconnecter les clients connectés.
sudo service postgresql stop
sudo service postgresql start
psql
DROP DATABASE DB_NAME;
PostgreSQL 9.2 et plus:
SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'
Voilà mon hack... = D
# Make sure no one can connect to this database except you!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "UPDATE pg_database SET datallowconn=false WHERE datname='<DATABASE_NAME>';"
# Drop all existing connections except for yours!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<DATABASE_NAME>' AND pid <> pg_backend_pid();"
# Drop database! =D
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "DROP DATABASE <DATABASE_NAME>;"
j'ai mis cette réponse parce que inclure une commande (ci-dessus) pour bloquer les nouvelles connexions et parce que toute tentative avec la commande ...
REVOKE CONNECT ON DATABASE <DATABASE_NAME> FROM PUBLIC, <USERS_ETC>;
... ne fonctionne pas pour bloquer les nouvelles connexions!
merci à @araqnid @GoatWalker ! = D
dans mon cas, j'ai dû exécuter une commande pour supprimer toutes les connexions, y compris ma connexion active administrateur
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
qui a mis fin à toutes les connexions et m'a montré une fatale "erreur" message:
FATAL: terminating connection due to administrator command SQL state: 57P01
après cela, il a été possible de supprimer la base de données