Killing an active PostgreSQL Session or Connection

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.

Description

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:

• pid: The process ID of the session to terminate. You can find this using the pg_stat_activity view.

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:

• pid: Process ID for each session.
• usename: Username for the connection.
• application_name: Name of the client application.
• client_addr: IP address of the client.
• state: Current state of the connection (e.g., active, idle).

Step 2: Kill a Specific Session

Code:

-- Terminate a session by its PID
SELECT pg_terminate_backend(12345);

Explanation:

• Replace 12345 with the pid of the session you want to terminate. This immediately ends the session.

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

• Finding Active Sessions: This command retrieves all active sessions in PostgreSQL, allowing you to identify connections based on criteria like usename or state.
• Terminating a Session: The pg_terminate_backend() function takes the pid as an argument and terminates the specified session.

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.