Cerrando conexiones en PostgreSQL

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.