multi target DATA_SOURCE_NAME has issues on a port != 5432

[sandroden]

I consistently have problems using DATA_SOURCE_NAME with more than one URI if the ports are not standard.

I understand from this sentences:

Also, you can set a list of sources to scrape different instances from the one exporter setup. Just define a comma separated string.

sudo -u postgres DATA_SOURCE_NAME="port=5432,port=6432" postgres_exporter

What I did is to set this variable:

DATA_SOURCE_NAME="postgresql://postgres:xxx@10.1.201.11:5434/postgres?sslmode=disable,postgresql://postgres:xxx@10.1.201.11:5432/postgres?sslmode=disable"

just for readability I'll write it this way:

DB1=10.1.201.11:5434
DB2=10.1.201.11:5432
OPTS=postgres?sslmode=disable
DATA_SOURCE_NAME="postgresql://postgres:xxx@$DB1$OPTS,postgresql://postgres:xxx@$DB1$OPTS"

What did you do?

I tested each database alone, and it worked.

As long as DB1 and DB2 use port 5432 the exporter works and I get rows for each target (clearly on different servers). If I use other ports (5434, 5435...) I get an error:

# wget -O - localhost:9187/metrics
--2022-01-26 23:47:17--  http://localhost:9187/metrics
Resolving localhost (localhost)... 127.0.0.1
Connecting to localhost (localhost)|127.0.0.1|:9187... connected.
HTTP request sent, awaiting response... 500 Internal Server Error
2022-01-26 23:47:17 ERROR 500: Internal Server Error.

What did you expect to see?
I expect it to work correctly on any port...

Environment

I did the tests with PostgreSQL 14 on Ubuntu 20.04 using binary 0.10.0

# uname -srm
Linux 5.4.0-80-generic x86_64

# postgres_exporter --version
postgres_exporter, version 0.10.0 (branch: HEAD, revision: 57719ba53cac428769aaf3c4c0bb742df3cfca98)
  build user:       root@4dcb2c7f1315
  build date:       20210709-11:49:20
  go version:       go1.16.5
  platform:         linux/amd64

• postgres_exporter flags:

not using any flags

I initially verified the same behaviour working in kubernetes.

[sandroden]

Can someone confirm to me that I'm not misreading the docs and that multiple targets are allowed to be declared in the same DATA_SOURCE_NAME?

[sysadmind]

I tried to reproduce this but was unsuccessful. Here's the steps I used in case I am misunderstanding the issue that you are seeing.

1. Run a postgres instance with the default 5432 port

docker run --rm -it\
-p 5432:5432 \
-e POSTGRES_DB=circle_test \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=test \
postgres

