Documentation relative to Release: 1.1.0 Authors: Andrea Dalle Vacche Referring Site: http://www.smartmarmot.com
Postbix is a plugin designed to work with Zabbix Enterprise Monitor to provide multi-tiered monitoring, performance and availability reporting and measurement for Postgres Databases, along with server performance metrics.
It provides an effective mechanism to acquire data from numerous Postgres instances, and in turn provides this information for monitoring and performance metrics to your Zabbix server. You can then utilise the reporting capabilities of Zabbix for all data collected, and provide analysis such as graphs and service level agreement metrics for stakeholders.
The current distribution contains a set of pre-defined templates which incorporate alerting and graphing capabilities from initial deployment. However these can be fine tuned to suit your needs and data/monitoring requirements.
Below are some examples of what Postbix is capable of monitoring:
Andrea Dalle Vacche: http://www.smartmarmot.com
You can read about the history and evolution of the Postbix plug-in in these threads below Original here: http://www.zabbix.com/forum/showthread.php?t=13666 Latest here: http://www.zabbix.com/forum/showthread.php?t=16391
You can find the latest versions of Postbix at the locations listed below:
To use Postbix, you will require the following on your Zabbix Server:
Current known successfully tested Postgres host platforms:
The installation instructions have been created based on instructions for most *nix deployments (i.e. RHEL/CentOS), and is assumed that any alterations required for your specific environment are to be taken into account accordingly.
This guide is based upon there being two (2) Hosts, 1x Zabbix Server and 1x Postgres Server. If you are planning on monitoring an Postgres instance that is running on your Zabbix Server, the steps are the same, with minor adjustments required for your connection information.
The steps also assume you are configuring Postbix to monitor a new installation or setup of Postgres. The installation steps will have you GRANT access for the Zabbix user to all tables, and this will include any USER tables present at the time of execution. If you do not want Zabbix to have access to specific tables or resources within your database, you will need to set a DENY to the Zabbix users access as required. Consult your DBA for details, as this is beyond the scope of these instructions.
Download Postbix to your Zabbix Server On your Zabbix server, unzip Postbix to: /opt/postbix Copy file /opt/postbix/init.d/postbix to /etc/init.d/postbix Grant execute permissions to the following files: /etc/init.d/postbix /opt/postbix/run.sh Create a User (ZABBIX) for Postbix to access your Postgres Database. You can use the following script:
CREATE USER zabbix WITH PASSWORD 'passw0rd';
GRANT SELECT ON pg_stat_activity to zabbix;
GRANT SELECT ON pg_stat_activity to zabbix;
GRANT SELECT ON pg_database to zabbix;
GRANT SELECT ON pg_authid to zabbix;
GRANT SELECT ON pg_stat_bgwriter to zabbix;
GRANT SELECT ON pg_locks to zabbix;
GRANT SELECT ON pg_stat_database to zabbix;
For this example on RedHat, run:
chkconfig -add postbix
Create a Host entry in Zabbix for the DB Instance/s you are planning to monitor, and import the templates found at:
Please note, instructions for step (8) can be found in the Zabbix Manual at http://www.zabbix.com/documentation.php With this step (8), ensure the name of your host in Zabbix is the same name of the SID or Postgres instance name.
Now we need to configure your Postbix setup. The tags below are listed as they will appear within the respective configuration files. The first you will need to modify is your config.props file to define your connection properties for Zabbix and Postgres.
The config.props file can be found at /opt/postbix/conf/config.props
comma separed list of Zabbix servers The settings under this tag allow you to configure your Zabbix Server information. Postbix can also be configured to send your Postgres data to multiple Zabbix servers.
Replace with your Zabbix Server info where appropriate:
Entries under this tag allow you to set your Postbix Daemon parameters.
frequency of item's refresh Set how long you want the Daemon to sleep between iterations (loop from one to the next). Default is 120
MaxThreadNumber should be >= than the number of your databases Set the number of threads the Postbix Daemon should have inside his internal pool of DB Jobs. This number should be at least equal to (or more than) then number of databases monitored by Postbix.
e.g. For 50 Databases, we are using 100 threads
pidFile Next you can set the location of the Daemons PID file. Default location is: /opt/postbix/logs
put here your databases in a comma separated list
This is where you define your database instances. You can specify more than one instance here, separated using a comma.
Configuration of Connection pool From here, you will configure settings that are specific to the connection pool. As the comments in config.props suggest, if you do not specify these values, Postbix will use default values hich have been hard-coded.
Maximum number of active connection inside pool Set the maximum number of connections that can be allocated to this pool at any time, or alternatively set a negative value for no limit.
The maximum number of milliseconds Here you define how long that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or set the value ⇐ 0 to wait indefinitely.
Also under the same section, you can define the maximum number of connections that can remain idle within the connection pool, without being released. Alternatively, you can set a negative value for no limit.
This section sets your connection string to the Postgres Database. This string invokes a Java Database Connector (JDBC) to your Postgres Databases, and as such does not need the Postgres Client to be installed.
Define here your connection string for each database.
Here you will define the connection string.
These are formatted as:
DBName.Url(as specified in your DatabaseList)followed by the jdbc string and your Database Server information.
Set your Database username and password below. e.g.
Example given below.
This defines where the file containing the Postgres (SQL) queries can be found. This is a customisable file. You can find the query parameter file at:
you can also specify :
This file specify the query file of a database relative path are allowed
The query.props file can be modified or added to, so you can supply your own customised queries through Postbix against your Postgres instances/databases. Each query created has an associated 'Item' or item name that Zabbix will use to identify the query.
The configurable items are formatted as follows.
You have to set the query name under the QueryList in the query.props file. Each query name is comma separated. For example:
You must identify the query by the unique item name you specified in the QueryList, followed by '.Query' for Postbix to recognise that this is the query string. Its important to remember NOT to add the semi-colon “;” to the end of your custom query.
Now you can define what you want Postbix to return to your Zabbix Server if no data is found for your query.
Next you can specify a query that will be executed and if return RaceCondiftionValue the query customQueryItemName is executed otherwise is skipped
customQueryItemName.ACTIVE=[true|false] if true query is executed otherwise skipped
SELECT COUNT (*) LOGS
WHERE FIRST_TIME >= (sysdate -10/60/24)) A,
( SELECT Avg(BYTES) AVG, Count(1), Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes
FROM v$log) B
archive.RaceConditionQuery=select value From v$parameter where name='log_archive_start'
To start the Postbix Daemon, simply run:
The Postbix daemon outputs its log file to the default location of /opt/postbix/logs/ Logging properties can be modified by making your required changes to: /opt/postbix/conf/log4j.properties The property setting responsible for defining the output location is: log4j.appender.Postbix.File=logs/postbix.log
From this properties file, you can modify the location, file name and log format as desired. For additional information, please refer to the official log4j documentation, found at: http://logging.apache.org/log4j/1.2/index.html
The parameters that are dynamically read at each iteration of “PostbixDaemon.Sleep” are as follows;
Any Query added to query.props
Modifications to config.props Available Upcoming Release; To be advised Database List
Currently, the only known items that don't dynamically update are the ZabbixDaemon.MaxThreadNumber, and changes to Connection Pool info.
Postbix is released under and according to the GNU GENERAL PUBLIC LICENSE Version 3, 29 June 2007