PostgreSQL Host Template

[_Dmitry_]

Hello,

recently I was inspired by MySQL Host Template and made my own for PostgreSQL Host Template with Data Queries to get all DB from it and draw following Graphs for them.

All Statistics are based on this Docs: http://www.postgresql.org/docs/current/ ... stats.html

If anyone interested I'll upload Host Template with all Scripts and XML here later.

Installation instruction:

Make sure you have enabled Statistic Collector in Postgres (postgresql.conf)

# - Query/Index Statistics Collector -
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = off


1. Create new User in PostgreSQL ,for example monitor (you don't have to grant any special roles to this users AFAIK, any user has access to Statistic Tables).
2. Try to connect remotely under this user to postgres Database:
psql -h YOUR_POSTGRES_HOST -U monitor -W postgres
and try execute following SQL:
select * from pg_stat_all_tables;
If everything work proceed with installation otherwise fix your problem till you get results.

3. Cacti Import
pgsql_stats.php - the Script, put this in <cacti_root>/scripts/
postgres_dbstat.xml - the Definition, put this in <cacti_root>/resource/script_queries
cacti_data_query_postgresql_database_stats.xml - Data Query + Graphs - import into Cacti

4. Edit pgsql_stats.php line 173 and 174 and put username and password you have created at point 1. (I don't know how to pass username and password parameters to Data Queries. If you know how, let me know so I can remote username and password from script)

5. Edit your Host Template or Device and add PostgreSQL Database Stats in Data Queries.

6. Create your Graphs

Please let me know if you had any troubles or have any wishes

[msw]

If anyone interested I'll upload Host Template with all Scripts and XML here later.

Yes, please do.

[_Dmitry_]

See post 1 for instructions.

Let me know if any problem occurs.

---------------------------------------------------

06.02.2008

Added new Graph: Database Size.

Instalation is the same except:

4. Edit pgsql_stats.php line 197 and 198 and put username and password you have created at point 1.

[gafton]

[quote="_Dmitry_"]See post 1 for instructions.

Let me know if any problem occurs.[/quote]

Using cacti 0.8.6j, it complains that the template was generated using a newer version of cacti. What version did you use to export the template?

[_Dmitry_]

See post 1 for instructions.

Let me know if any problem occurs.

Using cacti 0.8.6j, it complains that the template was generated using a newer version of cacti. What version did you use to export the template?

Version 0.8.6j.

[gangstafish]

_dmitry_,

I was wondering if you might have some insight on how to get average response time of transactions/queries. Any insight?

Thanks,
gfish

[flyit]

Hi,
So the install went fine and I get data back from my db when I rung the query from the machine but when using your script I do not get any data back. All it will give me is the name of the database. Any tips or hints you can share?

Here is what a verbose query give me.

Running data query [15].
+ Found type = '4 '[script query].
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ XML file parsed ok.
+ Executing script for list of indexes '/usr/bin/php /usr/local/cacti/scripts/pgsql_stats.php 10.10.85.11 index'
+ Executing script query '/usr/bin/php /usr/local/cacti/scripts/pgsql_stats.php 10.10.85.11 query dbname'
+ Found item [pg_DBName='db_baby'] index: db_baby
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'

[pachu]

HELO

i´am having problems .. Could you help

merci beaucoups


Data Source Debug


Notice: Undefined index: in /opt/lampp/htdocs/cacti/lib/rrd.php on line 225

Notice: Undefined index: in /opt/lampp/htdocs/cacti/lib/rrd.php on line 225
/usr/local/rrdtool-1.2.26/bin/rrdtool create \
/opt/lampp/htdocs/cacti/rra/localhost_trans_rollback_29.rrd \
--step 60 \
DS:trans_rollback:COUNTER:120:0:U \
DS:trans_comm:COUNTER:120:0:U \
RRA::0.5:1:864 \
RRA::0.5:1:25920 \
RRA:AVERAGE:0.5:1:500 \
RRA:AVERAGE:0.5:1:600 \
RRA:AVERAGE:0.5:6:700 \
RRA:AVERAGE:0.5:1:4320 \
RRA:AVERAGE:0.5:1:8928 \
RRA:AVERAGE:0.5:24:775 \
RRA:AVERAGE:0.5:1:25920 \
RRA:AVERAGE:0.5:1:25920 \
RRA:AVERAGE:0.5:1:44640 \
RRA:AVERAGE:0.5:5:25920 \
RRA:AVERAGE:0.5:6:35040 \
RRA:AVERAGE:0.5:288:797 \
RRA:AVERAGE:0.5:6:44640 \
RRA:AVERAGE:0.5:24:13140 \
RRA:AVERAGE:0.5:30:25920 \
RRA:AVERAGE:0.5:30:35040 \
RRA:AVERAGE:0.5:120:13140 \
RRA:AVERAGE:0.5:180:35040 \
RRA:AVERAGE:0.5:720:13140 \
RRA:MAX:0.5:1:500 \
RRA:MAX:0.5:1:600 \
RRA:MAX:0.5:6:700 \
RRA:MAX:0.5:24:775 \
RRA:MAX:0.5:288:797 \


RRDTool Command:

/usr/local/rrdtool-1.2.26/bin/rrdtool graph - \
--imgformat=PNG \
--start=-86400 \
--end=-60 \
--title="Localhost - DB Row - bit" \
--base=1000 \
--height=200 \
--width=700 \
--alt-autoscale-max \
--lower-limit=1 \
--vertical-label="" \
--slope-mode \
--font TITLE:12: \
--font AXIS:8: \
--font LEGEND:10: \
--font UNIT:8: \
DEF:a="/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd":n_tup_del:AVERAGE \
DEF:b="/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd":n_tup_del:MAX \
DEF:c="/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd":n_tup_ins:AVERAGE \
DEF:d="/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd":n_tup_ins:MAX \
DEF:e="/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd":n_tup_upd:AVERAGE \
DEF:f="/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd":n_tup_upd:MAX \
LINE1:a#FF0000FF:"Deleted" \
GPRINTLAST:" Current\:%8.0lf" \
GPRINTAVERAGE:"Average\:%8.0lf" \
GPRINTMAX:"Maximum\:%8.0lf\n" \
LINE1:c#35962BFF:"Inserted" \
GPRINT:c:LAST:"Current\:%8.0lf" \
GPRINT:c:AVERAGE:"Average\:%8.0lf" \
GPRINT:d:MAX:"Maximum\:%8.0lf\n" \
LINE1:e#EA8F00FF:"Updated" \
GPRINT:e:LAST:" Current\:%8.0lf" \
GPRINT:e:AVERAGE:"Average\:%8.0lf" \
GPRINT:f:MAX:"Maximum\:%8.0lf\n"
RRDTool Says:

ERROR: opening '/opt/lampp/htdocs/cacti/rra/localhost_n_tup_del_26.rrd': No such file or directory




LOG

01/22/2008 01:23:10 PM - CMDPHP: Poller[0] Host[3] DS[20] SNMP: v1: 10.35.9.2, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.11, output: 1907102169
01/22/2008 01:23:09 PM - CMDPHP: Poller[0] Host[1] DS[28] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get idx_blks_hit bit, output: 0
01/22/2008 01:23:09 PM - CMDPHP: Poller[0] Host[3] DS[20] SNMP: v1: 10.35.9.2, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.11, output: 59752819
01/22/2008 01:23:09 PM - CMDPHP: Poller[0] Host[1] DS[28] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get idx_blks_read bit, output: 0
01/22/2008 01:23:09 PM - CMDPHP: Poller[0] Host[3] DS[19] SNMP: v1: 10.35.9.2, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.10, output: 658497858
01/22/2008 01:23:09 PM - CMDPHP: Poller[0] Host[3] DS[19] SNMP: v1: 10.35.9.2, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.10, output: 628185873
01/22/2008 01:23:08 PM - CMDPHP: Poller[0] Host[1] DS[28] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get heap_blks_hit bit, output: 0
01/22/2008 01:23:08 PM - CMDPHP: Poller[0] Host[3] DS[18] SNMP: v1: 10.35.9.2, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.9, output: 1007474950
01/22/2008 01:23:08 PM - CMDPHP: Poller[0] Host[1] DS[28] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get heap_blks_read bit, output: 0
01/22/2008 01:23:08 PM - CMDPHP: Poller[0] Host[3] DS[18] SNMP: v1: 10.35.9.2, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.9, output: 3346177633
01/22/2008 01:23:08 PM - CMDPHP: Poller[0] Host[1] DS[27] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get blks_hit bit, output: 941020
01/22/2008 01:23:07 PM - CMDPHP: Poller[0] Host[1] DS[26] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get n_tup_upd bit, output: 0
01/22/2008 01:23:07 PM - CMDPHP: Poller[0] Host[3] DS[17] SNMP: v1: 10.35.9.2, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.7, output: 1694861823
01/22/2008 01:23:07 PM - CMDPHP: Poller[0] Host[1] DS[26] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get n_tup_ins bit, output: 0
01/22/2008 01:23:07 PM - CMDPHP: Poller[0] Host[3] DS[17] SNMP: v1: 10.35.9.2, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.7, output: 4194611801
01/22/2008 01:23:06 PM - CMDPHP: Poller[0] Host[1] DS[26] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get n_tup_del bit, output: 0
01/22/2008 01:23:06 PM - CMDPHP: Poller[0] Host[3] DS[12] SNMP: v1: 10.35.9.2, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.2, output: 1795140092
01/22/2008 01:23:06 PM - CMDPHP: Poller[0] Host[1] DS[25] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get seq_tup_read bit, output: 0
01/22/2008 01:23:06 PM - CMDPHP: Poller[0] Host[3] DS[12] SNMP: v1: 10.35.9.2, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.2, output: 2099011359
01/22/2008 01:23:06 PM - CMDPHP: Poller[0] Host[1] DS[25] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get idx_tup_fetch bit, output: 0
01/22/2008 01:23:05 PM - CMDPHP: Poller[0] Host[1] DS[24] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get idx_scan bit, output: 0
01/22/2008 01:23:05 PM - CMDPHP: Poller[0] Host[1] DS[24] CMD: /opt/lampp/bin/php /opt/lampp/htdocs/cacti/scripts/pgsql_stats.php 127.0.0.1 get seq_scan bit, output: 1

[xs]

Hi,

first of all, thnx for creating this.

-- Edit, old msg removed --
Nevermind, my bad. all works fine

[_Dmitry_]

Hi,
So the install went fine and I get data back from my db when I rung the query from the machine but when using your script I do not get any data back. All it will give me is the name of the database. Any tips or hints you can share?

Here is what a verbose query give me.

Running data query [15].
+ Found type = '4 '[script query].
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ XML file parsed ok.
+ Executing script for list of indexes '/usr/bin/php /usr/local/cacti/scripts/pgsql_stats.php 10.10.85.11 index'
+ Executing script query '/usr/bin/php /usr/local/cacti/scripts/pgsql_stats.php 10.10.85.11 query dbname'
+ Found item [pg_DBName='db_baby'] index: db_baby
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/local/cacti/resource/script_queries/postgres_dbstat.xml'

But that's mean it works. Now you have to create Graphs for this Database.
Select checkbox create Graph. Afterwards select another graph from dropdown menu and create it. Repeat for all available Graphs.

[_Dmitry_]

HELO

i´am having problems .. Could you help

merci beaucoups


Data Source Debug


Notice: Undefined index: in /opt/lampp/htdocs/cacti/lib/rrd.php on line 225

Notice: Undefined index: in /opt/lampp/htdocs/cacti/lib/rrd.php on line 225
/usr/local/rrdtool-1.2.26/bin/rrdtool create \
...................................................

Do you have this Problem only with 1 Graph ?

[_Dmitry_]

Added new Graph: Database Size.

Instalation is the same except:

4. Edit pgsql_stats.php line 197 and 198 and put username and password you have created at point 1.

[pachu]

Gracias Dimitry..

i can't create any graphs.

y have working 8 graphs but i can't create more !!.

[_Dmitry_]

Gracias Dimitry..

i can't create any graphs.

y have working 8 graphs but i can't create more !!.

What exactly you can't create and what you can create ?

Please provide Screenshot of Host Graph Creation Screen (where you can create Graphs for Host (Create Graph for this Host)) and Data Query Debug Output (Verbose Query).

[dustsmoke]

Shucks, too new of a version/export for my debian packaged cacti. Think I'll have to figure out how to backport it to an older version.