ZenPacks : PostgreSQL Database Monitor : Comments

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 19 Dec 2010 13:23:39 GMT

Hi I got the error message: "

Could not fetch data from source (Received error ('long' object has no attribute 'strip') from query: SELECT pg_database_size(datname) as sizeUsed FROM pg_database ) "

Could you please give me some help?

RE: PostgreSQL Database Monitor

community@zenoss.org — Sat, 25 Dec 2010 12:13:48 GMT

Greetings! I have this zenpack installed but it does not appears in availible Template Binds when i want to add it to current device.

Zenoss version 3.0.3

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 26 Dec 2010 16:17:33 GMT

Hi ALyarskiy,

you don't need to bind PgSqlDatabase components template to device or device class. Simply add community.odbc.PgSqlDatabaseMap collector plugin to zCollectorPlugins list and remodel your device.

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 26 Dec 2010 16:35:30 GMT

It seems to be SQLDataSource zenpack bug.

Find in file ZenPacks.community.SQLDataSource-1.5.egg/ZenPacks/community/SQLDataSource/SQLClient.py line:

kbIns = tuple([str(t.strip('"\' ')) for t in row[(0 - len(kbKey)):]])

and replace it with 2 lines:

try: kbIns = tuple([str(t.strip('"\' ')) for t in row[(0 - len(kbKey)):]])
except: kbIns = tuple([str(t) for t in row[(0 - len(kbKey)):]])

I do not really know what it must do, but it seems to fix this error.

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 27 Dec 2010 18:05:29 GMT

Hi Dmitry,

thanks, fixed.

Egor

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 02 Jan 2011 01:14:32 GMT

I got the error message:

2011-01-02 09:07:04,732 ERROR zen.SQLClient: Received error (('07006', '[07006] [unixODBC]Received an unsupported type from Postgres. (14) (SQLGetData)')) from query: SELECT datname as dbname, pg_database_size(datname) as totalBlocks FROM pg_database;

unixodbc not support bitint？

can you help me？

zenoss 3.0.3

pgsql-odbc-2.1

ZenPacks.community.RDBMS

community

Egor Puzanov

2.1

Yes

ZenPacks.community.SQLDataSource

community

Egor Puzanov

1.6

Yes

centos 5.5

unixODBC x86_64 2.2.11-7.1

postgresql-odbc x86_64 08.03.0400-1PGDG.rhel5

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 02 Jan 2011 06:48:59 GMT

display  conflicts in POSTGRESQL ODBC DATABASE MONITOR and MySQL ODBC Database Monitor.

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 02 Jan 2011 12:07:03 GMT

Hi wezi,

you can monitor both database types, but with a small trick.

1. add community.odbc.PgSqlDatabaseMap modeler pluging to 'Modeler Plugins' list and model your server.

2. Lock Postgress databases and Server instances from deletion

3. remove community.odbc.PgSqlDatabaseMap modeler pluging

4. add community.odbc.MySqlDatabaseMap modeler pluging to 'Modeler Plugins' list and model your server.

5. Lock MySQL databases and Server instances from deletion

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 02 Jan 2011 13:35:40 GMT

thinks.

1.unsupported type from Postgres.

modify

SELECT datname as dbname, pg_database_size(datname) as totalBlocks FROM pg_database

SELECT pg_database_size(datname)::float as sizeUsed FROM pg_database

but totalBlocks is 0.0b

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 02 Jan 2011 14:15:50 GMT

Hi weizi,

try : SELECT pg_database_size(datname)::float as sizeUsed, datname FROM pg_database WHERE datname='${here/dbname}'

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 03 Jan 2011 00:59:05 GMT

hi bigegor.

it is ok. thinks

1.modify PgSqlDatabaseMap.py 36 line

"SELECT datname as dbname, pg_database_size(datname) as totalBlocks FROM pg_database;"""

"SELECT datname as dbname, pg_database_size(datname)::float as totalBlocks FROM pg_database;"""

2.modify dmd/Devices/Server/rrdTemplates/PgSqlDatabase/sizeUsed

SELECT pg_database_size(datname) as sizeUsed FROM pg_database WHERE datname='${here/dbname}';

SELECT pg_database_size(datname)::float as sizeUsed, datname FROM pg_database WHERE datname='${here/dbname}';

RE: PostgreSQL Database Monitor

community@zenoss.org — Wed, 05 Jan 2011 03:52:03 GMT

