Postgresql 9.4: System tables ‘pg_stat_activity’ and ‘pg_database’, Cancel/Kill/Stop connections queries to a database.

You can view the status of connections to the postgresql database and the status of the queries. This info can be sorted by different fields.

      Column      | Modifiers
------------------+-----------
 datid            |
 datname          | The database name
 pid              | The pid number
 usesysid         |
 usename          | The username
 application_name |
 client_addr      | The IP address connected
 client_hostname  |
 client_port      | The port used
 backend_start    | The time at which the database was accessed by the client
 xact_start       | The time at which the transaction started to run. Should always have occurred before the query_start time 
 query_start      | The time the actual query started 
 state_change     |
 waiting          |
 state            |
 backend_xid      |
 backend_xmin     |
 query            | The actual query

View queries for specific user

select 
datname, pid, usename, client_addr, query_start, query
from pg_stat_activity where usename = 'seeker';
datname     | servers
pid         | 21694
usename     | seeker
client_addr | 10.0.0.19
query_start | 2015-10-16 15:59:08.234508-04
query       | INSERT INTO IP_DNS_SCAN(hostname, ipaddress, remote_ip, pingStatus, hostDNScheck) VALUES ('superman.sfentona.lol','10.0.
0.19','10.0.0.54','UP','wordpress.sfentona.lol.')

You can cancel or Kill queries by using pg_terminate_backend or pg_cancel_backend

Cancel or Kill the queries of a specific pid

select pg_terminate_backend(pid) 
from pg_stat_activity 
where pid = 24297;

Cancel or Kill the queries of a specific user

select pg_terminate_backend(pid)
from pg_stat_activity 
where usename = 'gmastrokostas';

Kill all queries besides your own

select pg_terminate_backend(pid)
from pg_stat_activity
where usename <> 'gmastrokostas';

Disallow all new connections to a specific Database

update pg_database 
set datallowconn = FALSE 
wnere datname = 'name_your_database';
Share Button

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload the CAPTCHA.