Kill PostgreSQL Session or Connection

Why we will session or connection?
If connection is in hung state.
If connection consumes more resources.
If connection(user pid) blocks other resources.

Solution: Kill the culprit process id. To kill any connection, the user who is going to kill pid(connection) must be super user in PostgreSQL.

To get the list of processes connected to PostgreSQL, use pg_stat_activity table:

Run select * from pg_stat_activity or run following query with columns of pg_stat_activity table.

select datname,pid,usename,application_name,state from pg_stat_activity;

Output:

datname | pid | usename | application_name | state ----------+-------+----------+------------------+-------- postgres | 15732 | postgres | psql | active company | 2628 | k2admin | psql | idle

Now, we want to kill culprit pid 2628. Which is causing my server performance. Then, run following query

select pg_terminate_backend(2628) from pg_stat_activity;

Output:

pg_terminate_backend ---------------------- t t (2 rows)

or Run the following query

SELECT pg_terminate_backend(2628) FROM pg_stat_activity WHERE -- don't kill my own connection! pid <> pg_backend_pid() -- kill the connections to connected to only the database 'company' AND datname = 'company' ;

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: