Low Level Discovery (LLD) for PostgreSQL on ZABBIX using ODBC.

How about ZABBIX PostgreSQL monitoring with LLD without custom addons installed on the server or milions of very heavy userparameter? 🙂

I’ve been searching for some way to monitor PostgreSQL database, all the solution I’ve found were based either on some custom addon:

https://share.zabbix.com/databases/db_postgresql/postgresql-monitoring-for-zabbix

http://cavaliercoder.com/libzbxpgsql/

Or had tooooonnns of userparameters that were just killing the server:

https://share.zabbix.com/databases/db_postgresql/template-postgresql

Zabbix_postgresql1

Well I though there is a better way, and you know what? I think there is.

I just couldn’t understand why when MS SQL can so easily discover instances and databases – PostgreSQL cannot. I always prefer to use default, widely available solutions, this is why when native way to monitor MS SQL with ODBC came out I was so happy.

Done a bit searching, and… see here:

https://www.zabbix.com/documentation/3.0/manual/discovery/low_level_discovery

look at this paragraph:

3.5 Discovery using ODBC SQL queries

And there I saw:

Zabbix_postgresql2

Fuck yeah! Let’s do it! Here is step by step what you need to do.

1. Get the odbc drivers, you can install it on the box with:

sudo apt install odbc-postgresql

On my ubuntu box I noticed that relevant entries appearedin the /etc/odbcinst.ini by itself:

$ cat odbcinst.ini |grep  ^[^#]

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

And… this is fucking it!

2. Now you need to just add your server to /etc/odbc.ini (I took that from zabbix website):

[JIRADBT]

Description = PostgreSQL database 1
Driver  = PostgreSQL Unicode
Username = postgres
Password =
Servername = jiraserver.domain.com
Database = jiradb
Port = 5432
ReadOnly = No
Protocol = 7.4+
ShowOidColumn = No
FakeOidIndex  = No
RowVersioning = No
ShowSystemTables = No
Fetch = Yes
BoolsAsChar = Yes
ConnSettings =

3. And now, the ZABBIX part.

I’ve created template with dicovery rule:

Zabbix_postgresql3

Discovery rule:
Name: PostgreSQL Databases for discovery
Type: Database monitor
Key: db.odbc.discovery[PostgreSQLDBs,{$PSQLDBNAME}]
SQL query: SELECT datname FROM pg_database WHERE datistemplate = false
{$PSQLDBNAME} is defined as JIRADBT in macro on the jiraserver host

And later created an item prototype:

Zabbix_postgresql4

Item prototype:

Database size for {#DATNAME}
Type: Database monitor
Key: db.odbc.select[db.size{#DATNAME},{$PSQLDBNAME}]
SQL Query: select pg_database_size(‘{#DATNAME}’)

Why {$DATNAME}???

Because of this:

$ export PGPASSWORD=”; psql -h -U postgres -c “SELECT datname FROM pg_database WHERE datistemplate = false”
datname
————-
postgres
jiradb
eazybi_jira
(3 rows)

So {$DATNAME} here is our database name.

Waited few minutes, and e voila!:

Zabbix_postgresql5

Zabbix_postgresql6

Now you can do all the other selects using your discovered database names.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s