How to Kill a PostgreSQL Session/Connection?
When managing a PostgreSQL database, it’s sometimes necessary to terminate a session or connection, especially when dealing with locking issues or unwanted connections. PostgreSQL provides a function called pg_terminate_backend() to terminate a specific session or connection.
The pg_terminate_backend() function terminates a connection to the PostgreSQL server. This function is particularly useful for ending connections causing locks or for sessions that are no longer needed. Only superusers or users with the appropriate permissions can terminate other sessions.
Syntax:
SELECT pg_terminate_backend(pid);
Explanation:
Example: Finding and Killing a Session
To terminate a specific session, first, identify the session you want to kill by querying the pg_stat_activity view, then use pg_terminate_backend() to end it.
Step 1: Find Active Sessions
Code:
-- List all active sessions
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity;
Explanation:
Step 2: Kill a Specific Session
Code:
-- Terminate a session by its PID
SELECT pg_terminate_backend(12345);
Explanation:
Full Example:
-- Step 1: Query active sessions to find the PID of the session to kill
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity;
-- Step 2: Terminate the session with a specific PID
-- Replace '12345' with the actual process ID of the session to terminate
SELECT pg_terminate_backend(12345);
Explanation
Note: Terminating a session with pg_terminate_backend() immediately closes the connection, which can lead to uncommitted changes being rolled back.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4