Cacti (home)ForumsRepositoryDocumentation
Cacti: offical forums and support
It is currently Wed Jul 23, 2014 3:07 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 54 posts ]  Go to page 1, 2, 3, 4  Next
Author Message
 Post subject: PostgreSQL Host Template
PostPosted: Tue Sep 18, 2007 6:32 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
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 :)


Attachments:
File comment: based on pg_stat_database
14.png
14.png [ 75.28 KiB | Viewed 45340 times ]
File comment: based on pg_stat_database
13.png
13.png [ 80.04 KiB | Viewed 45340 times ]
File comment: based on pg_statio_user_tables
12.png
12.png [ 71.59 KiB | Viewed 45340 times ]
File comment: based on pg_stat_user_tables
11.png
11.png [ 83.48 KiB | Viewed 45340 times ]
File comment: based on pg_stat_user_tables
1.png
1.png [ 97.79 KiB | Viewed 45341 times ]


Last edited by _Dmitry_ on Tue Feb 12, 2008 4:25 pm, edited 7 times in total.
Top
 Profile  
 
 Post subject: Re: PostgreSQL Host Template
PostPosted: Tue Sep 18, 2007 10:45 pm 
Offline

Joined: Tue Sep 18, 2007 10:44 pm
Posts: 1
_Dmitry_ wrote:
If anyone interested I'll upload Host Template with all Scripts and XML here later.


Yes, please do.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 19, 2007 6:18 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
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.


Attachments:
db_size.png
db_size.png [ 20.36 KiB | Viewed 41561 times ]
postgresql_database_stats_1.tgz [10.67 KiB]
Downloaded 3021 times
File comment: CACTI Files
postgresql_database_stats.tgz [9.92 KiB]
Downloaded 2325 times


Last edited by _Dmitry_ on Wed Feb 06, 2008 8:24 am, edited 1 time in total.
Top
 Profile  
 
 Post subject: cacti 0.8.6j can not load the template
PostPosted: Wed Sep 26, 2007 5:13 am 
Offline

Joined: Wed Sep 26, 2007 5:11 am
Posts: 1
[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?


Top
 Profile  
 
 Post subject: Re: cacti 0.8.6j can not load the template
PostPosted: Mon Oct 01, 2007 4:18 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
gafton wrote:
_Dmitry_ wrote:
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. :roll:


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 26, 2007 1:10 pm 
Offline

Joined: Tue Oct 23, 2007 10:48 am
Posts: 2
_dmitry_,

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

Thanks,
gfish


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 11, 2008 5:22 pm 
Offline

Joined: Tue Jan 30, 2007 2:47 pm
Posts: 9
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'


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 22, 2008 10:06 am 
Offline

Joined: Tue May 08, 2007 2:49 pm
Posts: 8
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" \
GPRINT:a:LAST:" Current\:%8.0lf" \
GPRINT:a:AVERAGE:"Average\:%8.0lf" \
GPRINT:b:MAX:"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


Top
 Profile  
 
 Post subject: spine
PostPosted: Mon Feb 04, 2008 9:21 am 
Offline

Joined: Thu Jul 17, 2003 5:32 am
Posts: 2
Hi,

first of all, thnx for creating this.

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

_________________
I'm a falling stone in a world of glass


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 06, 2008 7:25 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
flyit wrote:
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.


Attachments:
cacti_psql.png
cacti_psql.png [ 34.7 KiB | Viewed 41575 times ]
Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 06, 2008 7:37 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
pachu wrote:
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 ?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 06, 2008 7:51 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
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.


Attachments:
postgresql_database_stats_1.tgz [10.67 KiB]
Downloaded 1260 times
File comment: Database Size Graph
db_size.png
db_size.png [ 20.36 KiB | Viewed 41568 times ]
Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 06, 2008 11:34 am 
Offline

Joined: Tue May 08, 2007 2:49 pm
Posts: 8
Gracias Dimitry..

i can't create any graphs.

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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 07, 2008 2:38 am 
Offline

Joined: Tue Sep 18, 2007 4:58 am
Posts: 36
Location: DE
pachu wrote:
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).


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 11, 2008 3:48 pm 
Offline

Joined: Wed Dec 21, 2005 4:14 am
Posts: 2
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 54 posts ]  Go to page 1, 2, 3, 4  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Protected by Anti-Spam ACP Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group