Could you please tell me how to make the database(template0) out of data retrieving? We just need make sure it lives, and it does not make sense to know other information; otherwise, we will get the following error:

"Could not fetch data from source (Received error (('55000', '[55000] [unixODBC]FATAL: database "template0" is not currently accepting connections (210) "

RE: PostgreSQL Database Monitor

community@zenoss.org — Fri, 07 Jan 2011 10:03:29 GMT

select

data is null

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 28 Mar 2011 13:51:07 GMT

For x86_64 RH-like systems there is bug in unixODBC config - in file /etc/odbcinst.ini replace /usr/lib to /usr/lib64

RE: PostgreSQL Database Monitor

community@zenoss.org — Tue, 26 Apr 2011 17:05:08 GMT

Is there any way to bring the graphs into custom reports? I can see the data_source and data_point but I don't know how to specify the database for graphing in a custom report. Is this even possible?

RE: PostgreSQL Database Monitor

community@zenoss.org — Thu, 26 May 2011 20:14:59 GMT

Hi Egor,

What is the delimiter used in zPgSqlDatabaseIgnoreNames?

Thanks.

RE: PostgreSQL Database Monitor

community@zenoss.org — Thu, 26 May 2011 20:52:35 GMT

Hi Greg,

you must use RegExp syntax in zPgSqlDatabaseIgnoreNames, like in others zProperties (o.e zLocalIpAddresses).

Egor

RE: PostgreSQL Database Monitor

community@zenoss.org — Thu, 26 May 2011 20:54:45 GMT

Wow, that was quick. Thanks!

RE: PostgreSQL Database Monitor

community@zenoss.org — Fri, 11 May 2012 09:13:57 GMT

Since I installed this zenpack, it is generating error logs in the postgresql log file:

2012-05-11 05:08:59.792 EDT <REDACTED> [unknown] postgres <REDACTED> idle in transaction 08P01 <REDACTED>: LOG: unexpected EOF on client connection

2012-05-11 05:08:59.792 EDT postgres [unknown] postgres <REDACTED> idle in transaction 08P01 postgres: LOG: unexpected EOF on client connection

This happens every 5 minutes, the exact time the datasource kicks off in zenoss.

Apparently the connection is not issuing a connection close.

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 13 May 2012 20:32:54 GMT

Hi Michael,

how your zPgSqlConnectionString looks like?

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 14 May 2012 12:26:02 GMT

In python my connection string is as follows:

'DRIVER={PostgreSQL};Server=' + server + ';UID=' + userid + ';PWD=' + password + ';Database=' + database + ';Port=' + Port + ';'

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 14 May 2012 12:27:41 GMT

IM sorry ignore my last comment, that is a connection string i use when I directly connect to postgresql, not the one that the datasource is issuing

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 14 May 2012 12:29:55 GMT

Here is what is being executed using the pgServer datasource:

Executing command $ZENHOME/libexec/poll_postgres.py '<REDACTED>' '5432' 'postgres' '<my password>' 'False' server against <REDACTED>

I don't see a zPgSqlConnectionString in the configuration properties for the device to which this template is bound.

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 14 May 2012 17:51:20 GMT

Hi Michael,

it seems like you are using ZenPacks.zenoss.PostgreSQL, but asking your questions here where ZenPacks community.PgSQLMon will be discussed. Sorry, i have no answer.

RE: PostgreSQL Database Monitor

community@zenoss.org — Wed, 25 Jul 2012 17:18:13 GMT

Hello Egor,

After installing the ZenPack, I am able to view the databases in my database server, but all of the perf graphs are empty. The ZenPack has been enabled for the device for over a week. I just reinstalled the ZenPack with the same issue. I am running PostgreSQL v8.3.16 and ZenOSS v3.0.2.

I activated verbose logging for the zenperfsql daemon, and here is what I get when the ZenPack fetches the perf data:

2012-07-25 13:10:16,418 DEBUG zen.zenperfsql: Datasource PgSqlDatabaseStat/statUser query:'SELECT

SUM(heap_blks_hit) as heap_blks_hit,

SUM(heap_blks_read) as heap_blks_read,

SUM(idx_blks_hit) as idx_blks_hit,

SUM(idx_blks_read) as idx_blks_read,

SUM(idx_scan) as idx_scan,

SUM(idx_tup_fetch) as idx_tup_fetch,

SUM(n_tup_ins) as n_tup_ins,

