SELECT pid, usename, datname, state, query FROM pg_stat_activity;
SELECT pg_cancel_backend(pid); -- To gently cancel a query
-- OR
SELECT pg_terminate_backend(pid); -- To forcefully terminate a connection
pid with the process ID of the connection you want to cancel or terminate.max_connections:SHOW max_connections;
max_connections value in postgresql.conf and reload the configuration. Note that this requires access to the server config and will affect memory usage, so it should be done with caution.SELECT a.datname, l.relation::regclass, l.mode, l.locktype, a.query, a.pid, age(now(), a.query_start) AS "age", a.usename
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.datname = 'your_database_name' AND NOT l.granted;
'your_database_name' with your actual database name.SELECT pid, query, state, start_time, now() - query_start AS duration
FROM pg_stat_activity
WHERE (now() - query_start) > interval '5 minutes';
Remember, while the above actions can provide immediate relief, it’s crucial to investigate the root cause thoroughly to implement a long-term solution.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)
