| Arrêter / tuer une requête dans Postgresql |
| Article original Publié le : 10 février 2020 Mise a jour le : – |
Suite a l’exécution d’une requête qui n’en fini pas, le serveur load et dépasse les 1.50, le cpu monte a 100% sur une vm pas trop sizé.
Pour killer le process de la requête, il faut connaître son PID
Depuis la db, il faut chercher dans la table pg_stat_activity, et filtrer sur le status actif.
|
1 |
# SELECT * FROM pg_stat_activity WHERE state = 'active'; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
datid | datname | <strong>pid</strong> | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | query -------+---------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------- -----------------------+-------------------------------+---------+--------+-------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- 16384 | nom_db | <strong>17822</strong> | 10 | postgres | psql | | | -1 | 2020-02-10 14:50:40.344376+01 | 2020-02-10 14:50:49.676393+01 | 2020-02 -10 14:50:56.531487+01 | 2020-02-10 14:50:56.531497+01 | f | active | select workspace_name,login,max(session_creation_date) as last_connection from account_session JOIN account u sing (account_id) JOIN workspace using (workspace_id) where session_creation_date < now() - INTERVAL '1 YEAR' and workspace_name like 'sg-%' and account_id not in (select account_id fro m account_session where session_creation_date > now() - INTERVAL '1 YEAR') group by workspace_name,login order by 1,2; 16384 | nom_db | 21001 | 10 | postgres | psql | | | -1 | 2020-02-10 16:39:23.114492+01 | 2020-02-10 16:44:04.517476+01 | 2020-02 -10 16:44:04.517476+01 | 2020-02-10 16:44:04.517483+01 | f | active | SELECT * FROM pg_stat_activity WHERE state = 'active'; (2 lignes) |
Puis killer le pid qui correspond a la requête
|
1 |
# SELECT pg_cancel_backend (17822); |
|
1 2 3 4 |
pg_cancel_backend ------------------- t (1 ligne) |
Si le process ne veux pas se killer, utiliser la commande
|
1 |
# SELECT pg_terminate_backend(pid); |
Source: https://stackoverflow.com/questions/35319597/how-to-stop-kill-a-query-in-postgresql