How to Kill a Process ID in PostgreSQL

Bilal Shahid Feb 02, 2024
How to Kill a Process ID in PostgreSQL

Today, we’ll learn how to kill or stop a running query in the background while using the PostgreSQL database.

If the front-end ceases to work and the background process is still running, this may happen. In that scenario, you may want to kill the process.

Use the pg_cancel_backend to Kill a Process ID in PostgreSQL

First of all, view all the running tasks using this command:

select * from pg_stat_activity;

Now, if you want to drop a task from here, use the query:

SELECT pg_cancel_backend(11080), pg_terminate_backend(11080) FROM pg_stat_activity WHERE state = 'active';

We used 11080 for the active process and terminated it using this statement. It will return to idle later on.

So, pg_cancel_backend tends to be a little lenient in terminating a process as it requires some time. But in many cases, it may come back running; hence we use the pg_terminate_backend to hard kill the process.

Do not call this the only active process in your system, as that may close off your current query. Hence, use these functions wisely and only when needed.

Instead of killing a process, you can always choose to terminate the current PostgreSQL session and restart it again.

Author: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!

GitHub