Sysadmin Tips y trucos

Cerrando conexiones en PostgreSQL

single image


Suele ocurrir que tras instalar PostgreSQL creamos bases de datos de prueba, y que después de juguetear deseamos eliminarlas. En PostgreSQL eliminar la base de datos es sencillo, pero puede ocurrir se mantengan conexiónes sin actividad (IDLE) a la base de datos, y no nos permita borrarla.

En PostgreSQL podemos solventarlo desde el cliente psql con la función pg_terminate_backend . Cada conexión a la base de datos en PostgreSQL tiene asociado un proceso y esta información se almacena en la tabla pg_stat_activity. La función pg_terminate_backend nos permite enviar una señal a dicho proceso para que finalice.

En primer lugar, nos conectamos a nuestro servidor de bases de datos PostgreSQL

$ psql -h servidor -U postgres -d postgres
sql (9.1.2, server 8.4.11)
Type "help" for help.
 
postgres=#

Ejecutamos la consulta que nos proporcionará el listado de todas las conexiones establecidas a nuestra base de datos, en este caso db_testing:

Este paso es opcional, pero realizarlo siempre nos dará una idea de las conexiones existentes, y si están activas, o solo permanece la conexión (IDLE).

postgres=# SELECT  * FROM pg_stat_activity WHERE datname='db_testing';
 
 datid  |  datname   | procpid | usesysid | usename  |                        current_query                         | waiting |          xact_start           |          query_start          |         backend_start         |  client_addr  | client_port 
--------+------------+---------+----------+----------+--------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+---------------+-------------
 166976 | db_testing |    1952 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:16:26.378121+01 | 2012-03-05 12:16:25.962237+01 | 192.168.0.112 |       36493
 166976 | db_testing |    1959 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:37:51.801532+01 | 2012-03-05 12:17:51.516567+01 | 192.168.0.112 |       36520
 166976 | db_testing |    1961 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:39:51.82745+01  | 2012-03-05 12:17:58.394679+01 | 192.168.0.112 |       36521
 166976 | db_testing |    1962 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:18:10.426506+01 | 2012-03-05 12:17:59.696756+01 | 192.168.0.112 |       36523
 166976 | db_testing |    1963 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:40:51.836997+01 | 2012-03-05 12:17:59.942576+01 | 192.168.0.112 |       36527
 166976 | db_testing |    1964 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:38:51.805679+01 | 2012-03-05 12:18:09.922962+01 | 192.168.0.112 |       36537
 166976 | db_testing |    1965 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:41:31.907741+01 | 2012-03-05 12:18:09.963128+01 | 192.168.0.112 |       36538
 166976 | db_testing |    1979 |   106430 | tad     |                                                         | f       |                               | 2012-03-05 12:31:51.732619+01 | 2012-03-05 12:21:30.990653+01 
 
(8 ROWS)

Como vemos existen 8 registros en la tabla pg_stat_activity, que se corresponden con 8 conexiones establecidas pero sin actividad (IDLE).
Para cada conexión se almacena además del PID del proceso procpid, IP y puerto, usuario de conexión, query lanzada, estado, …

Una vez obtenida la lista con los procesos, solo queda utilizar la funcion pg_terminate_backend(procid).
Su uso es sencillo, si deseamos eliminar una determinada conexión le pasamos a la función, el procid de dicha conexión:

postres=# SELECT pg_terminate_backend(1952);
 pg_terminate_backend 
----------------------
 t
(1 ROW)

También podemos eliminar todas las conexiones de una determinada base de datos, con una única instrucción:

postgres=# SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='db_testing';
 
 pg_terminate_backend 
----------------------
 t
 t
 t
 t
 t
 t
 t
(7 ROWS)

Es importante ser cuidadoso, seleccionar la base de datos correcta, y tener en cuenta que si hay conexiones activas, también serán eliminadas a menos que lo indiquemos explícitamente.

2 Comentarios
  1. Alex Gutierrez 4 años ago
    Contestar

    Muchas gracias, me fue de mucha ayuda.

    • Pablo Baenas 4 años ago
      Contestar

      jeje me alegro. Hace ya tiempo que tengo el blog algo desatentido. A ver si puedo ir subiendo más cosas.

      saludos!!

Escribir comentario

Tu email no será publicado.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Otras noticias de interés