postgres_exporter left more and more conntions in database and not released or reuse them

[sheenzxx]

when use postgres_exporter to monitor pg database,

postgres_exporter left more and more connections in database and not released or reuse them

then on other windows to request metrics

curl "http://localhost:9187/probe?auth_module=foo1&target=192.168.74.94:5433/pcauto"

query
select datid,pid,usename,query_start from pg_stat_activity where usename='exporter_monitor' order by query_start

can see that when request 1 time .1 more connection add and not released or reuse

[sheenzxx]

I reslove this problem wiht add db.SetConnMaxLifetime(60 * time.Second)

[sheenzxx]

if there any Perfect solution？

[hajdukda]

ok this one is super important to fix.

[kjbass]

Yeah, another +1 for this being an important one to fix. Running the exporter caused number of connections to go up endlessly. @sheenzxx's solution seems like a valid one, but another option would be actually closing the connection after a probe finishes. Currently, cannot really use this in prod.

[kjbass]

Tried my hand at writing a fix here: #734

[kazauwa]

This bug can easily bring the production database down. This is what we saw yesterday after enabling postgres_exporter. We definitely need https://github.com/prometheus-community/postgres_exporter/pull/734 merged.

[MichaelDBA]

Doesn't the error message indicate its a configuration problem? --> error = empty dsn

So perhaps the connection is made nonetheless but unawares to the postgres_exporter so that's the reason its never closed or assumed to be active in the first place.

[kjbass]

Doesn't the error message indicate its a configuration problem? --> error = empty dsn

So perhaps the connection is made nonetheless but unawares to the postgres_exporter so that's the reason its never closed or assumed to be active in the first place.

I'm pretty sure that's a separate issue. The error = empty dsn shows up regardless when you're using the multi-target exporter, but it's a bit of noise that does not affect things. When using the multi target exporter you do not set a DSN, since you are targeting multiple instances with the probes.

I think in general the multi target exporting could use some more love, but triaging the runaway connections is a good first step.