Share via:


In PostgreSQL, you can terminate or “kill” a session using the pg_terminate_backend function. This function allows you to forcefully terminate a specific session, which can be helpful in scenarios where a session is unresponsive or causing issues.
We can kill PostgreSQL session in two ways:
1. Pg_cancel_backend()
it takes time to kill the user session.
2. Pg_terminate_backend()
Immediately it kills the entire process.
To kill a session in PostgreSQL, you’ll need to follow these steps:
1. Pg_stat_activity:
You can use the pg_stat_activity view to monitor active sessions and their associated Process IDs. This view provides information about all active connections to the PostgreSQL server, including the PID, user, current query, and more. Below command provide catalog view of pg_stat_activity.

2. Identify the session to be killed:
Run the query to get information about the current sessions. This query will list all active connections to the PostgreSQL server, including the database name, PID (Process ID) of the backend process, the username, the application name, the current state of the connection, and the current query being executed.

3. Terminate the session:
Once you identified the session to be terminated use the pg_terminate_backend or pg_cancel_backend function by providing respective process id (PID). Here is the command to kill session.

4. Check if the session was terminated:
You can re-run the query from Step 2 to verify if the session has been terminated. The session should no longer be present in the pg_stat_activity view.

In the above, the process with ID 3674 was terminated. As a result, the user or application associated with that process was disconnected from the PostgreSQL database.
WAL write ahead logging:
It is a method of recording changes made to a PostgreSQL database in a sequential log, commonly referred to as the WAL log or the transaction log. Instead of writing changes directly to the database files, PostgreSQL first writes them to the WAL log. This logging approach ensures that changes are durably recorded before they are applied to the actual data files, which helps safeguard the data in case of crashes or failures.
In PostgreSQL, the pg_wal directory, also known as the WAL (Write-Ahead Logging) directory, is a critical component of the Write-Ahead Logging mechanism. It stores the WAL files that record changes made to the database before those changes are applied to the actual data files. The pg_wal directory is usually located within the PostgreSQL data directory.

Log Sequence Number (LSN)
LSNs are a unique identifier for a location in the WAL, and they are crucial for various operations such as replication, backup, and point-in-time recovery. Run the below command to retrieve the current WAL LSN number.

The LSN consists of two parts separated by a forward slash ‘/’. The ‘0’ before the slash represents the timeline, and ‘16EC6F8’ represents the position or offset within that timeline.
WAL file:
To find the WAL file name corresponding to a specific LSN (Log Sequence Number), you can use the pg_walfile_name() function in PostgreSQL. In your case, where the LSN is 0/16EC6F8, Run the following query to get the WAL file name:

Below is the command that prints both the current Write-Ahead Logging (WAL) Log Sequence Number (LSN) and its corresponding WAL file name.

Switching WAL segment:
In PostgreSQL, switching to a new WAL segment is typically an automatic process that occurs when the current segment is full. However, you can manually trigger a switch using pg_switch_wal() if needed.

The LSN ‘0/16EE720’ represents the position in the current WAL segment, and ‘0/16EE738’ represents the position in the new WAL segment.


Author    : Prudhvi Teja
LinkedIn :
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook  :
LinkedIn    :
Twitter       :
YouTube   :
Instagram  :
Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment