¿Cómo cerrar conexiones inactivas en PostgreSQL automáticamente?

Este grupo de trabajo ha estado por horas buscando para darle resolución a tus preguntas, te ofrecemos la solución así que nuestro objetivo es resultarte de mucha ayuda.

Solución:

Para aquellos que estén interesados, esta es la solución que se me ocurrió, inspirada en el comentario de Craig Ringer:

(…) use un trabajo cron para ver cuándo la conexión estuvo activa por última vez (vea pg_stat_activity) y use pg_terminate_backend para eliminar las antiguas. (…)

La solución elegida se reduce así:

• Primero, actualizamos a Postgresql 9.2.
• Luego, programamos un hilo para que se ejecute cada segundo.
• Cuando se ejecuta el subproceso, busca conexiones inactivas antiguas.
  

  • Se considera una conexión inactivo si es Expresar es cualquiera idle, idle in transaction, idle in transaction (aborted) o disabled.
  • Se considera una conexión antiguo si es Expresar permaneció igual durante más de 5 minutos.

• Hay hilos adicionales que hacen lo mismo que el anterior. Sin embargo, esos hilos se conectan a la base de datos con un usuario diferente.
• Dejamos al menos una conexión abierta para cualquier aplicación conectada a nuestra base de datos. (rank() función)

Esta es la consulta SQL ejecutada por el hilo:

WITH inactive_connections AS (
    SELECT
        pid,
        rank() over (partition by client_addr order by backend_start ASC) as rank
    FROM 
        pg_stat_activity
    WHERE
        -- Exclude the thread owned connection (ie no auto-kill)
        pid <> pg_backend_pid( )
    AND
        -- Exclude known applications connections
        application_name !~ '(?:psql)|(?:pgAdmin.+)'
    AND
        -- Include connections to the same database the thread is connected to
        datname = current_database() 
    AND
        -- Include connections using the same thread username connection
        usename = current_user 
    AND
        -- Include inactive connections only
        state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
    AND
        -- Include old connections (found with the state_change field)
        current_timestamp - state_change > interval '5 minutes' 
)
SELECT
    pg_terminate_backend(pid)
FROM
    inactive_connections 
WHERE
    rank > 1 -- Leave one connection for each application connected to the database

Si está utilizando PostgreSQL> = 9.6, hay una solución aún más fácil. Supongamos que desea eliminar todas las conexiones inactivas cada 5 minutos, simplemente ejecute lo siguiente:

alter system set idle_in_transaction_session_timeout='5min';

En caso de que no tenga acceso como superusuario (ejemplo en la nube de Azure), intente:

SET SESSION idle_in_transaction_session_timeout = '5min';

Pero este último funcionará solo para la sesión actual, lo más probable es que no sea lo que desea.

Para desactivar la característica,

alter system set idle_in_transaction_session_timeout=0;

o

SET SESSION idle_in_transaction_session_timeout = 0;

(por cierto, 0 es el valor predeterminado).

Si utiliza alter systemdebe volver a cargar la configuración para iniciar el cambio y el cambio es persistente, ya no tendrá que volver a ejecutar la consulta si, por ejemplo, reiniciará el servidor.

Para comprobar el estado de la función:

show idle_in_transaction_session_timeout;

Conéctese a través de un proxy como PgBouncer que cerrará las conexiones después server_idle_timeout segundos.

Si conservas alguna desconfianza o forma de ascender nuestro noticia puedes ejecutar una observación y con gusto lo estudiaremos.

---

---

Utiliza Nuestro Buscador