w3resource

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/postgresql-kill-pid.php