KILLING SESSIONS:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
postgres=# \d pg_stat_activity View "pg_catalog.pg_stat_activity" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- datid | oid | | | datname | name | | | pid | integer | | | usesysid | oid | | | usename | name | | | application_name | text | | | client_addr | inet | | | client_hostname | text | | | client_port | integer | | | backend_start | timestamp with time zone | | | xact_start | timestamp with time zone | | | query_start | timestamp with time zone | | | state_change | timestamp with time zone | | | wait_event_type | text | | | wait_event | text | | | state | text | | | backend_xid | xid | | | backend_xmin | xid | | | query | text | | | backend_type | text | | | |
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.
|
1 2 3 4 5 6 7 8 9 |
postgres=# select datname,pid,usename,application_name,state,query from pg_stat_activity; datname | pid | usename | application_name | state | query ----------+------+----------+------------------+--------+----------------------- | 3586 | postgres | | | postgres | 3589 | postgres | psql | active | select datname,pid,usename, application_name,state,query from pg_stat_activity; odxa | 3674 | abc | psql | idle | | 3584 | | | | | 3583 | | | | | 3585 | | | | |
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.
|
1 2 3 4 5 |
postgres=# select pg_terminate_session(3674); pg_terminate_backend ---------------------- t (1 row) |
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.
|
1 2 3 4 5 6 7 8 |
postgres=# select datname,pid,usename,application_name,state,query from pg_stat_activity; datname | pid | usename | application_name | state | query ----------+------+----------+------------------+--------+------------------ | 3586 | postgres | | | postgres | 3589 | postgres | psql | active | select datname,pid,usename, application_name,state,query from pg_stat_activity; | 3584 | | | | | 3583 | | | | | 3585 | | | | |
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.
Pg_wal:
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.
|
1 2 3 4 5 |
[postgres@postgres1 ~]$ cd /u01/data1 [postgres@postgres1 data1]$ ls base pg_commit_ts pg_ident.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts current_logfiles pg_dynshmem pg_log pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid global pg_hba.conf pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf |
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.
|
1 2 3 4 5 |
postgres=# select pg_current_wal_lsn(); pg_current_wal_lsn -------------------- 0/16EC6F8 (1 row) |
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:
|
1 2 3 4 5 |
postgres=# SELECT pg_walfile_name('0/16EC6F8'); pg_walfile_name -------------------------- 000000010000000000000001 (1 row) |
Below is the command that prints both the current Write-Ahead Logging (WAL) Log Sequence Number (LSN) and its corresponding WAL file name.
|
1 2 3 4 5 |
postgres=# SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()); pg_current_wal_lsn | pg_walfile_name --------------------+-------------------------- 0/16EE720 | 000000010000000000000001 (1 row) |
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.
|
1 2 3 4 5 |
postgres=# SELECT pg_switch_wal(); pg_switch_wal --------------- 0/16EE738 (1 row) |
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 : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
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 : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplat
Note: Please test scripts in Non Prod before trying in Production.
Loading...
