PostgreSQL: Terminate Processes by PID

PostgreSQL: How to Kill a Process by PID

PostgreSQL allows terminating processes using their Process ID (PID). This is useful to stop long-running queries or handle stuck transactions.

1. Find the Target PID

To identify the PID of the problematic process, query the pg_stat_activity view:

Code:

SELECT pid, usename, datname, state, query  
FROM pg_stat_activity; 

This shows the active processes, including their PIDs, associated users, databases, states, and queries.

2. Terminate a Process by PID

Use the pg_terminate_backend() function to terminate the process:

Code:

SELECT pg_terminate_backend(<pid>); 

Replace <pid> with the actual PID.

For example:

Code:

SELECT pg_terminate_backend(12345);  

3. When to Use pg_terminate_backend()

Use it when:

• A query or transaction is consuming excessive resources.
• A process is stuck in an unresponsive state.
• There’s a need to free up connections for maintenance.

4. Alternative: Cancel a Query Without Termination

If you only want to stop the query but keep the session active, use pg_cancel_backend():

Code:

SELECT pg_cancel_backend(<pid>);  

This cancels the ongoing query without disconnecting the user.

5. Important Notes

• Permissions: You must have superuser privileges to terminate other users' processes.
• Impact: Terminating a process can cause data rollback if the process was in the middle of a transaction.
• Avoid Overuse: Frequent use can disrupt normal database operations.

Example Scenario

Step 1: Identify the Process

Code:

SELECT pid, usename, state, query  
FROM pg_stat_activity  
WHERE state = 'active';  

Step 2: Terminate the Process

Code:

SELECT pg_terminate_backend(23456);  

Step 3: Verify

Check if the process is terminated:

Code:

SELECT pid, state  
FROM pg_stat_activity;   

All PostgreSQL Questions, Answers, and Code Snippets Collection.