1. What is pg_stats_reporter?
  2. Summary
    1. Create reports by Web browser
    2. Create reports by command-line
  3. Install
    1. Requirement
    2. Install pg_stats_reporter
    3. Initial settings
    4. Install cheack
  4. Usage
    1. Usage of creating reports by Web browser
    2. Usage of creating reports by command-line
  5. Uninstall
  6. Configuration
  7. Restrictions and cautions
  8. FAQ
  9. Changes from pg_stats_reporter 1.0
  10. See also
  11. Acknowledgement

pg_stats_reporter 2.0

What is pg_stats_reporter?

pg_stats_reporter can make statistical and graphical reports by pg_statsinfo's(2.4 or later) statistical database(we call it 'snapshot'). Example of report is here

Summary

Pg_stats_reporter is web-based software and can make statistical and graphical reports by pg_statsinfo's snapshot which is statistic of PostgreSQL.

We can create reports in two ways which are under following.

Create reports by Web browser

This method can easy to create reports on Web browser, program executes on Apache HTTP Server.
If you access web server by Web browser, you can create reports by GUI on your Web browser window.

Create reports by command-line

This method can create reports on command-line.
This method doesn't need Apache HTTP Server. If you cannot use Apache HTTP Server or would like to create reports by crond in specific period, you had better select this method.

Install

Please download install package of pg_stats_reporter.
If you would like to install by source package, please read the INSTALL.en file which is included in source package.

Requirement

