PostgresSQL Monitoring with Agent 2

[kcteh]

Hey guys,

Currently I am trying to set up postgres monitoring with the latest new agent2.

I have manage to get most of the data but except for pgsql.db.bloating_tables, pgsql.db.size & pgsql.db.age.

I am getting this error code "Cannoe Etabilish Connection To Postgres Server: Failed To Connect To `Host=Localhost User=Postgres Database=Postgres`: Server Error (FATAL: Password Authentication Failed For User "Postgres" (SQLSTATE 28P01))."

I have configured the postgres username and password in agent 2 configuration.






Please Help.

Appreciate and thanks in advance.

[reedacus25]

After setting the user/pass/host/port/db vars in the conf file, I was able to get everything except for the key which reports "Cannot fetch data."

pgsql.archive

But what really threw me off was the fact that they've gone in two completely different directions for their MySQL agent2 template compared to the PostgreSQL agent template.

Before the mysql and postgres templates both used .my.cnf and .pgpass in the zabbix home directory.

Now the MySQL credentials have been moved into host level variables in the front end, which makes sense because they're trying to secure things by allowing secret text passwords in the front end.
However with Postgres, now we've buried the credentials into an agent2 conf key, which is very not super obvious, especially if you are upgrading an existing agent2 instance that doesn't have the key in the conf to begin with.

[reedacus25]

I also realized that while the Postgres instance stats are connected via the credentials in the agent2.conf, the stats that collect the discovered databases actually use the host level macros, similar to the mysql agent2 template.

While I am all for this for the sake of keeping things consistent, why the duplicity of needing to specify it in two places in the first place?
Hopefully thats helpful for you kcteh as the screenshot looks like it may reflect this, where its pulling from the host (inherited) macros for pg.user and pg.pass, rather than the config file.

[kcteh]

Hi reedacus25 ,

First of all thanks for answering my question.

I think you are right and if that's is the case I am totally agree with you on why it's duplicating the credentials to store in two places rather than one.

However I have tried to set up the .pgpass and pg_hba.conf as well, it seem like I am still getting the same error code and not getting zabbix to read the macro.

.pgpass:

*:5432:*:zbx_monitor:zabbix

.pghba:

host    all             zbx_monitor     127.0.0.1/32            trust

On the post above you mention about pg.user. Can you please guide me on it?

Appreciate your responses and help. Cheers

[reedacus25]

Yes.

In the Zabbix front end, head to configuration -> host.

From there, select your host with the postgres agent2 template applied.

Then go to the Macros sub-menu.

Flip the switch to Inherited and Host Macros

Find the macros for {$PG.USER} and {$PG.PASSWORD}, and click change to the right of them, and set your value in there.
You can set the value as text (readable in the frontend) or secret text (obfuscated in the front end).

FWIW, {$PG.URI} also looks to be used, however if you are only talking to an instance on the host that the agent is running on, there's no need to set this.

All of these variables are how the new MySQL agent2 template is configured, in case you run into that.

Hope this helps kcteh

[kcteh]

You are awesome reedacus25 ,

I have manage to get the data once I have set up the macros in the front end.

You are amazing and thanks for your quick responses and helped. Appreciate mate.


Note to others,

Just to make some correction in my previous post #4, actually you don't need to set up .pgpass and pg_hba.conf.

Just have to follow reedacus25 in post #5 you will be able to do it.

[jchavezb]

did you compare pgadmin metrics with zabbix metrics?. Tuples for example are very differents between both platform.