PostgreSQL – How to kill database sessions

PostgreSQL – How to kill database sessions

From session #1

[root@postgresql ~]# su - weishan
[weishan@postgresql ~]$ psql -d testdb
psql (9.3.6)
Type "help" for help.
testdb=# SELECT datname as database, pid as pid, usename as username, application_name as application, client_addr as client_address, query FROM pg_stat_activity;
database | pid | username | application | client_address | query 
----------+-------+----------+-------------+----------------+-----------------------------------------
 testdb | 11834 | weishan | psql | | SELECT datname as database, +
 | | | | | pid as pid, +
 | | | | | usename as username, +
 | | | | | application_name as application,+
 | | | | | client_addr as client_address, +
 | | | | | query +
 | | | | | FROM pg_stat_activity;
(1 row)

We can see the username logged in is “weishan” and the SQL statement being executed as well.

From session #2

[root@postgresql ~]# su - postgres
-bash-4.1$ psql -d testdb
psql (9.3.6)
Type "help" for help.
testdb=# SELECT datname as database, pid as pid, usename as username, application_name as application, client_addr as client_address, query FROM pg_stat_activity;
database | pid | username | application | client_address | query 
----------+-------+----------+-------------+----------------+--------------------------------------------------------------
 testdb | 11834 | weishan | psql | | SELECT datname as database, +
 | | | | | pid as pid, +
 | | | | | usename as username, +
 | | | | | application_name as application, +
 | | | | | client_addr as client_address, +
 | | | | | query +
 | | | | | FROM pg_stat_activity;
 testdb | 12067 | postgres | psql | | SELECT datname as database, pid as pid, usename as username,+
 | | | | | application_name as application, +
 | | | | | client_addr as client_address, query +
 | | | | | FROM pg_stat_activity;
(2 rows)

We can now see 2 sessions in the database, session #1 and session #2.

To kill the session, use PG_TERMINATE_BACKEND function.

testdb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='weishan';
 pg_terminate_backend 
----------------------
 t
(1 row)

From session #1

testdb=# SELECT datname as database, pid as pid, usename as username, application_name as application, client_addr as client_address, query FROM pg_stat_activity;
server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

We can see that session #1 has been disconnect and auto-connection was being made.

Cheers,
Wei Shan

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s