PostgreSQL Host Template

[jofficer]

OK, I followed the instructions listed on the initial posting. Although I'm not sure if I'm getting proper results, and my gut tells me I'm not. I'm not the administrator of the PostgreSQL DB and as such I'm limited in my knowledge.

The admin is willing to help, as he likes the idea of monitoring the server.

Here are the results of my work thus far:

I have a connection to our postgre DB, using a 'test' database. User 'monitor' is setup without a password.

Code: Select all

cacti scripts # psql -h db-2 -U monitor test
Welcome to psql 8.0.15, the PostgreSQL interactive terminal.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

test=>

I run the referenced query:

Code: Select all

test=> select * from pg_stat_all_tables
test->

Please notice the change in the prompt, from "test=>" to "test->" .

This is as far as I have gotten. I have modified the php script, but am unsure if I need to leave in the single quotes, and whether or not any changes need to be made if I'm accessing the DB without a password.

Any assistance is greatly appreciated.

Thanks to all who are working on this!

joey

[_Dmitry_]

OK, I followed the instructions listed on the initial posting. Although I'm not sure if I'm getting proper results, and my gut tells me I'm not. I'm not the administrator of the PostgreSQL DB and as such I'm limited in my knowledge.

The admin is willing to help, as he likes the idea of monitoring the server.

Here are the results of my work thus far:

I have a connection to our postgre DB, using a 'test' database. User 'monitor' is setup without a password.

Code: Select all

cacti scripts # psql -h db-2 -U monitor test
Welcome to psql 8.0.15, the PostgreSQL interactive terminal.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

test=>

I run the referenced query:

Code: Select all

test=> select * from pg_stat_all_tables
test->

Please notice the change in the prompt, from "test=>" to "test->" .

This is as far as I have gotten. I have modified the php script, but am unsure if I need to leave in the single quotes, and whether or not any changes need to be made if I'm accessing the DB without a password.

Any assistance is greatly appreciated.

Thanks to all who are working on this!

joey

you have to put ; at the end of SQL statement if you want to get results under psql.
Like this: select * from pg_stat_all_tables;

please execute and post result here or send me PM.

[jofficer]

OK, I got results like I would expect, but here's what I'm getting when attempting to run the script from the CLI:

Code: Select all

cacti scripts # php pgsql_stats.php db-2 index

Fatal error: Call to undefined function pg_connect() in /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php on line 216

I reviewed line 216

Code: Select all

$db_connection = pg_connect("host=$host dbname=$db_name user=$pg_username password=$pg_password");

I do not see a function defined with that name.

[_Dmitry_]

OK, I got results like I would expect, but here's what I'm getting when attempting to run the script from the CLI:

Code: Select all

cacti scripts # php pgsql_stats.php db-2 index

Fatal error: Call to undefined function pg_connect() in /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php on line 216

I reviewed line 216

Code: Select all

$db_connection = pg_connect("host=$host dbname=$db_name user=$pg_username password=$pg_password");

I do not see a function defined with that name.

You have to install php-pgsql package
Afterward you shouldn't have any problems.

[jofficer]

OK, after a world rebuild to include the correct use flags (gentoo), I think I'm making progress.

I had to make a couple of changes to the script, but I believe I'm getting what I want.

Code: Select all

cacti scripts # php pgsql_stats.php db-2 index
test

I had to change a few lines, as I noticed that 'postgres' was defaulting a 3 seperate spots, so I defaulted them to 'test' in order to test my connection.

Based on the command above, am I seeing what I should be seeing?

[_Dmitry_]

OK, after a world rebuild to include the correct use flags (gentoo), I think I'm making progress.

I had to make a couple of changes to the script, but I believe I'm getting what I want.

Code: Select all

cacti scripts # php pgsql_stats.php db-2 index
test

I had to change a few lines, as I noticed that 'postgres' was defaulting a 3 seperate spots, so I defaulted them to 'test' in order to test my connection.

Based on the command above, am I seeing what I should be seeing?

Yes, thats the correct output. But you shouldn't change script except username and password, otherwise I can't guarantee you would receive correct results.

Where you see 'postgres' it stays there for a reason.

[jofficer]

I returned the 'test' values to 'postgres' . I created my device, and this is the results of clicking on 'Verbose query'

Code: Select all

+ Running data query [13].
+ Found type = '4 '[script query].
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ XML file parsed ok.
+ Executing script for list of indexes '/usr/bin/php /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php db-2 index'
+ Executing script query '/usr/bin/php /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php db-2 query dbname'
+ Found item [pg_DBName=' no pg_hba.conf entry for host "192.168.11.58", user "monitor", database "postgres", SSL off in /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php on line 216'] index: Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL
+ Found item [pg_DBName=' No PostgreSQL link opened yet in /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php on line 253'] index: Warning: pg_last_error()
+ Found item [pg_DBName=' PostgreSQL connect failed. Check PostgreSQL parameters (db-2/username/password)'] index: Error
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'

I get a similar error if I run it from the CLI:

Code: Select all

cacti scripts # php pgsql_stats.php db-2 index

Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL:  no pg_hba..conf entry for host "192.168.11.58", user "monitor", database "postgres", SSL off in /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php on line 216

Warning: pg_last_error(): No PostgreSQL link opened yet in /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php on line 253
Error: PostgreSQL connect failed. Check PostgreSQL parameters (db-2/username/password)

Now, I had the DB admin grant 'monitor' access to postgres DB, and I'm back to getting the following:

Code: Select all

