PostgreSQL module cannot connect

[thealy]

We are running metricbeat-7.6.0 on a RedHat 7 system. The basic Metricbeat app is running and connecting with ES. However, the postgresql module is not connecting to the local database. The password, shown here as "redacted" works otherwise. It appears to be using the username as the database name when trying to connect:

Feb 25 13:46:30 bnlbox04 metricbeat: 2020-02-25T13:46:30.884-0500#011INFO#011module/wrapper.go:252#011Error fetching data for metricset postgresql.bgwriter: error in QueryStats: failed to obtain a connection with the database: pq: database "redacted" does not exist

Here are the postgresql.yml contents:

- module: postgresql
  metricsets:
    - database
    - bgwriter
    - activity
  period: 10s
  hosts: ["postgres://localhost:5432?sslmode=disable"]
  username: redacted
  #password:

Any suggestions what the problem connecting is?
Thanks,
Terry

[Mario_Castro]

Hi @thealy What's your postgres version? It might be some version mismatch. I'm not sure if I understood correctly but did you use username AND password to connect? (even if it's an empty password).

I agree that it seems like it's using the username as the database to connect and that's totally unexpected

[thealy]

Hi Mario.
The version is psql (PostgreSQL) 11.7
The password on this test system is blank.
-Terry

[Mario_Castro]

@thealy

• module: postgresql metricsets: - database - bgwriter - activity period: 10s hosts: ["postgres://localhost:5432?sslmode=disable"] username: redacted #password:

My question in different words: have you checked if it fails also with this?

- module: postgresql
  metricsets:
    - database
    - bgwriter
    - activity
  period: 10s
  hosts: ["postgres://localhost:5432?sslmode=disable"]
  username: redacted
  password: ""

[thealy]

Hi Mario-
OK, I just tried that variation and the result is the same:
Error fetching data for metricset postgresql.activity: error in QueryStats: failed to obtain a connection with the database: pq: database "bnlboxread" does not exist
-Terry

[Mario_Castro]

Greats! Thank you for trying. After your test I have done a quick test using Docker https://hub.docker.com/_/postgres with version 11.7

I didn't manage to reproduce the problem so it might be something on your environment. Things I tried:

• Set POSTGRES_PASSWORD to empty when initializing: This simply kills the container because empty passwords are not supported by the init script.
• Use POSTGRES_HOST_AUTH_METHOD=trust and comment password entry in Metricbeat. This worked and data was retrieved as expected.

Can you provide some way to reproduce your issue?

Thanks!

[thealy]

Thanks Mario.
Can you tell me where I set the POSTGRES_HOST_AUTH_METHOD=trust statement?
Thanks

[Mario_Castro]

It comes in the Docker container of Postgres but if you have an ad-hoc deployment I'm not sure if that env variable exists.

It's all here: https://www.postgresql.org/docs/current/auth-methods.html (trust is the one that gets activated by POSTGRES_HOST_AUTH_METHOD AFAICS

[thealy]

As a work around, I have asked the DB admin to add a password - which should have been there anyhow. Hopefully then it will connect.