I have this issue with my zabbix 4. pgsql 11 and Rhel 7. Trappers are unable to gather values from local host. (DB and Server on same host installation) If I push values using sender they are getting updated but its not happening automatically. What could be wrong

Hi.

Is this correct in pgsql_tbl_funcs.sh:27 ?

... idx_blks_hit * 100 / (idx_blks_hit + heap_blks_read + 0.0001) ...

https://github.com/pg-monz/pg_monz/blob/master/pg_monz/usr-local-bin/pgsql_tbl_funcs.sh#L27

I think to use not "heap_blks_read" but "idx_blks_read" .

Dear all!
i use zabbix + pg_monz to monitor pg,everything is ok,but the pg_monz discovery's items slow one hour than the normal items,i can not understand why.
i change the template's Update interval (in sec), but it doesn't take effeck, i really confused.

Seems like this should be based on % of con/maxcon. I rewrote the expression for the trigger:

{PostgreSQL Check:psql.server_connections[{$PGDATABASE}].last(0)} / {PostgreSQL Check:psql.server_maxcon[{$PGDATABASE}].last(0)} * 100 >{$PGCONNECTIONS_THRESHOLD}

How can I set a password for the database user?

The configuration in the quick install file assumes that the postgresql user is configured with "trust" authentication in pg_hba.conf, but that's what most people would want.

Hi,

My company strategy is to disallow the SSL connection locally (127.0.0.1) from the pg_hba.conf.
But by default every psql cmd line query is first trying a SSL connection then fallback to no encryption. This lead into a log pollution on the postgres.log and useless double query.

There is a solution for it, it is to implement the PGSSLMODE, so I would like to be able to use this in my pgsql_funcs.conf.

PGSSLMODE="disable"

https://www.postgresql.org/docs/9.0/libpq-envars.html

But, of course, this implies to have this parameter added to all psql cmd call into pg_monz.

Do you think possible to implement it?

Or may be a more generic approach for people willing to pass custom parameters.
A kind of: PGMZOPTIONS="PGSSLMODE="disable""

Thanks

I think you should add GNU Basic Calculator ("bc") into System Requirements section.

The trigger name should have a host name "on {HOST.NAME}" .
Because it is often used in the notification subject.

Consider adding a listing to https://share.zabbix.com?

Hi

This trigger is sending mail when service is down but its unable to send OK alert when the service is up. is there something i'm missing?

Dear Folks

I have 2 Postgresql clusters on same server. both clusters have different data directories and are running on different ports.

As we can pass information about only 1 cluster in pgsql_funcs.conf, I wonder if there is any solution to monitor both cluster with pg_monz.

Many thanks in advance.

Regards
Neeraj

I packaged your agent configuration into an easily deployable Docker container:

https://github.com/croepha/docker-pg-monz

People might find it useful, so you might want to ad a link

Hello have you use "postgres" USER for monitoring?
What about to use read only access role to monitor? For example zabbix user.

Hi,

pg_monz is not working on my environment. I don't get any data and the PG RUNNING trigger shows me as Postgres is not running.

PostgreSQL: 10
Zabbix Server: 3.4

From my Zabbix Server:

./pgsql_simple.sh /etc/zabbix/postgres_monitor/
1

From my Monitored DB Server with Zabbix Agent and zabbix-sender installed:

./pgsql_simple.sh /usr/local/etc/
1

As you can see, calling the scripts manually works. However, via Zabbix is not working. Debugging the zabbix log file I found the following:

