How to Kill a Process ID in PostgreSQL

Users write queries to perform specific database operations/actions in PostgreSQL. There may be cases when it becomes necessary to terminate a running query. This can occur when the user interface stops responding, but the query process continues to run. Also, it may be necessary to terminate a query that is taking an unusually long time to complete. To deal with such situations, PostgreSQL provides a couple of commands that help us terminate the process or halt its further execution.

This article will thoroughly cover the method to kill a process ID in PostgreSQL.

How to Kill a Process ID in PostgreSQL?

Killing a process involves several steps. First, we will be getting all the currently running queries in the PostgreSQL and then we will kill them. We will go through the following steps to kill a specific process using its ID:

Find the Running Processes
Find Non-Idle Processes
Kill the Process ID
Verify Process Termination

Let’s begin with finding the running processes.

Step 1: Find the Running Processes

The first step is to find the currently running processes in Postgres. To do that, use the query:

SELECT * 
FROM pg_stat_activity;

In the above query, the “pg_stat_activity” view is used to get all the database connections to respond to the PostgreSQL server. Most importantly, it is used to get all the currently running queries/processes in PostgreSQL.

The query will return the detailed table showing the running queries/processes. In our case, the result set looks like this:

Let’s break down the above view so that we can understand what the table contains.

datename - It represents the name of databases.
pid - It indicates the process id.
Usename - usename is the name of the user in the PostgreSQL server.
Client_addr - client_addr specifies the client address which is shown as the client IP. Using this IP address, the client and server establish a connection.
Client_port - The client _port specifies the port numbers for all clients that are available on the server.
Xact_start - The xact_start means the start time transaction.
Backend_start - The backend _start column specifies the start time of the respective process when the client is connected.
Query_start - The query_start shows the time when the query begins/starts its execution.
State - This specifies the current status of the process/transaction that can be active or idle.
Query - This shows all executed queries.

In the above query, all columns of the “pg_stat_activity” view were being fetched. Let’s make the query concise and fetch only those columns that are necessary for getting the process information:

SELECT datname, pid, usename, query
FROM pg_stat_activity;

Step 2: Find Non-Idle Processes/Queries

To show all the non-idle queries we will execute the following command:

SELECT datname, pid, usename, query 
FROM pg_stat_activity 
WHERE STATE !='idle';

The result set contains all the non-idle queries at the current instant.

The output shows all the non-idle processes. Similarly, we can also check the active processes by executing the following query:

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

On successful execution, the stated query will retrieve all the currently active queries.

Step 3: Kill the Process ID

Now you have found all the idle and non-idle processes (process IDs). These processes can be killed by using their “PID”. For this particular purpose, use two basic queries to kill a process. The first one is the “pg_cancel_backend(pid)” and the other one is the “pg_terminate_backend(pid)”.

We always run the “pg_cancel_backend(pid)” query first. The syntax for killing a process having “pid = 20280” is

SELECT pg_cancel_backend(20280);

We run the “pg_cancel_backend(pid)” query before the “pg_terminate_backend(pid)” because the “pg_cancel_backend(pid)” query is safer than the “pg_terminate_backend(pid)”. The “pg_cancel_backend(pid)” tends to be lenient and takes more time to run.

SELECT pg_terminate_backend(20280);

It is important to note that the pg_terminate_backend() can terminate the whole process leading to the restart of the whole database.

Step 4: Verify Process Termination

Execute the below-stated query to verify if the selected process has been killed or not:

SELECT datname, pid, usename, query 
FROM pg_stat_activity WHERE STATE !='idle';

The absence of the “20280” process from the pid column proves the termination of the selected process.

Long-running PostgreSQL Queries: How to Find and Kill Them?

In this example, we will let you know how a long-running query can be killed in PostgreSQL. For a profound understanding, consider the following stepwise demonstration:

Find the Long-Running Queries
Stop the Long-Running Queries
Kill the Long-Running Queries
Verify Queries Termination

Let’s start with finding the long-running queries.

Step 1: Find the Long-Running Queries

In the following Code snippet, the pg_stat_activity view will be utilized to find all the queries that have been running from the last ten minutes:

SELECT
  pid AS process_id,
  now() - pg_stat_activity.query_start AS query_duration,
  query, state
 FROM pg_stat_activity
 WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes';

In this code block,

- First, the process IDs are fetched using the pg_stat_activity view.

- After that, the query’s running duration is computed by subtracting the query’s start time from the current DateTime.

- Finally, the queries and respective states are fetched.

Step 2: Stop the Long-Running Queries

Now use the “pg_cancel_backend” to stop any particular query using its process id, as follows:

SELECT pg_cancel_backend(11404);

The above code block will cancel the query having process id “11404”:

The Boolean “true” verifies the cancellation of the selected process.

Step 3: Kill the Long-Running Queries

Now, use the pg_terminate_backend() with the process ID to kill the selected long-running query:

SELECT pg_terminate_backend(11404);

The Boolean true proves that the selected long-running query has been successfully terminated.

Step 4: Verify Queries Termination

You can confirm the termination of the selected query by executing the following code:

SELECT
  pid AS process_id,
  now() - pg_stat_activity.query_start AS query_duration,
  query, state
 FROM   pg_stat_activity
 WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes';

The stated query retrieves all those queries whose running time is more than ten minutes:

From the output snippet, it can be noted that the query having a process ID equal to “11404” has been successfully terminated.

Final Thoughts

To kill a process in PostgreSQL, first, find out the “pid” for all the processes by using the “pg_stat_activity” view. The pg_stat_activity view retrieves detailed information about the processes. After that, run the “pg_cancel_backend(pid)” and “pg_terminate_backend(pid)” commands to kill the selected processes. This article has covered the complete procedure of killing a process using step-by-step demonstration.

How to Kill a Process ID in PostgreSQL