Posted on: November 13, 2020 11:55 AM
Posted by: Renato
Categories: postgresql backend linux
Views: 1298
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";
Donate to Site
Renato
Developer
-
Renato de Oliveira Lucena - há 4 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’;
