Posted on: November 13, 2020 11:55 AM

Posted by: Renato

Categories: postgresql backend linux

Views: 1165

Check and terminate active connections in PostgreSQL

To be able to list the number of active connections in PostgreSQL use the command:

select * from pg_stat_activity;

To be able to list the number of active connections in PostgreSQL use the command: You can use the count () command to count the connections.

select count(*) from pg_stat_activity;

It is possible to filter by Database by placing the desired bank in the “where” clause.

select * from pg_stat_activity where datname = ‘NOME_DO_BANCO’;

End Connections

To end connections you can use the function below, passing the pid number as a parameter. The pid is obtained by selecting from the pg_stat_activity table.

Attention: This command does not exist in versions of PostgreSQL below 8.4.

select pg_terminate_backend(pid);

Note: The “pid” column in versions less than 9.2 of PostgreSQL had the name “procpid”.

It is possible to end all connections except your connection through the query below.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

# Comandos Uteis:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'DB' AND pid <> pg_backend_pid() AND state = 'idle' AND state_change < current_timestamp - INTERVAL '5' MINUTE;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'DB' AND pid <> pg_backend_pid() AND state = 'idle' AND state_change < current_timestamp - INTERVAL '5' MINUTE;
select count(*) from pg_stat_activity;

SELECT "pid", "usename", "client_addr", "datname", application_name , EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - "query_start")::INTEGER, "state", "query" FROM "pg_stat_activity";

Renato

Developer

• 

  Renato de Oliveira Lucena - há 3 anos

  select datname, pid, usename, application_name, client_addr, client_hostname, backend_start from pg_stat_activity; select * from pg_stat_activity; SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid(); ## consultas que estão ociosas: Select * from pg_stat_activity where state=’idle’; Select pid, usename, application_name, backend_start, state_change, state from pg_stat_activity where state=’idle’;