22313:20180612:172922.140 End of evaluate() error:'Cannot evaluate expression: "Cannot evaluate function "db1.example.com:psql.primary_server[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}].last(#1)": item is not supported.".'
22313:20180612:172922.140 evaluate_expressions():expression [(ZBX_UNKNOWN0)=0 and (ZBX_UNKNOWN1)=1] cannot be evaluated: Cannot evaluate expression: "Cannot evaluate function "db1.example.com:psql.primary_server[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}].last(#1)": item is not supported.".
22321:20180612:172922.149 In get_value_agent() host:'db1.example.com' addr:'192.168.99.16' key:'psql.running[/usr/local/bin,/usr/local/etc]' conn:'unencrypted'
22313:20180612:172922.150 In evaluate_function() function:'db1.example.com:system.swap.size[,pfree].last(0)'
22312:20180612:172922.326 In evaluate_function() function:'db1.example.com:psql.running[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}].last(0)'
22322:20180612:172926.164 In get_value_agent() host:'db1.example.com' addr:'192.168.99.16' key:'system.users.num' conn:'unencrypted'
22322:20180612:172928.310 In get_value_agent() host:'db1.example.com' addr:'192.168.99.16' key:'psql.standby_server[/usr/local/bin,/usr/local/etc]' conn:'unencrypted'

Do I need to do anything else? Am I missing something? Cheers!

We got a PG 10 and a zabbix 4.0.4. We recently discover that zabbix doen't get the data. We managed to make it work with zabbix 3.X and postgres 9.X. I recently update the script .sh in the repo to make sure we got the most recent version but nothing change.

Do pg_monz support pg10 and zabbix 4? If yes what can I do to resolve this problem?

Dear all
In zabbix_server, I try to run
zabbix_get -sIP -k db.list.discovery[/etc/zabbix/monitor_scripts]
but the server displays
psql: symbol lookup error: psql: undefined symbol: PQconnectdbParams

2614:20160321:161758.339 error reason for "zabbix-database2.team-it.weuk.net:pgsql.get.pg.stat_replication[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR},{HOST.HOST},{$ZABBIX_AGENTD_CONF},streaming]" changed: Received value [zabbix_sender [24885]: ERROR: [line 1] 'Hostname' requiredSending failed. Use option -vv for more detailed output] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]

I'm seeing this error on my Zabbix 3.0 server; what do I need to amend to fix this?

In the "pgsql_primary.sh" and "pgsql_standby.sh" files it has $DBNAME and it should probably be $PGDATABASE, which is what is found in the "/usr/local/etc/pgsql_funcs.conf" file. It erred for me until I changed it.

I can import the Template_App_PostgreSQL_SR.xml without any problems, but wenn I try to add it to a host I always get:
ERROR: Cannot update host
Template "Template OS Linux" with item key "psql.table_autovacuum_count[{#DBNAME},{#SCHEMANAME},{#TABLENAME}]" already linked to host.

The host has "Template App PostgreSQL" and "Template OS Linux" enabled.

Hi.

I use rhel6 PGDG postgres package.

rpm -ivh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-2.noarch.rpm

yum install postgresql94-server

PGDG init.d scripit use postmaster( deprecated.... ) when we start service.

service postgresql-9.4 start

ps -ef | grep postmaster

But pg_monz monitor only "postgres" process.

So, I can't monitor PostgreSQL process with pg_monz.

Hello.

Is this Library support PostgreSQL on Windows Server?

I looked files on repository and thought it for Linux or Unix.
I also googled a lot but I couldn't get any info about it.

Thanks

I Would like to share the template for 5.0 and above

After I followed all the instructions to install and configure pg_monz, I get this error when trying to restart the zabbix-agent.
zabbix_agentd[7805]: zabbix_agentd [7805]: ERROR: cannot add user parameter "db.list.discovery[*],"$1"/find_dbname.sh "$2"": key "db.list.discovery" already exists

I am using Zabbix 5.2 and PostgreSQL 13 on CentOS 8.

I am just trying to test out the postgresql plugin on the zabbix server before pushing it out to other hosts.

If I update /etc/zabbix/zabbix_agentd.conf and comment out this:
#Include=/etc/zabbix/zabbix_agentd.d/*.conf

and then uncomment this:
#works for pg_monz plugin
Include=/etc/zabbix/zabbix_agentd.d/userparameter_pgsql.conf

I have these files in /etc/zabbix/zabbix_agentd.d folder:
template_db_postgresql.conf
userparameter_pgsql.conf
zabbix-extensions.conf

Then zabbix agent restarts OK, but then I cannot access this stuff from webconsole dashboard.

Hi

I don't know if this is a known issue, but following error seems to stop sending the alerts regarding Postgres.

1. Cannot evaluate expression: "Cannot evaluate function "test-vm-monitoring:psql.running[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}].last(0)": item is not supported.".

2. Cannot evaluate expression: "Cannot evaluate function "test-vm-monitoring:psql.primary_server[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}].last(#1)": item is not supported.".

https://i.imgur.com/eJBH7W6.png

error reason for "Zabbix server:pgsql.get.pg.slow_query[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR},{HOST.HOST},{$ZABBIX_AGENTD_CONF},{$PGSLOWQUERY_TIME_THRESHOLD}]" changed: Received value [zabbix_sender [30852]: Warning: 'Server' parameter requiredSending failed. Use option -vv for more detailed output] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]
Please help me to solve this problem!!! I have this with any keys

