Posted on

Postgres system queries

check active connections

SELECT 
    pid
    ,datname as database
    ,usename as username
    ,application_name
    ,client_addr as client_address 
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active'
order by datname;

close connections

SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'medical_center_sm'
;