Postgresql - Idle query keep open connections to all databases

Question

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.

The fact is, a client is connecting as the postgres user and doing SELECT version(). Now you need to track down what that client is. Best guess some sort of monitoring program. I would start by looking at the Postgres log. — Adrian Klaver
– Adrian Klaver, Commented Aug 27, 2023 at 15:22
backend_type | client backend, this shows you that some client is doing this. — Frank Heikens
– Frank Heikens, Commented Aug 27, 2023 at 16:03
Note that it is not the "idle query" that keeps the connection open. It is the client that forgets to close the connection. — Laurenz Albe
– Laurenz Albe, Commented Aug 28, 2023 at 6:19

Taha Adel · Accepted Answer · 2023-08-27 16:46:39Z

0

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

answered Aug 27, 2023 at 16:46

Taha Adel

114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

Rather than not monitoring the database, fix the monitoring system so that it does not leak connections.

Taha Adel Over a year ago

I don't need to monitor it as it's just a test database. So, I've excluded it from the monitoring system and kept the rest of the databases

Collectives™ on Stack Overflow

Postgresql - Idle query keep open connections to all databases

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related