During day to day operation of PostgreSQL database administrator we may need to terminate a PostgreSQL session, in this article we are going to cover how to terminate a PostgreSQL session.
What is a PostgreSQL Session?
I would like to have a brief introduction of the terminology here, because in each database engine, name of a connection differs, like application on Db2. Each connection made to a database is called as a session in PostgreSQL.
When a java application server connects to our database with a connection pool consists of 10 connetions means 10 sessions will be open to our database.
Why Do We Need to Terminate a PostgreSQL Connection?
In some applications we face a connection leak due to bad application design. Or again, due to bad application design, sometimes applications don’t work as expected and cause our PostgreSQL server’s to consume high resources, or blocks other applications to complete operations on database.
Step by Step: Terminate a PostgreSQL Session
Let’s start with finding the session and proceed to terminating a session.
Finding the Session
PostgreSQL has some functions to manage sessions, you can reach documentation of those functions from this link.
pg_terminate_backend() function is function to be used to terminate a PostgreSQL session. Let’s have a look how can we decide the session to kill.
To list current session you need to query pg_stat_activiy view:
postgres=# select * from pg_stat_activity;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_star
t | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+----------+-----+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+--------------------
-----------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+---------------------------------+------------------------------
| | 31 | | | | | | | | 2023-10-16 18:56:49.774192+00 | |
| | Activity | AutoVacuumMain | | | | | | autovacuum launcher
| | 32 | | 10 | postgres | | | | | 2023-10-16 18:56:49.774344+00 | |
| | Activity | LogicalLauncherMain | | | | | | logical replication launcher
5 | postgres | 48 | | 10 | postgres | psql | | | -1 | 2023-10-16 18:57:17.118669+00 | 2023-10-16 18:57:47.414994+00 | 2023-10-16 18:57:47
.414994+00 | 2023-10-16 18:57:47.414995+00 | | | active | | 799 | | select * from pg_stat_activity; | client backend
| | 28 | | | | | | | | 2023-10-16 18:56:49.763763+00 | |
| | Activity | BgWriterMain | | | | | | background writer
| | 27 | | | | | | | | 2023-10-16 18:56:49.763643+00 | |
| | Activity | CheckpointerMain | | | | | | checkpointer
| | 30 | | | | | | | | 2023-10-16 18:56:49.773936+00 | |
| | Activity | WalWriterMain | | | | | | walwriter
(6 rows)
Table above is not being rendered perfectly, but you can copy it and paste to a text editor to have more clear view. Result of select * from pg_stat_activity query displays list of sessions and their current activities.
Termiate a Session:
And then, next step is very easy:
select pg_terminate_backend(pid) from pg_stat_acitivity, for instance to kill pid 31:
select pg_terminate_backend(31) from pg_stat_activity;
And query current sessions again to check if 31 still exists:
postgres=# select pid, leader_pid, usesysid, usename from pg_stat_activity;
pid | leader_pid | usesysid | usename
-----+------------+----------+----------
81 | | |
32 | | 10 | postgres
48 | | 10 | postgres
28 | | |
27 | | |
30 | | |
(6 rows)
As you can see, pid 31 is does not exist on the list anymore.
Hi! I’m an IT Specialist
I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.
Get in touch with me.
Leave a Reply