While working in PostgreSQL Database, we may encounter situations where some of the processes are halted or hanged, and we don’t want them anymore. Every database user will try to remove or terminate such connections from the database system in such a situation. PostgreSQL has come up with simple instructions to do that. It provides the pg_cancel_backed() and pg_terminate_backend() function to utilize the process ID for a particular query to cancel and terminates it along with the connection it holds within a few seconds. Within this guide, we will discuss using both functions in our database queries to kill the queries.
Using PostgreSQL PgAdmin GUI:
Let’s get started with the simple illustration of killing a session of a postgresql database using the process ID in the query. We will start from the pgAdmin GUI Interface of the PostgreSQL database. Open it using the search bar of your Windows 10 system. Add the password for your server and database. In our case, the database is “aqsayasin”. You have to open the “Query Tool” of your database using the query tool icon held at the top of pgAdmin GUI.
Let’s display all the sessions or processes of PostgreSQL on the query area. For that, you need to use the SELECT query with the “*” sign along with the keyword “pg_stat_activity”. Execute this query using the “triangle” sign at the pgAdmin query taskbar. All the current processes will be shown in your data Output area of pgAdmin as below. A total of 8 records have been found.
Let’s kill a process of ID “908”. We need to utilize two functions within the SELECT query on the query area to kill a process. The first one is pg_cancel_backend() function and second is pg_terminate_backend() function. The pg_cancel_backend() function is used to simply cancel out the query of a database using the process ID for a particular query. It doesn’t terminate the database connection. While the pg_terminate_backend() function cancels the query using the process ID for the query and shuts the connected database. So, we have been utilizing both the queries simultaneously on the same query tool to kill the process having ID “908”. On execution, we have got the Boolean value “true” beneath the “pg_terminate_background” column. This means the query and connection have been successfully terminated.
Let’s see if the selected query from its process ID has been terminated or not. For this, we have used the SELECT query again with the “pg_stat_activity” keyword. The output grid shows that the query “908” has been gone.
Let’s make it more clear by only selecting the queries having state equals to “idle”. The same query will be used with the WHERE clause to put the condition of “state = ‘idle’”. In return, we have got only two results for queries having an ‘idle’ state. Let’s kill the process ID “7316”.
To kill the query of process ID “7316”, we need to cancel it first using the same “SELECT” query with the “pg_cancel_backend()” function, taking process ID as an argument. Run the shown query in the query area with the run button held on the pgAdmin GUI taskbar. The output shows the Boolean value “true” under the column “pg_cancel_backend” column. This means the query for a particular process has been canceled finally.
Let’s terminate the query along with the database connection. So, the SELECT instruction has been used once more so far with the “pg_terminate_backend()” function. The process ID has been mentioned in the argument of the “pg_terminate_backend()” function. The output for this program displays the “true” Boolean value under the column “pg_terminate_backend”. This means the query having process ID “7316” has finally terminated, and the connection for this query is terminated along with it.
Let’s see if we can find the just canceled and terminated query having process ID 7316 on the output area or not. So, we have utilized the same SELECT query with the “pg_stat_activity” keyword and executed it on the query tool of PostregSQL PgAdmin. It doesn’t show the specified query/ process ID in the output, which states that it has gone already.
Using PostgreSQL Shell Console:
All we have done is kill the query with its connection within the pgAdmin GUI of PostgreSQL. We can also achieve it by using the PostgreSQL Shell terminal. Search for it in the Windows 10 application using the search bar on your desktop. Write “psql” and click on it upon showing. It will open up as a black screen asking you to add the local host’s name you own. Add that and press Enter. It will ask for the database name you want to work on. If not any, use the default “Postgres”. We have been using the “aqsayasin” database so far and port number 5432. We have added the username and its password already created in our database, i.e., aqsayasin. If you don’t have any user-created, go with the default “Postgres” username. After all the credentials have been added, your PostgreSQL shell is ready to be used.
Before killing any specific query with its process ID, we need to see the currently working, active, idle, and just presented queries and sessions of our database “aqsayasin”. Therefore, we will be using a “SELECT” command in the shell along with the information columns we want to display for the specific query via the pg_stat_Activity utility of the PostgreSQL database.
Let’s say you want to see the process ID of a query, the username by which this query has been executed, the database in which this query has been used, and the state of a query. We have stated all the column names we want to fetch for queries. The SELECT instruction has returned 9 records. We have a total of 1 active query and 3 idle queries/activities.
Let’s try removing the queries having a state “idle”. So we have been using the Process ID “10892” to remove the related query to it. We have used the “pg_cancel_backend” method first to cancel it then the “pg_terminate_backend()” function to terminate it along with the connection. Both queries return “t” as true for canceling and removing it.
After 1 “idle” state query is removed, let’s remove the query with process ID “12488” as well. The same commands have been used here separately on the terminal so far. Both return “true” Boolean value returns, implying that the specific query and connection are gone.
The same process has been used again for the query with process ID “11164” as shown.
After killing 3 ‘idle’ queries with their process IDs, let’s see if it has been successful or not. Use the same SELECT instruction using the “pg_stat_activity” utility to display the list of all queries/processes of the database system. The output shows that all the “idle” queries have been permanently removed and terminated so far.
Conclusion:
This tutorial is a simple guide to using the pg_cancel_backend() and pg_terminate_backend() function to kill the specific query and its connection. The main purpose of using these functions in the queries is to simply remove the unwanted queries or database sessions, i.e., idle. Thus, this article has well-explained the idea of cleaning your database system from unwanted and “idle” queries and connections within seconds.