pg_statsinfo
version 2.4 or later
PHP
version 5.3.3 (It's included in rpm package of OS.)
OS
RHEL 5.9, RHEL 6.4
web browser
Internet Explorer : 9, 10
Firefox : 10.0.12, 21.0
Google Chrome : 27.0.1453.110 m
Safari (Windows) : 5.1.7
HTTP Server
Apache HTTP Server : 2.2.3 (It's included in RHEL 5.9), 2.2.15 (It's included in RHEL 6.4)
libraries (They are included in package of pg_stats_reporter.)
Resolution
1024x786(SXGA) or higher

How to install pg_stats_reporter?

Install rpm packages which has dependency by yum or rpm command. Install command example is under following.

$ su
/* Example of install command by yum. */
# yum install httpd php php-pgsql php-intl php-cli

/* Example of install command by rpm. */
# rpm -ivh httpd-2.2.15-15.el6_2.1.x86_64.rpm \
           php-common-5.3.3-3.el6_2.8.x86_64.rpm \
           php-5.3.3-3.el6_2.8.x86_64.rpm \
           php-pdo-5.3.3-22.el6.x86_64.rpm \
           php-pgsql-5.3.3-3.el6_2.8.x86_64.rpm \
           php-intl-5.3.3-3.el6_2.8.x86_64.rpm \
           php-cli-5.3.3-22.el6.x86_64.rpm

[caution1] php-intl rpm file is included under following directory in OS install disk 2.
  Packages/php-intl-5.3.3-22.el6.x86_64.rpm
[caution2] It can work without php-intl rpm file. However, it doesn't work feature of auto language detection.

Install pg_stats_reporter by rpm command. Install example is under following.

# rpm -ivh pg_stats_reporter-2.0.0-1.el6.noarch.rpm

Under following install example, it only uses feature of createing reports by command-line.

$ su
/* Example of install command by yum. */
# yum install php-pgsql php-intl php-cli

/* Example of install command by rpm. */
# rpm -ivh php-common-5.3.3-3.el6_2.8.x86_64.rpm \
           php-pdo-5.3.3-22.el6.x86_64.rpm \
           php-pgsql-5.3.3-3.el6_2.8.x86_64.rpm \
           php-intl-5.3.3-3.el6_2.8.x86_64.rpm \
           php-cli-5.3.3-22.el6.x86_64.rpm

[caution1] php-intl rpm file is included under following directory in OS install disk 2.
  Packages/php-intl-5.3.3-22.el6.x86_64.rpm
[caution2] It can work without php-intl rpm file. However, it doesn't work feature of auto language detection.

Install pg_stats_reporter by rpm command. Install example is under following.

# rpm -ivh --nodeps pg_stats_reporter-2.0.0-1.el6.noarch.rpm

Initial Setting

Configuration of repository database

Edit configuration file for connecting repository database. Configuration method is here.
And sample of configuration is here.

Start HTTP Server

If you would like to use Web reporting feature, start HTTP Server.

# service httpd start

Install Check

How to confirm to work Web reporting feature collectly?

Check Usage of creating reports by Web browser and access HTTP server by your web browser. If you see report window of pg_stats_reporter, install is collectly finished.

How to confirm to work creating reports by command-line collectly?

Check Usage of creating reports by command-line and show snapshot list on conmmad-line. If you see snapshot list, instakk is collectly finished.

Usage

Pg_stats_reporter is visual and graphical report software for pg_statsinfo's statistical snapshot database.
You prepare installing pg_statsinfo before you use pg_stats_reporter.

Usage of creating reports by Web browser

You access pg_stats_reporter.php by web browser.
When you access your report which is created by pg_stats_reporter by web browser, example of reports URL is under following.

http://<hostname>/pg_stats_reporter/pg_stats_reporter.php

If you access above URL, you can see a pg_statsinfo's GUI.


Detail usage

Detail usage on window is under following.

report display

① : Button of create report
This button can create report and select term. More details is "dialog of creating report and selecting term"
② : Menu of repository databases
If you select a repository database in this menu, pg_stats_reporter displays management instances in this repository database.
③ : Menu of report instances
If you select a instance in this menu, pg_stats_reporter displays graphical reports in the instance.
④ : Botton of reload pg_stats_reporter.ini
If you push this button, pg_stats_reporter reload configuration file(pg_stats_reporter.ini) and create new report in latest setting.
⑤ : Menu of report items
If you select a item, pg_stats_reporter displays selective item in the report.
⑥ : Button of display or not left-side menu
If you push this button, left-side menu is displayed or not
⑦ : Help button
Display explanation which is how to see this table or graph.

Dialog of creating report and selecting term


⑧ : Text box of begginnig date of report
Input beginning date of report in this box. If you select this box, pop up a calender dialog.
⑨ : Text box of end date of report
Input end date of report in this box. If you select this box, pop up a calender dialog.
⑩ : Button of create report
Create report in your inputting report term
⑪ : Cancel button
Cancel for creating report

Usage of creating reports by command-line

Createing report

$ pg_stats_reporter [-R DBNAME] [-i INSTANCEID] [-a] [-O DIRECTORY] [-b SNAPID] [-e SNAPID] [-B DATE] [-E DATE]

Example of command is under following.
This sample of command shows creating report which is created from snapshot which is in repository database named sample, and it is default report setting.

When you create reports, HTML reports and directories which are JavaScript libraries and CSS files are created in report output directory.

$ pg_stats_reporter -R sample
-R, --repositorydb=DBNAME
Set repository database which you would like to create reports.
This option can be omitted. If you doesn't set this parameter, default setting sets top of repository database in your config file.
-i, --instid=INSTANCEID=DBNAME
Set the monitored instance which you would like to create reports.
This option can be omitted. If you doesn't set this parameter, default setting sets all of monitored instance in your repository database.
-a, --all
Show all reports item, if we set this option.
-O, --outputdir=DIRECTORY
Set reports output directory
This option can be omitted. If you doesn't set this parameter, default setting outputs at current directory.
If you select unexists directory, it creates new directory.
-b, --beginid=SNAPID
Set snapshot id which is begining of snapshot you would like to create report.
This option can be omitted. If you doesn't set this parameter, default setting sets start of the snapshot in the monitored instance. (*1) (*2)
-e, --endid=SNAPID
Set snapshot id which is end of snapshot you would like to create report.
This option can be omitted. If you doesn't set this parameter, default setting sets end of the snapshot in the monitored instance. (*1) (*2)
-B, --begindate=DATE
Set date which is beginning of snapshot you would like to create report.(YYYY-MM-DD HH:MI:SS format)
This option can be omitted. If you doesn't set this parameter, default setting sets start of snapshot in the monitored instance. (*1) (*2)
-E, --enddate=DATE
Set date which is end of snapshot you would like to create report.(YYYY-MM-DD HH:MI:SS format)
This option can be omitted. If you doesn't set this parameter, default setting sets end of the snapshot in the monitored instance. (*1) (*2)

*1: Can not set snapshot id and date in a same.
*2: If you ommit period of snapshot, default setting sets from 0:00 a.m. of the day before to the present.

List of snapshot

$ pg_stats_reporter -l [-R DBNAME] [-i INSTANCEID]

Sample command is under following.
Under following command example shows list of snapshots stored in sample database in the repository database.

$ pg_stats_reporter -l -R sample
-l, --list
Show list of snapshots, if you select this option.
-R, --repositorydb=DBNAME
Set repository database which you would like to see list of snapshots.
This option can be omitted. If you doesn't set this parameter, default setting is top og repository database in config file.
-i, --instid=INSTANCEID
Set instance id which you would like to see list of snapshots.
This option can be omitted. If you doesn't set this parameter, default setting is showing all of the monitored instance.

Show list of moritored instances

$ pg_stats_reporter -L [-R DBNAME]

Sample command is under following.
Under following command sample shows list of monitored instances submitted in "sample" database in repository database.

$ pg_stats_reporter -L -R sample
-L, --dblist
Select this option, show list of monitored instances.
-R, --repositorydb=DBNAME
Set repository database which you would like to see list of monitored instances.
This option can be omitted. If you don't set this parameter, default setting is top of repository database in your config file.

Show snapshots size

$ pg_stats_reporter -s [-R DBNAME]

Sample command is under following. The command example shows snapshots size in "sample" database stored in repository database.

$ pg_stats_reporter -s -R sample
-s, --size
Show snapshot size, if you use this option.
-R, --repositorydb=DBNAME
Set repository database name which you want to confirm snapshot size
This option can be omitted. If you don't set this parameter, default setting is top of repository database in your config file.

Create an index of reports

$ pg_stats_reporter --index [-O DIRECTORY]

Sample command is under following. In this sample command, create an index of report in /var/report directory.

$ pg_stats_reporter --index -O /var/report
--index
Create an index of reports, If you set this option and execute, create only an index of report not reports.
-O, --outputdir=DIRECTORY
Set output directory for created reports
This option can be omitted. If you don't set this parameter, default setting is current directory.

Uninstall

If you use rpm package, please command under following.
If you use source package, please see INSTALL.en for details.

$ su
# rpm -e pg_stats_reporter-2.0.0-1.el6.noarch

Configuration

Configuration file of pg_stats_reporter is "pg_stats_reporter.ini" in /etc directory.
Configuration file is composed mainly two sections which are global setting and repository database setting.

Global setting

Global setting is "[global_setting]" of section name.
If you install pg_stats_reporter from rpm package, you don't need to set global setting
If you install it only from source, set global setting. More detail is written in INSTALL.ja which is included source package.

Setting of repository database

Please set one setting section by one repository database server; Global settig section is repository database setting section.
The section name in repository database setting is be repository database name.

Configuration of repository database is under following.
Sample configuration is here

parameter kind of parameter Not set parameter Default setting example description
[repository database] character not omission not omission [repository1] Repository name at left-side menu in pg_stats_reporter.
host host name or IP address (*1) NULL host = localhost Host name or IP address of repository database.
port port number (*1) NULL port = 5432 Port number of repository database cluster.
dbname character (*1) (*1) dbname = postgres repository database name.Please set repository database in pg_statsinfo(Defalut repository database is 'postgres'.).
username char (*1) (*1) username = postgres connect user for repository database. Please set user which has read permission in repository database.
password char no password no password password = hogehoge connect password for repository database. If you use .pgpass, do not set this parameter.
language auto/ja/en auto en language = ja language in reports. If you set auto, we use browser information for language setting. (*2)
report item settings
(summary ~ profiles)
true/false true false summary = true If you set true, this report item is created.

(*1): If you do not set this parameter, please see PostgreSQL document.
(*2): If it didn't install php-intl package, it will show messages in English.

Cautions and restrictions

pg_stats_reporter has cautions and restrictions under following. Please read it before you use.

FAQ

Q1. Pg_stats_reporter does not display in my web browser.

A1. You had better review setting which are firewall(iptables etc) and SE Linux.

Q2. error massage which is database connection error is displayed in my browser. What should I do?

A2. You had better review under following.

Q3. I use Internet Explorer browser. But I think that createing report and display may not correctly.

A3. Internet Explore is not recommended browser. We recommend Firefox.

Q4. Does pg_stats_reporter supoort Apache HTTP Server's worker mode?

A4. It doesn't support it, because PHP package is recommend to use in prefork mode.

Changes from pg_stats_reporter 1.0

Change points from pg_stats_reporter 1.0 is under following.


See also

pg_statsinfo

Acknowledgements

Pg_stats_reporter use under following libraries. Thank you for developper of libraries