Connections to the database remains open for each database when you use PG_EXPORTER_AUTO_DISCOVER_DATABASES

[kadaffy]

When you configure postgres-exporter using the option PG_EXPORTER_AUTO_DISCOVER_DATABASES=true make the service open and keep open a connection per each database,

postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:39Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Established new database connection to \"postgres:5432\"." source="postgres_exporter.go:878"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Semantic Version Changed on \"postgres:5432\": 0.0.0 -> 12.4.0" source="postgres_exporter.go:1405"
postgres-exporter    | time="2020-09-21T10:09:40Z" level=info msg="Starting Server: :9187" source="postgres_exporter.go:1672"

$ psql -h localhost -p 5432 -U postgres -c 'select state, usename, datname, count(*) from pg_stat_activity group by state, usename, datname order by usename;'
 state  |      usename      | datname  | count 
--------+-------------------+----------+-------
 active | postgres          | postgres |     1
        | postgres          |          |     1
 idle   | postgres_exporter | postgres |     1
 idle   | postgres_exporter | test1    |     1
 idle   | postgres_exporter | test2    |     1
 idle   | postgres_exporter | test3    |     1
 idle   | postgres_exporter | test4    |     1
 idle   | postgres_exporter | test5    |     1
 idle   | postgres_exporter | test6    |     1
 idle   | postgres_exporter | test7    |     1
 idle   | postgres_exporter | test8    |     1
 idle   | postgres_exporter | test9    |     1
        |                   |          |     4
(13 rows)

This is a problem because in an environment with many databases you are wasting connections just for the postgres-exporter service instead of open and close this connection every time it collect the metrics which is more efficient to keep the connection open.

This could also apply to the master database the one your using in the DATA_SOURCE_NAME.

postgres-exporter version: v0.8.0

[bmeriaux]

We have the same problem, on our development instance, we create a DB per feature branch, per application, it can go up to ~150 DB, we have noticed postgres_exporter is keeping 150 connections Idle. I tink it would be better if a connection pool could be implemented, or maybe simply use only one connection for all queries ?

[edubacco]

same problem here

[KES777]

If metrics are collected every 5 seconds, why resources should be wasting for reconnection? Why not reuse already opened connection??

On the other side, if one server has many databases, why for same server separate connection should be issued??

To my mind, there should be no more connections, than configured at DATA_SOURCE_NAME environment variable.

[SuperQ]

The postgres exporter is meant for measuring the health of the database server. Having new connections for each scrape is to make sure we excersie connection handling. For example, what if authentication on the server has been broken. We want to have that test as part of the server validation.

[KES777]

@SuperQ: probably the idea to make new connection is good, but postgres exporter is meant to export metrics and do not make testing itself. Probably postgres exporter could export stat about volume of new authentications/new connections. (Anyway correct authentication for postgres exporter does not mean that auth for other database/users are not broken)

[KES777]

Also do not forget to issue connection to database was closed log message, when connection was closed.

ts=2022-10-28T14:03:16.522Z caller=main.go:149 level=info msg="Listening on address" address=:9187

ts=2022-10-28T14:03:16.522Z caller=tls_config.go:195 level=info msg="TLS is disabled." http2=false

ts=2022-10-28T14:03:19.973Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=db:5432

ts=2022-10-28T14:03:19.998Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=tsdb:5432

ts=2022-10-28T14:03:20.020Z caller=postgres_exporter.go:645 level=info msg="Semantic version changed" server=db:5432 from=0.0.0 to=15.0.0

ts=2022-10-28T14:03:20.040Z caller=server.go:74 level=info msg="Established new database connection" fingerprint=db:5432

ts=2022-10-28T14:03:20.060Z caller=postgres_exporter.go:645 level=info msg="Semantic version changed" server=db:5432 from=0.0.0 to=15.0.0

ts=2022-10-28T14:03:20.060Z caller=postgres_exporter.go:645 level=info msg="Semantic version changed" server=tsdb:5432 from=0.0.0 to=14.5.0

expected to see:

ts=2022-10-28T14:03:20.040Z caller=server.go:74 level=info msg="Closed database connection" fingerprint=db:5432

[HugoPuntos]

I faced the same issue while testing the multi-target support feature and I coped with it by using the idle_session_timeout parameter (cf the PostgreSQL documentation for more information on how it works) at the client level. You can specify it in the options section in the auth_modules. On the PostgreSQL server side, you should see some messages like "terminating connection due to idle-session timeout". Caveat: the following solution works for PostgreSQL 14 and later.

[dennisverspuij]

Hello, I also experience this while using both multi-target and auto db discovery features, each scrape new connections are opened and never closed. I am using Postgres 13 so the idle_session_timeout option is unavailable for me. Would love to see this issue fixed, too bad for now I have to revert to single db per exporter setup.