SUM(n_tup_upd) as n_tup_upd,

SUM(n_tup_del) as n_tup_del,

SUM(seq_scan) as seq_scan,

SUM(seq_tup_read) as seq_tup_read,

SUM(tidx_blks_hit) as tidx_blks_hit,

SUM(tidx_blks_read) as tidx_blks_read,

SUM(toast_blks_hit) as toast_blks_hit,

SUM(toast_blks_read) as toast_blks_read

FROM

(SELECT

C.oid AS relid,

N.nspname AS schemaname,

C.relname AS relname,

pg_stat_get_numscans(C.oid) AS seq_scan,

pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,

sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,

sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +

pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,

pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,

pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,

pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,

pg_stat_get_blocks_fetched(C.oid) -

pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,

pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,

sum(pg_stat_get_blocks_fetched(I.indexrelid) -

pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,

sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,

pg_stat_get_blocks_fetched(T.oid) -

pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,

pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,

pg_stat_get_blocks_fetched(X.oid) -

pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,

pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit

FROM pg_class C LEFT JOIN

pg_index I ON C.oid = I.indrelid LEFT JOIN

pg_class T ON C.reltoastrelid = T.oid LEFT JOIN

pg_class X ON T.reltoastidxid = X.oid

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

WHERE C.relkind IN ('r', 't')

GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid) AS t

WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')'

2012-07-25 13:10:16,549 DEBUG zen.collector.scheduler: Task psf3dbpg1 300 237156584 changing state from FETCH_DATA to PARSING_DATA

2012-07-25 13:10:16,549 DEBUG zen.collector.scheduler: Task psf3dbpg1 300 237156584 changing state from PARSING_DATA to STORE_PERF_DATA

2012-07-25 13:10:16,550 DEBUG zen.collector.scheduler: Task psf3dbpg1 300 237156584 finished, result: <twisted.python.failure.Failure <type 'exceptions.TypeError'>>

2012-07-25 13:10:16,551 DEBUG zen.collector.scheduler: Task psf3dbpg1 300 237156584 changing state from STORE_PERF_DATA to IDLE

I am not sure what is causing the exceptions.TypeError.

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 29 Jul 2012 19:26:05 GMT

Hi slew,

it will be helpful if you can provide additional information:

1. SQL DataSource version

2. PgSQLMon version

3. zPgSqlConnectionString

RE: PostgreSQL Database Monitor

community@zenoss.org — Mon, 30 Jul 2012 15:19:27 GMT

Hello bigegor,

Thanks for your help on this. Here is what you asked for:

1. SQLDataSource: v2.2

2. PgSQLMon: v2.5

3. 'pyisqldb',DRIVER='{PostgreSQL}',host='${here/manageIp}',port='${here/port}',database='${here/dbname}',user='${here/zPgSqlUsername}',password='${here/zPgSqlPassword}',ansi=True

Here are the other relevant zProperties:

zPgSqlPorts: 5432

zPgSqlUsername: zenoss

zPgSqlDatabaseIgnoreNames: ^postgres|^template0|^template1

RE: PostgreSQL Database Monitor

community@zenoss.org — Sun, 05 Aug 2012 15:35:55 GMT

Hi slew,

try to use pyodbc or pg8000 DB-API module. Connection strings examples you can find here : https://github.com/epuzanov/ZenPacks.community.PgSQLMon/blob/master/README.rst

RE: PostgreSQL Database Monitor

community@zenoss.org — Fri, 19 Apr 2013 21:16:10 GMT

Hi, Thanks a lot for your db zenpacks but I am experiencing a problem when trying to load the database performance graphs, I receive the error ValueError: cannot convert float NaN to integer.

ZenPack information:

SQLDataSource: 2.4

PgSQLMon: 2.5

zPgSQLConnectionString: 'pyodbc',DRIVER='{PostgreSQL}',host='${here/manageIp}',port='${here/port}',database='${here/dbname}',user='${here/zPgSqlUsername}',password='${here/zPgSqlPassword}',ansi=True

I have 4 databases (template0, template1, postgres and P). When I run rrdtool lastupdate statDb_sizeUsed.rrd for each database they all return valid numbers execpt for P which returns:


 ds0

1366402935: U

This looks to be the problem. Indeed, by placing P in the zPgSQLIgnoredDatabaseNames and remodeling, I no longer see the error. (The error is also gone if I replace the P statDb_sizeUsed.rrd with another databases copy of the file).

Debugging zenperfsql I see the following query being executed:


2013-04-19 16:37:03,315 DEBUG zen.zenperfsql: Datasource PgSqlDatabase/statDb query:'SELECT

    pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,

    pg_stat_get_db_blocks_fetched(D.oid) -

        pg_stat_get_db_blocks_hit(D.oid) AS blks_read,

    pg_stat_get_db_numbackends(D.oid) AS numbackends,

    pg_database_size(datname)::float AS sizeUsed,

    pg_stat_get_db_xact_commit(D.oid) AS xact_commit,

    pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,

    D.datname AS datname

FROM pg_database D

WHERE datname='P''

Which if I run manually gives me a valid number for sizeUsed.


  blks_hit   | blks_read  | numbackends |   sizeused    | xact_commit | xact_rollback | datname  

-------------+------------+-------------+---------------+-------------+---------------+----------

 10874272122 | 1293627020 |           6 | 1892585448248 |      666501 |            72 | P

Later on in the debug dump I see statDb_sizeUsed.rrd is updated with correct values for each database execept for P:


2013-04-19 16:37:05,493 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/template0/statDb_xact_commit.rrd: 0L, @ N

2013-04-19 16:37:05,493 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/template0/statDb_xact_rollback.rrd: 0L, @ N

2013-04-19 16:37:05,494 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_blks_hit.rrd: 1344578L, @ N

2013-04-19 16:37:05,494 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_blks_read.rrd: 574274L, @ N

2013-04-19 16:37:05,495 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_numbackends.rrd: 0.0, @ N

2013-04-19 16:37:05,495 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_sizeUsed.rrd: 6030136.0, @ N

2013-04-19 16:37:05,495 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_status_count.rrd: 1.0, @ N

2013-04-19 16:37:05,495 DEBUG zen.thresholds: Checking value 1.0 on Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_status_count

2013-04-19 16:37:05,496 DEBUG zen.MinMaxCheck: Checking statDb_status_count 1.0 against min 1 and max None

2013-04-19 16:37:05,496 DEBUG zen.zenperfsql: Queued event (total of 3) {'severity': 0, 'min': 1, 'max': None, 'component': 'postgres', 'summary': 'threshold of status restored: current value 1.000000', 'current': 1.0, 'eventKey': 'statDb|statDb_status_count', 'device': 'xxx.xxx.xxx.xxx', 'eventClass': '/Status/PgSqlDatabase'}

2013-04-19 16:37:05,497 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_xact_commit.rrd: 33774L, @ N

2013-04-19 16:37:05,497 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/postgres/statDb_xact_rollback.rrd: 2L, @ N

2013-04-19 16:37:05,497 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_blks_hit.rrd: 10883063955L, @ N

2013-04-19 16:37:05,498 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_blks_read.rrd: 1294130538L, @ N

2013-04-19 16:37:05,498 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_numbackends.rrd: 6.0, @ N

2013-04-19 16:37:05,499 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_status_count.rrd: 1.0, @ N

2013-04-19 16:37:05,499 DEBUG zen.thresholds: Checking value 1.0 on Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_status_count

2013-04-19 16:37:05,499 DEBUG zen.MinMaxCheck: Checking statDb_status_count 1.0 against min 1 and max None

2013-04-19 16:37:05,500 DEBUG zen.zenperfsql: Queued event (total of 4) {'severity': 0, 'min': 1, 'max': None, 'component': 'p', 'summary': 'threshold of status restored: current value 1.000000', 'current': 1.0, 'eventKey': 'statDb|statDb_status_count', 'device': 'xxx.xxx.xxx.xxx', 'eventClass': '/Status/PgSqlDatabase'}

2013-04-19 16:37:05,500 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_xact_commit.rrd: 666536L, @ N

2013-04-19 16:37:05,500 DEBUG zen.RRDUtil: /opt/zenoss/perf/Devices/xxx.xxx.xxx.xxx/os/softwaredatabases/p/statDb_xact_rollback.rrd: 72L, @ N

2013-04-19 16:37:05,500 DEBUG zen.zenperfsql: Queued event (total of 5) {'eventKey': 'statDb', 'severity': 0, 'device': 'xxx.xxx.xxx.xxx', 'eventClass': '/Status/PgSqlDatabase', 'component': 'template1', 'summary': 'Datasource PgSqlDatabase/statDb query completed successfully'}

Why would the sizeUsed be causing this problem for only the 1 database?