+ Running data query [13].
+ Found type = '4 '[script query].
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ XML file parsed ok.
+ Executing script for list of indexes '/usr/bin/php /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php db-2 index'
+ Executing script query '/usr/bin/php /usr/share/webapps/cacti/0.8.7a/htdocs/scripts/pgsql_stats.php db-2 query dbname'
+ Found item [pg_DBName='test'] index: test
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/webapps/cacti/0.8.7a/htdocs/resource/script_queries/postgres_dbstat.xml'

I'll let this run for a little bit, but I don't think I'm getting any results back. Is thereanother way to test the CLI to ensure that I'm getting data back from the host?

[jofficer]

Ok, I figured it out finally.

A couple of things to note. When setting up the device, be sure to select PING as the 'Downed Device Detection' method. Otherwise it thinks that the host is down and doesn't try to pull stats.

Gentoo users make sure to compile PHP with Postgres support, otherwise it won't work.

Seems to be working like a champ. Some graphs are still vacant, but being that it's a test DB it may very well have 0 data for those charts. I'll continue to monitor.

A request was made to provide number of connections. I didn't see that as an available graph, is that something that could be implemented?

[sashthebash]

Hi,

I tried to get this to run but ran into problems. First of all I manually had to change the pg_connect strings in the script to adapt to a non-default port of postgres. I also updated my cacti to 0.8.7.

I can now run the data query (verbose output):

Code: Select all

+ Running data query [10].
+ Found type = '4 '[script query].
+ Found data query XML file at '/usr/share/cacti/site/resource/script_queries/postgres_dbstat.xml'
+ XML file parsed ok.
+ Executing script for list of indexes '/usr/bin/php /usr/share/cacti/site/scripts/pgsql_stats.php localhost index'
+ Executing script query '/usr/bin/php /usr/share/cacti/site/scripts/pgsql_stats.php localhost query dbname'
+ Found item [pg_DBName='template_postgis'] index: template_postgis
+ Found item [pg_DBName='ruby'] index: ruby
+ Found item [pg_DBName='xxx_test'] index: xxx_test
+ Found item [pg_DBName='xxx_development'] index: xxx_development
+ Found data query XML file at '/usr/share/cacti/site/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/cacti/site/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/cacti/site/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/cacti/site/resource/script_queries/postgres_dbstat.xml'
+ Found data query XML file at '/usr/share/cacti/site/resource/script_queries/postgres_dbstat.xml'

When I click on Create Graphs for this Host I can create Graphs for each database that is on Postgres. Unfortunately only one graph per database, afterwards the entry is greyed out. I tried with the Database Size and DB Row Stats.

Both new datasources give me this error now:

Code: Select all

Notice:  Undefined index:   in /usr/share/cacti/site/lib/rrd.php on line 224

/usr/bin/rrdtool create \
/var/lib/cacti/rra/localhost_snmp_n_tup_del_55.rrd \
--step 300  \
DS:n_tup_ins:COUNTER:600:0:U \
DS:n_tup_del:COUNTER:600:0:U \
DS:n_tup_upd:COUNTER:600:0:U \
RRA::0.5:1:500 \
RRA:AVERAGE:0.5:24:2160 \
RRA:AVERAGE:0.5:1:51840 \
RRA:AVERAGE:0.5:6:8736 \
RRA:AVERAGE:0.5:288:797 \
RRA:MAX:0.5:24:2160 \
RRA:MAX:0.5:1:51840 \
RRA:MAX:0.5:6:8736 \
RRA:MAX:0.5:288:797 \
RRA:LAST:0.5:24:2160 \
RRA:LAST:0.5:1:51840 \
RRA:LAST:0.5:6:8736 \
RRA:LAST:0.5:288:797 \

The graphs are not drawn, what is this undefined index about?

Thanks for any help![/quote]

[sashthebash]

Found the problem myself: http://forums.cacti.net/post-122800.html&highlight=

[debel]

hi everyone,
i get an XML: Hash version does not exist
when i try to import cacti_data_query_postgresql_database_stats.xml

i'm running debian etch
my cacti version is 0.8.6i

[aimonb]

Hi Great work. The stats I am collecting for each DB are great. However, How do I check general stats about the postgresql server? Like total reads per second, total disk IO, etc. We have 50 DBs per server and I would like not to track each db separately but instead get cumulative data and general postgres server health data.

[omntidba]

[quote="_Dmitry_"]Hello,

recently I was inspired by [url=http://forums.cacti.net/viewtopic.php?t ... highlight=]MySQL Host Template [/url] 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)

[i]# - 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
[/i]

1. Create new User in PostgreSQL ,for example [b]monitor[/b] (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
[b]pgsql_stats.php[/b] - the Script, put this in <cacti_root>/scripts/
[b]postgres_dbstat.xml[/b] - the Definition, put this in <cacti_root>/resource/script_queries
[b]cacti_data_query_postgresql_database_stats.xml[/b] - Data Query + Graphs - import into Cacti

4. Edit [b]pgsql_stats.php[/b] 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 [b]PostgreSQL Database Stats[/b] in Data Queries.

6. Create your Graphs :)

Please let me know if you had any troubles or have any wishes :)[/quote]


would you please send me these scripts?

[omntidba]

Hello,

Would you please attach scripts to graph postgres stats in cacti?

thanks,
Denish

[jehan.procaccia]

when I try to import the template from cacti_data_query_postgresql_database_stats.xml
I get:

Error: XML: Hash version does not exist.

what's wrong ? I run on cacti-0.8.6i-1.el4.rf .

thanks.

PS: BTW, should I download postgresql_database_stats_1.tgz (10.67KB) or
postgresql_database_stats.tgz 9.92KB) ?