Skip to main content

PostgreSQL - Detect and terminate long-running queries

Stephanie R.
Updated

Learn how to detect and terminate specific sessions via the OVHcloud Control Panel and Command Line Interface (CLI).

Queries on a PostgreSQL instance can run indefinitely, and OVHcloud will not stop them automatically. Such queries can lock resources inappropriately and cause wrong behaviors in your system.

 

Requirements

 

Instructions

Terminate long-running queries with OVHcloud Control Panel

From your OVHcloud Control Panel, browse your Cloud Databases services, open the desired one, and click the Requests tab. This table regroups ongoing queries with some filtering options, such as showing idle or active connections.

manager_database_requestsTab.png

The query table shows the exact query, along with detailed information: PID (Process ID), Duration, Database, Client address, and Application name.

In the table list, find the session you want to terminate, then click the more options ... button at the end of the row for that entry. You will be able to terminate the session and related backend processes directly.

The query will disappear, but if your client application is coded to redo the query after a failure, another query and PID will reappear.

 

Terminate long-running queries with CLI (psql)

PostgreSQL CLI, called psql, provides native administration functions such as pg_terminate_backend().

For more information, please refer to their official documentation.

 

Connect to your database using psql

Please refer to this guide to find out how to connect to your database using psql.

 

Make sure to get enough access privileges first

You can terminate a session only when you are the owner of the database.

To verify your roles and grants, run the following command in psql:

\l

It will return the list of databases and access privileges. As an example:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 defaultdb | avnadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | avnadmin
           |          |          |             |             | bastien

If you don't see your user, you will have to grant them with required access privileges on the appropriate database. Otherwise, the instructions below will return an error.

 

Find long-running queries

Once you are connected to your database, type (for example):

SELECT * FROM pg_stat_activity WHERE state <> 'idle';

It will show you a list of queries and PIDs.

For more information about statistics, please follow the official documentation.

 

Terminate a specific query

Now that you have the PID, you can terminate a specific query with the following command:

SELECT pg_terminate_backend(pid);

Where pid is the unique identifier found during the previous step. This command does not return any result when successful.

As explained here, the query will disappear if you check again the statistics, but if your client application is coded to launch the query again after a failure, another query and PID will reappear.

 

Go further

For more information and tutorials, please see our other Managed Databases & Analytics or Platform as a Service guides. You can also explore the guides for other OVHcloud products and services.

Related articles