HI all

zabbix agent don't have permission to access the pg log. because the $PGDATA need the 700 permission. anyone face the same issue?

My environment:

• pg_monz 2.0.1
• PostgreSQL 9.3.14
• zabbix_server (Zabbix) 3.2.4 Revision 65975 27 February 2017, compilation time: Mar 1 2017 04:37:49
• Zabbix agent v2.2.2 (revision 42525) (12 February 2014)
• Zabbix Sender v2.2.2 (revision 42525) (12 February 2014)

I have already checked whether there are any authorization problems for the PostgreSQL user collecting data on behalf of pg_monz by running the problematic item statements from pgsql_db_funcs.sh in the psql console, but these commands returned values.

I see no updates in over a year. Is this repo still active?

Good afternoon, I is presenting the following error, then place the .pgpass file and import it still gives me the following error
12207: 20150914: 182136,793 item "prueba_9.4:pgsql.get.pg.slow_query[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR},{HOST.HOST},{$ZABBIX_AGENTD_CONF},{$PGSLOWQUERY_TIME_THRESHOLD}]" Became not supported: Received value [password for user postgres: psql: fe_sendauth: no password supplied] is not suitable for value type
[Numeric (unsigned)] and data type [Decimal]
12205: 20150914: 182141,794 item "prueba_9.4:pgsql.get.pg.sr.status[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR},{HOST.HOST},{$ZABBIX_AGENTD_CONF}]" Became not supported: Received value [password for user postgres: psql: fe_sendauth: no password supplied] is not suitable for value type [Numeric (unsigned)] and data tt
ype [Decimal]
12205: 20150914: 182141,794 item "prueba_9.4:pgsql.get.pg.transactions[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR},{HOST.HOST},{$ZABBIX_AGENTD_CONF}]" Became not supported: Received value [password for user postgres: psql: fe_sendauth: no password supplied] is not suitable for value type [Numeric (unsigned)] and datt
to type [Decimal]
12206: 20150914: 182156,798 item "prueba_9.4: psql.primary_server [{$ PGSCRIPTDIR}, {$ PGSCRIPT_CONFDIR}]" Became not supported: Received value [Password for uu
ser postgres: psql: fe_sendauth: no password supplied] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]
12205: 20150914: 182206,799 item "prueba_9.4: psql.standby_server [{$ PGSCRIPTDIR}, {$ PGSCRIPT_CONFDIR}]" Became not supported: Received value [Password for uu
ser postgres: psql: fe_sendauth: no password supplied] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]

I'm using postgresql 9.4, they appreciate what I could to help. thank you

I've installed pg_monz 2.0 with zabbix 2.4.5, for monitoring postgresql 9.3, I have more than one instance of postgresql on my server, one with port = 5432, other with port = 5433, and so on. But I don't know if I can configure more than one instance in pgsql_funcs.conf.

Something like that...

pghost= localhost
pgport = 5432
...

pghost = localhost
pgport = 5433
...

Another doubt with the zabbix user, I don't want to give him access trust (pg_hba.conf) but when I put the line of the password on my pgpass for use md5, still saying that password don't supplied, and I have to set trust on the file. I don't know if exist another way to deal with the password of the zabbix user, or if I have to put trust.

I wish some help with that, thanks.

Hi,

The configuration example shows a network connection to localhost/127.0.01 and the psql connection string forces the use of the -h <host> parameter.

Would it be possible with an empty PGPOOLHOST to use a Local connection?

pool_connections=$(psql -A --field-separator=',' -p $PGPOOLPORT -U $PGPOOLROLE -d $PGPOOLDATABASE -t -X -c "${POOL}" 2>&1) should do the trick.

There should then no need to have a password set.

It would be nice, if this template would monitor PostgreSQL streaming replication. It should monitor following items:

• connected replication client on the master
• replication delay on slaves
• state of the replication and it it's running (possibly by creating dummy table and inserting some value)

I would gladly help with testing.

Hi, the userparameter_pgsql.conf currently has:

UserParameter=pgsql.get.pg.stat_database[*],"$1"/pgsql_db_funcs.sh pg.stat_database "$2" "$3" "$4" "$5"

which results in:

/usr/local/bin/pgsql_db_funcs.sh  pg.stat_database /usr/local/etc $(hostname) zabbix-server 10051

