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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics