/*
* Function: pg_connect
*
* Parses a Zabbix agent request and returns a PostgreSQL connection.
*
* See: http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PQCONNECTDB
*
* Parameter [request]: Zabbix agent request structure.
* The following parameters may be set:
*
* 0: connection string (default: DEFAULT_CONN_STRING)
* 1: connection database (default: DEFAULT_CONN_DBNAME)
*
* Returns: Valid PostgreSQL connection or NULL on error
*/
PGconn *pg_connect(AGENT_REQUEST *request)
{
const char *__function_name = "pg_connect";
PGconn *conn = NULL;
char *param_connstring = NULL, *param_dbname = NULL;
char connstring[MAX_STRING_LEN], *c = NULL;
int param_connstring_len = 0, param_dbname_len = 0, connstring_len = 0;;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// get connection string from first parameter
param_connstring = get_rparam(request, PARAM_CONN_STRING);
param_connstring = (NULL == param_connstring) ? DEFAULT_CONN_STRING : param_connstring;
param_connstring_len = strlen(param_connstring);
// get database name from second parameter
param_dbname = get_rparam(request, PARAM_DBNAME);
param_dbname = (NULL == param_dbname) ? DEFAULT_CONN_DBNAME : param_dbname;
param_dbname_len = strisnull(param_dbname) ? 0 : strlen(param_dbname);
// create buffer to concat connection string and database name
zbx_strlcpy(connstring, param_connstring, sizeof(connstring));
c = connstring;
// append dbname= key
if (!strisnull(param_dbname)) {
if (!strisnull(connstring))
c = strcat2(c, " ");
c = strcat(c, "dbname=");
c = strcat(c, param_dbname);
}
/*
* Breaks in ~ v8.4
// append application name
if (!strisnull(connstring))
c = strcat2(c, " ");
c = strcat(c, "application_name='" STRVER "'");
*/
// connect
zabbix_log(LOG_LEVEL_DEBUG, "Connecting to PostgreSQL with: %s", connstring);
conn = PQconnectdb(connstring);
if(CONNECTION_OK != PQstatus(conn)) {
zabbix_log(LOG_LEVEL_ERR, "Failed to connect to PostgreSQL in %s():\n%s", __function_name, PQerrorMessage(conn));
PQfinish(conn);
conn = NULL;
}
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return conn;
}
/*
* Custom key pg.checkpoint_time_perc
*
* Returns the percentage of time spent writing or syncing checkpoints since
* statistics were reset.
*
* Parameters:
* 0: connection string
* 1: connection database
* 2: action: all (default) | write | sync
*
* Returns: d
*/
int PG_BG_TIME_PERC(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_BG_TIME_PERC"; // Function name for log file
char query[MAX_STRING_LEN];
char *action = NULL, *field = NULL;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// parse action parameter
action = get_rparam(request, PARAM_FIRST);
if (strisnull(action) || 0 == strcmp(action, "all"))
field = "(checkpoint_write_time + checkpoint_sync_time)";
else if (0 == strcmp(action, "write"))
field = "checkpoint_write_time";
else if (0 == strcmp(action, "sync"))
field = "checkpoint_sync_time";
else {
SET_MSG_RESULT(result, zbx_dsprintf(NULL, "Invalid action parameter: %s", action));
return ret;
}
// build query
zbx_snprintf(query, sizeof(query), PGSQL_BG_TIME_PERC, field);
// get result
ret = pg_get_dbl(request, result, query, NULL);
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
int PG_INDEX_IDX_BLKS_PERC(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_INDEX_IDX_BLKS_PERC"; // Function name for log file
char *index = NULL;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
index = get_rparam(request, PARAM_FIRST);
if(strisnull(index)) {
ret = pg_get_percentage(
request,
result,
"pg_statio_all_indexes",
"sum(idx_blks_hit)",
"sum(idx_blks_hit) + sum(idx_blks_read)",
NULL,
NULL);
} else {
ret = pg_get_percentage(
request,
result,
"pg_statio_all_indexes",
"idx_blks_hit",
"idx_blks_hit + idx_blks_read",
"indexrelname",
index);
}
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
/*
* Custom keys pg.index.* (for each field in pg_statio_all_indexes)
*
* Returns the requested IO statistic for the specified index
*
* Parameters:
* 0: connection string
* 1: connection database
* 2: filter by index name (default: sum of all indexes)
*
* Returns: u
*/
int PG_STATIO_ALL_INDEXES(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_STAT_ALL_INDEXES"; // Function name for log file
char *index = NULL;
char *field;
char query[MAX_STRING_LEN];
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// Get stat field from requested key name "pb.index.<field>"
field = &request->key[9];
// Build query
index = get_rparam(request, PARAM_FIRST);
if(strisnull(index))
zbx_snprintf(query, sizeof(query), PGSQL_GET_INDEX_STATIO_SUM, field);
else
zbx_snprintf(query, sizeof(query), "SELECT %s FROM pg_statio_all_indexes WHERE indexrelname = $1", field);
ret = pg_get_int(request, result, query, param_new(index));
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
/*
* Custom key pg.index.discovery
*
* Parameters:
* 0: connection string
* 1: connection database
* 2: search mode: deep (default) | shallow
* 3: filter by schema name
* 4: filter by table name
*
* Returns all known indexes in a PostgreSQL database
*
* Returns:
* {
* "data":[
* {
* "{#OID}":"12345",
* "{#INDEX}":"MyIndex",
* "{#DATABASE}":"MyDatabase",
* "{#SCHEMA}":"public",
* "{#TABLE}":"MyTable",
* "{#OWNER}":"postgres",
* "{#ACCESS}":"btree|hash"}]}
*/
int PG_INDEX_DISCOVERY(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_DB_DISCOVERY"; // Function name for log file
char query[MAX_STRING_LEN], buffer[MAX_STRING_LEN];
char *c = NULL;
char *param_mode = NULL, *param_table = NULL, *param_schema = NULL;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// build the query
zbx_strlcpy(query, PGSQL_DISCOVER_INDEXES, sizeof(query));
c = query;
// filter by schema name
param_schema = get_rparam(request, PARAM_FIRST + 1);
if(!strisnull(param_schema)) {
zbx_snprintf(buffer, sizeof(buffer), " AND n.nspname = '%s'", param_schema);
c = strcat2(c, buffer);
}
// filter by table name
param_table = get_rparam(request, PARAM_FIRST + 2);
if(!strisnull(param_table)) {
zbx_snprintf(buffer, sizeof(buffer), " AND t.relname = '%s'", param_table);
c = strcat2(c, buffer);
}
// build results
param_mode = get_rparam(request, PARAM_FIRST);
if (strisnull(param_mode) || 0 == strcmp(param_mode, "deep")) {
ret = pg_get_discovery_wide(request, result, query, NULL);
} else if (0 == strcmp(param_mode, "shallow")) {
ret = pg_get_discovery(request, result, query, NULL);
} else {
SET_MSG_RESULT(result, zbx_dsprintf(NULL, "Invalid search mode parameter: %s", param_mode));
}
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
/*
* Custom keys pg.index.* (for each field in pg_stat_all_indexes)
*
* Returns the requested statistic for the specified index
*
* Parameters:
* 0: connection string
* 1: connection database
* 2: filter by index name (default: sum of all indexes)
*
* Returns: u
*/
int PG_STAT_ALL_INDEXES(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_STAT_ALL_INDEXES"; // Function name for log file
char *index = NULL;
PGconn *conn = NULL;
PGresult *res = NULL;
char *field;
char query[MAX_STRING_LEN];
char *buffer = NULL;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// Get stat field from requested key name "pb.table.<field>"
field = &request->key[9];
// Build query
index = get_rparam(request, PARAM_FIRST);
if(strisnull(index))
zbx_snprintf(query, sizeof(query), "SELECT SUM(%s) FROM pg_stat_all_indexes", field);
else
zbx_snprintf(query, sizeof(query), "SELECT %s FROM pg_stat_all_indexes WHERE indexrelname = $1", field);
// Connect to PostreSQL
if(NULL == (conn = pg_connect_request(request)))
goto out;
// Execute a query
res = pg_exec(conn, query, param_new(index));
if(PQresultStatus(res) != PGRES_TUPLES_OK) {
zabbix_log(LOG_LEVEL_ERR, "Failed to execute PostgreSQL query in %s() with: %s", __function_name, PQresultErrorMessage(res));
goto out;
}
if(0 == PQntuples(res)) {
zabbix_log(LOG_LEVEL_ERR, "No results returned for query \"%s\" in %s()", query, __function_name);
goto out;
}
// Set result
buffer = strdup(PQgetvalue(res, 0, 0));
SET_UI64_RESULT(result, atoi(buffer));
ret = SYSINFO_RET_OK;
out:
PQclear(res);
PQfinish(conn);
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
/*
* Custom key pg.index.size
*
* Returns the disk usage in bytes for the specified index
*
* Parameters:
* 0: connection string
* 1: connection database
* 2: filter by index name (default: sum of all indexes)
*
* Returns: u
*/
int PG_INDEX_SIZE(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_INDEX_SIZE"; // Function name for log file
char *index = NULL; //, *include = NULL;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// Parse parameters
index = get_rparam(request, PARAM_FIRST);
// Build query
if(strisnull(index))
ret = pg_get_int(request, result, PGSQL_GET_INDEX_SIZE_SUM, NULL);
else
ret = pg_get_int(request, result, PGSQL_GET_INDEX_SIZE, param_new(index));
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
/*
* Custom key pg.tablespace.size
*
* Returns the size of the specified tablespace in bytes
*
* Parameters:
* 0: connection string
* 1: tablespace name
*
* Returns: u
*/
int PG_TABLESPACE_SIZE(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_TABLESPACE_SIZE"; // Function name for log file
char *tablespace = NULL;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// Build query
tablespace = get_rparam(request, PARAM_FIRST);
if(strisnull(tablespace)) {
zabbix_log(LOG_LEVEL_ERR, "No tablespace specified in %s()", __function_name);
goto out;
}
// execute query
ret = pg_get_int(request, result, PGSQL_GET_TS_SIZE, param_new(tablespace));
out:
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}
/*
* build_activity_clause takes agent request parameters for an item key which
* targets the pg_stat_actity table and creates an SQL clause to filter
* results.
*
* This function should be reused for all items which query the pg_stat_activity
* table.
*
* request is the agent request containing the user parameters.
* buf is the character buffer to which the clause is written.
* params is a pointer to a PGparams type which stores query parameters.
* has_clause determines if the the clause starts with "WITH" or "AND".
*
* Returns non-zero on success.
*/
static int build_activity_clause(const AGENT_REQUEST *request, char *buf, PGparams *params, int has_clause) {
const char *__function_name = "build_activity_clause"; // Function name for log file
int i = 0;
char *param = NULL;
char *clause = (0 < has_clause ? PG_AND : PG_WHERE);
int pgi = 0;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// iterate over the available parameters
for(i = 0; i < 4; i++) {
param = get_rparam(request, PARAM_FIRST + i);
if(!strisnull(param)) {
switch(i) {
case 0: // <database>
*params = param_append(*params, param);
if(is_oid(param))
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s datid = $%i", clause, ++pgi);
else
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s datname = $%i", clause, ++pgi);
break;
case 1: // <user>
*params = param_append(*params, param);
if(is_oid(param))
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s usesysid = $%i", clause, ++pgi);
else
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s usename = $%i", clause, ++pgi);
break;
case 2: // <client>
*params = param_append(*params, param);
if(is_valid_ip(param))
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s client_addr = $%i::inet", clause, ++pgi);
else
// requires v9.1+
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s client_hostname = $%i", clause, ++pgi);
break;
case 3: // <waiting>
if(0 == strncmp("true\0", param, 5)) {
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s waiting = TRUE", clause);
} else if(0 == strncmp("false\0", param, 6)) {
zbx_snprintf(buf, MAX_CLAUSE_LEN, " %s waiting = FALSE", clause);
} else {
zabbix_log(LOG_LEVEL_ERR, "Unsupported parameter value: \"%s\" in %s", param, request->key);
return 0;
}
break;
}
buf += strlen(buf);
clause = PG_AND;
}
}
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return 1;
}
/*
* Custom key pg.backends.count
*
* Returns statistics for connected backends (remote clients)
*
* Parameters:
* 0: connection string
* 1: connection database
* 2: filter by database oid name
* 3: filter by user OID or name
* 4: filter by hostname or IP address of the connected host
* 5: return only waiting backends
*
* Returns: u
*/
int PG_BACKENDS_COUNT(AGENT_REQUEST *request, AGENT_RESULT *result)
{
int ret = SYSINFO_RET_FAIL; // Request result code
const char *__function_name = "PG_BACKENDS_COUNT"; // Function name for log file
char query[MAX_QUERY_LEN];
char *p = &query[0];
int i = 0;
char *param = NULL;
char *clause = PG_WHERE;
PGparams pgparams = NULL;
int pgi = 0;
zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
// Build the sql query
memset(query, 0, MAX_QUERY_LEN);
zbx_strlcpy(p, PGSQL_GET_BACKENDS, MAX_QUERY_LEN);
p += strlen(p);
// iterate over the available parameters
for(i = 0; i < 4; i++) {
param = get_rparam(request, PARAM_FIRST + i);
if(!strisnull(param)) {
switch(i) {
case 0: // <database>
pgparams = param_append(pgparams, param);
if(is_oid(param))
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s datid=$%i", clause, ++pgi);
else
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s datname=$%i", clause, ++pgi);
break;
case 1: // <user>
pgparams = param_append(pgparams, param);
if(is_oid(param))
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s usesysid=$%i", clause, ++pgi);
else
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s usename=$%i", clause, ++pgi);
break;
case 2: // <client>
pgparams = param_append(pgparams, param);
if(is_valid_ip(param))
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s client_addr = $%i::inet", clause, ++pgi);
else
// requires v9.1+
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s client_hostname=$%i", clause, ++pgi);
break;
case 3: // <waiting>
if(0 == strncmp("true", param, 4)) {
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s waiting=TRUE", clause);
} else if(0 == strncmp("false", param, 5)) {
zbx_snprintf(p, MAX_CLAUSE_LEN, " %s waiting=FALSE", clause);
} else {
zabbix_log(LOG_LEVEL_ERR, "Unsupported 'Waiting' parameter: %s in %s()", param, __function_name);
goto out;
}
break;
}
p += strlen(p);
clause = PG_AND;
}
}
ret = pg_get_int(request, result, query, pgparams);
out:
zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __function_name);
return ret;
}