Question 238974 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Postgresql – Idle query keep open connections to all databases

I have a strange situation in my PostgreSQL server. I have the below idle query that keeps running in all databases in the server.

datid            | 1029662
datname          | ganar
pid              | 19022
usesysid         | 10
usename          | postgres
application_name | 
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2023-08-27 10:10:23.265554+00
xact_start       | 
query_start      | 2023-08-27 10:18:07.886526+00
state_change     | 2023-08-27 10:18:07.886626+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      | 
backend_xmin     | 
query            | SELECT version();
backend_type     | client backend

If I tried to create a database named "test", it will have the same query running even without connecting to it via the "postgres" user.

datid            | 1887973
datname          | test
pid              | 19021
usesysid         | 10
usename          | postgres
application_name | 
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2023-08-27 10:10:23.210437+00
xact_start       | 
query_start      | 2023-08-27 10:18:07.885979+00
state_change     | 2023-08-27 10:18:07.886143+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      | 
backend_xmin     | 
query            | SELECT version();
backend_type     | client backend

I’ve tried to kill this process but after 5 sec, it’s getting created again.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue was from the monitoring system, I've excluded the database from the monitoring system and worked fine. Thank you all.


  2. 0

    Your PostgreSQL server’s ongoing idle query appears to be caused by a connection pooling system or an external application that is constantly connecting to your database. In order to find the PostgreSQL version, the query SELECT version(); is frequently used. Start by looking at your application’s source code, configuration settings, and any external scripts that might be opening connections as a first step in fixing issue. Verify that no unauthorised or redundant connections are being established. Check the setup of your connection pooling software if you use it to avoid having idle connections linger. To learn more about the source of these queries, you should also look through your PostgreSQL logs. The client application and host connected to these idle queries can be found using the pg_stat_activity view. To successfully resolve this, it is crucial to identify the source and change the related application or settings to stop irrational connections and queries from being made.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search