I think the correct call should be:

/usr/local/bin/pgsql_db_funcs.sh  pg.stat_database /usr/local/etc $(hostname) /etc/zabbix/zabbix_agentd.conf $databasetocheck

could you take a look please?

Hello,

There seems to be some issues using pg_monz with PostgreSQL 9.6 databases. Some calls (such as for pg_stat_activity have changed enough that they no longer work properly with pg_monz. I found and fixed that issue locally (by modifying line 34 in pgsql_server_funcs.sh), but there seem to be other functions that do not work (such as cache hit ratio, deadlocks, committed/rolledback transaction, etc).

I've started troubleshooting them and will contribute back any fixes I find, but I am also not sure how the project would like to handle the changes (few people are using pg96 since it is fairly new, but what would be the preferred method to handle different versions of the same functions depending on the pg version)?

Hi, I wonder why pg_monz does not support password as part of the configuration? It's useful when your database is not running under a trusted environment.

I got the error below after I copied userparameter_pgsql.conf in pg_monz ver 2.0.1 to ../zabbix_agentd.d and restart the zabbix agent. I have to remark all the userparameter or move the userparameter_pgsql.conf file to other location only I am able to start my zabbix agent. Anyone has idea what is going on? Thanks alot.

Starting Zabbix agent: zabbix_agentd [77914]: ERROR: cannot add user parameter "db.list.discovery[*],"$1"/find_dbname.sh "$2"": key "db.list.discovery" already exists

I noticed that pg_monz required "bc" command in pgsql_server_funcs.sh.
Would it be possible for you to add "bc" command as minimum requirement?

I have an issue with the grpsum function for cluster monitoring.

The grpsum is based on this item :
psql.primary_server[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}]

But in the grpsum item, zabbix change the macros with the macros values (so zabbix is looking for the item psql.primary_server["/usr/local/bin","/usr/local/etc"], but the "real item" in the item list of zabbix is show as psql.primary_server[{$PGSCRIPTDIR},{$PGSCRIPT_CONFDIR}].

Zabbix is sending us the error : No items for key psql.primary_server["/usr/local/bin","/usr/local/etc"] in group(s) MYGROUP"

Just to notify you that I've created and published puppet module for pg_monz. You can find it at puppet forge

Keep with the great work guyz!

It seems in Zabbix 5.x the response from zabbix_sender application changed and the "info" string is no longer there it now looks like this "sent: 2; skipped: 0; total: 2"
so the info needs to replace with sent or a full string will be returned which is an invalid value for the field type numeric

from

response=$(echo "$result" | awk -F ';' '$1 ~ /^info/ && match($1,/[0-9].*$/) {sum+=substr($1,RSTART,RLENGTH)} END {print sum}')

to

response=$(echo "$result" | awk -F ';' '$1 ~ /^(info|sent)/ && match($1,/[0-9].*$/) {sum+=substr($1,RSTART,RLENGTH)} END {print sum}')

If table created like:
CREATE TABLE "Table" ( ... );
pgsql_tbl_funcs.sh return error like:
relation public.table not found
because public.table != public.Table
I fixed this issue by addin double quotes around ${TABLENAME} in select pg_total_relation_size:
select '\"$HOST_NAME\"', 'psql.table_total_size[$DBNAME,$SCHEMANAME,$TABLENAME]', $TIMESTAMP_QUERY, (select pg_total_relation_size('${SCHEMANAME}.\"${TABLENAME}\"'))" 2>&1

please review and fix if necessary

hello,

I found that these three application could not be enabled,This is the macros I set.I hope you can give me some advice.

Hi
I see that there is a fix for the pgsql_simple.sh script for the incorrect $DBNAME variable. Can you please release a new version of pg_monz that incorporates this fix?

Thanks
Shaun

I was setting the pg_monz and after creating database in PostgreSQL, Zabbix monitoring item was auto registered.
After I dropped database, it has been outputting below error message in PostgreSQL log.

2014-05-16 13:24:57.303 JST >ERROR:  database "hoge" does not exist
2014-05-16 13:24:57.303 JST >STATEMENT:  select pg_database_size('hoge')
2014-05-16 13:34:57.681 JST >ERROR:  database "hoge" does not exist
2014-05-16 13:34:57.681 JST >STATEMENT:  select pg_database_size('hoge')
...

I think pg_monz need to fix in Zabbix userparameter file.