2. Run another instance using a different port (I'm using docker to translate the ports

docker run --rm -it\
  -p 5434:5432 \
  -e POSTGRES_DB=circle_test \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=test \
  postgres

3. Run the exporter, scraping both servers. This is using the version 0.10.0, but I also tried running from master.

docker run \
  --net=host --rm -it \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME='postgresql://postgres:test@localhost:5432/circle_test?sslmode=disable,postgresql://postgres:test@localhost:5434/circle_test?sslmode=disable' \
  quay.io/prometheuscommunity/postgres-exporter:v0.10.0

4. Query the metrics

curl 127.0.0.1:9187/metrics
....
pg_locks_count{datname="circle_test",mode="accessexclusivelock",server="localhost:5432"} 0
pg_locks_count{datname="circle_test",mode="accessexclusivelock",server="localhost:5434"} 0
pg_locks_count{datname="circle_test",mode="accesssharelock",server="localhost:5432"} 1
pg_locks_count{datname="circle_test",mode="accesssharelock",server="localhost:5434"} 1
pg_locks_count{datname="circle_test",mode="exclusivelock",server="localhost:5432"} 0
pg_locks_count{datname="circle_test",mode="exclusivelock",server="localhost:5434"} 0
pg_locks_count{datname="circle_test",mode="rowexclusivelock",server="localhost:5432"} 0
pg_locks_count{datname="circle_test",mode="rowexclusivelock",server="localhost:5434"} 0
pg_locks_count{datname="circle_test",mode="rowsharelock",server="localhost:5432"} 0
pg_locks_count{datname="circle_test",mode="rowsharelock",server="localhost:5434"} 0
pg_locks_count{datname="circle_test",mode="sharelock",server="localhost:5432"} 0
....

From the output, I can see both servers showing up, so everything appears to be working to me. If you still have no luck getting this working, can you provide a small, reproducible example with docker that can be run locally?

[sandroden]

Thanks @sysadmind, I confirm that this example works perfectly.

I further tested postgresql_exporter in docker with PostgreSQL on different servers and it still works.

Nevertheless in my staging environment it continues to fail. In this case I see the error and it complains of help mismatch...

An error has occurred while serving metrics:

194 error(s) occurred:
* collected metric pg_settings_allow_system_table_mods label:<name:"server" value:"10.2.201.11:5435" > gauge:<value:0 >  has help "Allows modifications of the structure of system tables." but should have "Consente le modifiche alla struttura delle tabelle di sistema."
* collected metric pg_settings_archive_timeout_seconds label:<name:"server" value:"10.2.201.11:5435" > gauge:<value:0 >  has help "Forces a switch to the next WAL file if a new file has not been started within N seconds. [Units converted to seconds.]" but should have "Forza il passaggio al prossimo file WAL se un nuovo file non è stato iniziato entro N secondi. [Units converted to seconds.]"                                                                                                                   
* collected metric pg_settings_array_nulls label:<name:"server" value:"10.2.201.11:5435" > gauge:<value:1 >  has help "Enable input of NULL elements in arrays." but should have "Abilita l'input di elementi NULL negli array."                              
* collected metric pg_settings_authentication_timeout_seconds label:<name:"server" value:"10.2.201.11:5435" > gauge:<value:60 >  has help "Sets the maximum allowed time to complete client authentication. [Units converted to seconds.]" but should have "Imposta il tempo massimo consentito per completare l'autenticazione del client. [Units converted to seconds.]"

It complains the language is different. I guess the help is written by the exporter, that puzzles me.

I'm sure that -as reported in my first post- the error when running in the cluster is not this error... Let me know if I can do some other test.

[heidricha]

I experience the same with version 0.11.
The same config worked well with 0.10, but now...

[gitlab-runner@runner8 docker]$ curl --noproxy "*" http://192.168.48.3:9187/metrics
An error has occurred while serving metrics:

4 error(s) occurred:
* collected metric "pg_scrape_collector_duration_seconds" { label:<name:"collector" value:"bgwriter" > gauge:<value:0.01298328 > } was collected before with the same name and label values
* collected metric "pg_scrape_collector_success" { label:<name:"collector" value:"bgwriter" > gauge:<value:1 > } was collected before with the same name and label values
* collected metric "pg_scrape_collector_duration_seconds" { label:<name:"collector" value:"database" > gauge:<value:0.02308314 > } was collected before with the same name and label values
* collected metric "pg_scrape_collector_success" { label:<name:"collector" value:"database" > gauge:<value:1 > } was collected before with the same name and label values

pg-ex runs in container, it is started like:

[gitlab-runner@runner8 docker]$ cat smv-psql-exporter/start.sh
#!/bin/sh

export DATA_SOURCE_NAME="postgresql://postgresadmin:$(cat /run/secrets/postgresadmin)@smv-timescale:9050/smartvision?sslmode=disable,postgresql://postgresadmin:$(cat /run/secrets/postgresadmin)@smv-timescale-op:9055/smartvision-op?sslmode=disable" \

/bin/postgres_exporter --log.level=debug "$@"

Nothing relevant in the logs, even with debug level. looks like the database connections and the queries are ok, there should be something with the report generation

[sysadmind]

@sandroden This new error is related to #682. The problem is that the help text is dynamically set from the database

postgres_exporter/cmd/postgres_exporter/pg_setting.go

Line 73 in ac9fa13

shortDesc = s.shortDesc

Because the databases have different descriptions (it looks like this is due to language) the prometheus registry is complaining. I will work on a fix for that issue.

[malcolm061990]

@sysadmind
Hi :)
Do you have any progress?
I have the same issue as #609 (comment) with multiple datasources using 4.2.1 helm chart.
Setting disableCollectorBgwriter: true and disableCollectorDatabase: true fixed the issue but its not fix, its workaround.