RetroSearch Browse

Showing content from https://www.w3resource.com/PostgreSQL/snippets/postgresql-kill-pid.php below:

PostgreSQL: Terminate Processes by PIDLast update on December 28 2024 13:04:45 (UTC/GMT +8 hours)

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:

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;

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