Question Comment faire pour supprimer une base de données PostgreSQL s'il y a des connexions actives?


Je dois écrire un script qui déposera une base de données PostgreSQL. Il peut y avoir beaucoup de connexions, mais le script doit ignorer cela.

Le standard DROP DATABASE db_name La requête ne fonctionne pas quand il y a des connexions ouvertes.

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


495
2018-03-23 16:03


origine


Réponses:


Cela supprimera les connexions existantes sauf la vôtre:

Question pg_stat_activity et obtenir les valeurs de pid que vous voulez tuer, puis émettre SELECT pg_terminate_backend(pid int) pour 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 ci-dessous:

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 déconnectez tout le monde, vous devrez vous déconnecter et exécuter la commande DROP DATABASE à partir d'une connexion à partir 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 liste de diffusion.


864
2018-03-23 16:27



Dans PostgreSQL 9.2 et supérieur, 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 changer pid à procpid. Pour vous déconnecter d'une autre base de données, changez simplement current_database() au nom de la base de données dont vous souhaitez déconnecter les utilisateurs.

Vous voudrez peut-être REVOKE la CONNECT directement des utilisateurs de la base de données avant de déconnecter les utilisateurs, sinon les utilisateurs continueront simplement à se reconnecter et vous n'aurez jamais la possibilité de supprimer la base de données. Voir ce commentaire et la question à laquelle il est associé, Comment détacher tous les autres utilisateurs de la base de données.

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


95
2017-11-06 06:00



Vous pouvez tuer toutes les connexions avant de déposer la base de données en utilisant le 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 ne pouvez pas être connecté à cette base de données


24
2018-03-23 16:28



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

J'ai tendance à l'appeler depuis le shell:

#!/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 c'est utile. Merci à @JustBob pour le sql.


16
2017-10-23 03:35



Selon votre version de postgresql, vous pourriez rencontrer un bogue, ce qui pg_stat_activity 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 aux requêtes telles que:

 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 9.2+ vous aurez le changement procpid à pid.


12
2017-11-10 00:41



Dans la commande Linux Invite, j'arrêterais d'abord tous les processus postgresql qui sont en cours d'exécution en liant cette commande     sudo /etc/init.d/postgresql restart

tapez la commande     bg vérifier si d'autres processus postgresql sont toujours en cours d'exécution

puis suivi par dropdb dbname abandonner la base de données

sudo /etc/init.d/postgresql restart
bg
dropdb dbname

Cela fonctionne pour moi sur l'invite de commande linux


6
2017-08-19 09:23



PostgreSQL 9.2 et supérieur:

SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'


5
2017-11-22 17:39



Je viens de redémarrer le service dans Ubuntu pour déconnecter les clients connectés.

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;

4
2017-11-13 20:03



Voici 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>;"

Je mets cette réponse car 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

https://stackoverflow.com/a/3185413/3223785


3
2017-07-19 21:27