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 check
  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 2.0
  10. See also
  11. Acknowledgment

pg_stats_reporter 3.0

What is pg_stats_reporter?

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

Summary

Pg_stats_reporter is a web-based software that makes statistical and graphical reports based on pg_statsinfo's snapshot which is derived from PostgreSQL.

We can create reports in two ways following.

Create reports by Web browser

This method can easily create reports on the Web browser by the pg_stats_reporter program that executes on Apache HTTP Server.
If you access web server with a web browser, you can get reports on the browser that includes two kinds of reports following.

Performance report
This report helps you know the performance statistics on the database you specify and resource usages derived from the OS on which the database run.
Log report
This report helps you know the log messages issued by the database you specify and alerts detected by the pg_statsinfo.

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 periodically by crond, you had better use this method.

Install

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

Requirement

pg_statsinfo
version 2.4 or later
PHP
version 5.3.3 (It's included in rpm package of OS.), 5.5.12
OS
RHEL 6.5
Web browser
Firefox : 28.0
Google Chrome : 33.0
HTTP Server
Apache HTTP Server : 2.2.15 (It's included in RHEL 6.5)
libraries (They are included in package of pg_stats_reporter.)
  • jQuery : 2.0.1
  • jQuery UI : 1.10.2
  • jquery-ui-timepicker-addon : 1.3
  • dygraphs JavaScript Visualization Library : b839102723 (commit ID)
  • jqPlot : 1.0.8 r1250
  • tablesorter : 2.15.5
  • Superfish : 1.7.2
  • Smarty : 3.1.13
Resolution
1024x786(SXGA) or higher

How to install pg_stats_reporter?

Full Installation

If you would like to install all the functionality of pg_stats_reporter, you should do things below as a superuser. You can install by yum command or rpm packages.

Installation by yum

Install pg_stats_reporter by yum command. Install example is as follows.

$ su
/* Example of install command by yum. */
# yum install httpd php php-pgsql php-intl php-cli
# rpm -ivh pg_stats_reporter-3.0.0-1.el6.noarch.rpm
Installation by RPM

Install pg_stats_reporter with rpm command. Install example is as follows.

$ su
/* 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 \
           pg_stats_reporter-3.0.0-1.el6.noarch.rpm

Installation limited to command-line mode

If you would like to install only command-line mode functions, you should do things below as a superuser.

Installation by yum

Installation example pg_stats_reporter limited to command-line mode with yum command is shown below.

$ su
/* Example of install command by yum. */
# yum install php-pgsql php-intl php-cli
# rpm -ivh --nodeps pg_stats_reporter-3.0.0-1.el6.noarch.rpm
Installation by RPM

Installation example pg_stats_reporter limited to command-line mode with RPM is shown below.

/* 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 in the following directory
  of OS install disk No. 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.

Initial Setting

Configuration of repository database

Edit configuration file for connecting the repository database. Configuration method is explained later, and sample configuration is here.

Start repository database

After setting configuration, you should start a repository database.

Start HTTP Server

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

# service httpd start

If you would like to start the HTTP server at the system start, run the command below.

# chkconfig httpd on

Install Check

How to confirm that Web reporting feature works correctly

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

How to confirm that creating reports by command-line works correctly

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

Usage

Pg_stats_reporter visualizes database statistics collected by pg_statsinfo. So you need to install and run pg_statsinfo before you use pg_stats_reporter.

Usage of creating reports by Web browser

How to start creating reports

If you access the URL below, you can see an initial report from pg_stats_reporter and refine the report through a GUI controller.

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

Detailed operation

Detail operations on the web browser's GUI are followings.

report display

① : Create report
You can create a new report about specified term as it is explained in section "dialog for creating reports and selecting term"
② : Display or hide repository databases
If you select a repository database in this menu, the monitored database instances in this repository database are displayed or hidden.
③ : Instance selector
If you select an instance from this menu, pg_stats_reporter create reports about the instance.
④ : Reload pg_stats_reporter.ini
If you push this button, pg_stats_reporter reload configuration file (pg_stats_reporter.ini) and create new report along the latest settings.
⑤ : Item selector
If you select a report item such as Summary, SQL and others, pg_stats_reporter displays selected items in the report.
⑥ : Go to the top
If you push this button, the report is displayed from the top.
⑦ : Display or hide the left-side menu
If you push this button, left-side menu column is displayed or not.
⑧ : Help
Display simplified explanation how to see tables or graphs.

Dialog for creating reports and selecting term

⑨ : Entry for beginning date of the report
Input beginning date of the report in this box. If you click this entry, pop up a calendar dialog.
⑩ : Entry for ending date of the report
Input ending date of the report in this box. If you click this entry, pop up a calendar dialog.
⑪ : Create report
Create a report according to a specified term.
⑫ : Cancel button
Cancel creating a report.
log viewer

① : Display or hide the search option menu
If you push this button, search option is displayed or not.
② : Entries for the search conditions
You can specify the conditions to search the log records as follow.
  • ELEVEL:massage level
  • USERNAME:user name of the database
  • DATABASE:database name
  • MESSAGE:specify the pattern to match the message text as that of PostgreSQL.
③ : Search
You can search the log records that match the conditions you specify at serch conditions. (see ②)
④ : Clear the condition
You can clear the conditions in the entries.
⑤ : Column selector
Pushing the button, you can display the check boxes at each column which you set visible or invisible. To hide the check boxes, push the button again.
⑥ : Reset the table filter conditions
You can reset the filter conditions accompanied with each column on the table.
⑦ : Page selector
You can see the next or previous page.
⑧ : Table filter conditions
You can select rows that meet the condition you specify. The conditions can be expressed same as those of tablesorter.
⑨ : Help button
You can get help messages.

Usage of creating reports by command-line

Creating report

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

A command example is shown below. This command creates a report which is derived from a snapshot in repository database named sample, and the report is created by default settings shown below.

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

$ pg_stats_reporter -R sample
-R, --repositorydb=DBNAME
Set the repository database on which you would like to create reports.
This option can be omitted. If you don't set this parameter, the top of the repository database in your config file is used by default.
-i, --instid=INSTANCEID
Set the monitored instance on which you would like to create reports.
This option can be omitted. If you don't set this parameter, all of the monitored instances stored in your repository database are reported by default.
-a, --all
If we set this option, all report items are shown.
-O, --outputdir=DIRECTORY
Set reports output directory.
This option can be omitted. If you don't set this option, the report is put in the current directory by default. If you specify a nonexistent directory, it creates the new directory with the specifed name.
-b, --beginid=SNAPID
Set snapshot id which is beginning of snapshot you would like to create report.
This option can be omitted. If you don't set this parameter, default setting sets start of the snapshot in the monitored instance. (*a) (*b)
-e, --endid=SNAPID
Set snapshot id which is end of snapshot you would like to create report.
This option can be omitted. If you don't set this parameter, default setting sets end of the snapshot in the monitored instance. (*a) (*b)
-B, --begindate=DATE
Setting this parameter with a data value (YYYY-MM-DD HH:MI:SS format), you can specify the earliest snapshot in the duration you would like to report.
This parameter is optional. If you omit setting this parameter, the earliest snapshot among the monitored instances should be used as a starting point of the report.(*a) (*b)
-E, --enddate=DATE
Setting this parameter with a data value (YYYY-MM-DD HH:MI:SS format), you can specify the latest snapshot in the duration you would like to report.
This parameter is optional. If you omit setting this parameter, the latest snapshot among the monitored instances should be used as a ending point of the report.(*a) (*b)

  1. Can not set both snapshot id and date at once.
  2. If you omit period of snapshot, default duration is from 0:00 a.m. of the day before to the present.

Show the list of snapshot

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

Sample command is following.
The following command example shows the list of snapshots stored in the database named 'sample' in the repository.

$ pg_stats_reporter -l -R sample
-l, --list
If you select this option, the list of snapshots are shown.
-R, --repositorydb=DBNAME
You can select the repository database, and the list of snapshots the database has is shown.
This parameter is optional. If you omit it, the first repository database appeared in the config file should be shown by default.
-i, --instid=INSTANCEID
You can select the id of monitored instance, and the list of snapshots the instance has is shown.
This parameter is optional. If you omit it, the all snapshots included all the monitored instances by default.

Show list of monitored instances

$ pg_stats_reporter -L [-R DBNAME]

Sample command is following.
The following sample command shows the list of the monitored instances stored in "sample" database in the repository database.

$ pg_stats_reporter -L -R sample
-L, --dblist
If you select this option, the list of the monitored instances should be shown.
-R, --repositorydb=DBNAME
You can select the id of the monitored instance, and the list of snapshots in the instances has is shown.
This parameter is optional. If you omit it, the all snapshots included all the monitored instances by default.

Show snapshot size

$ pg_stats_reporter -s [-R DBNAME]

Sample command is following. This shows the snapshot size of the "sample" database stored in the repository database.

$ pg_stats_reporter -s -R sample
-s, --size
Show snapshot size, if you use this option.
-R, --repositorydb=DBNAME
You can specify the repository database name which you would like to show the snapshot's size.
This parameter is optional. If you omit it, the first repository database size in the config file should be shown by default.

Create an index of reports

$ pg_stats_reporter --index [-O DIRECTORY]

Sample command is following. This sample command creates the index of the reports in the /var/report directory.

$ pg_stats_reporter --index -O /var/report
--index
If you set this option and execute, the index about reports should be created instead of a set of the reports.
-O, --outputdir=DIRECTORY
You can specify the output directory for the index created.
This is optional. If you omit this parameter, the index should be created in the current directory by default.

Uninstall

If you installed pg_stats_repoter by the RPM package, you can uninstall it as follows. The following uninstall command doesn't delete the configuration file ('/etc/pg_stats_repoter.ini'). If you would like to delete it, do manually.

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

On the other hand, if you installed it by the source package, please see the method explained in detail in INSTALL.en included with the package.

Configuration File

Configuration file for pg_stats_reporter is "pg_stats_reporter.ini" in /etc directory.
It is composed of two main sections. One is for global settings, and the other is repository database settings.

Global settings

Global settings should be specified in the "[global_setting]" section.
If you install pg_stats_reporter from RPM package, you don't need to set these values.
If you install the tool from the source package, set global setting values. The method is explained in detail in the file INSTALL.ja that is included with the source package.

parameters type of values default value sample description
install_directory string mandatory install_directory = /var/www set install directory of this tool(*a)
log_page_size 1 - 1000 1000 log_page_size = 1000 maximum number of rows of the log report table
  1. When you install pg_reporter from RPM package, this value is set automatically. Otherwise, you should set appropriate value.

Setting for repository databases

Please describe one setting section for one repository database server. The section name for a repository database settings sould be the repository database name.

Parameters for a repository database are shown below. And you can get the sample configuration file from here.

parameters type of values default value sample description
[repository database] character mandatory [repository1] The repository database should be specified.
host host name or IP address (*a) host = localhost The host name or IP address of the database server that the repository resides.
port port number (*a) port = 5432 The port number of the database server that the repository resides.
dbname string (*a) dbname = postgres The name of the repository database. You should specify the same name as that for pg_statsinfo (The repository database is stored in 'postgres' by default).
username string (*a) username = postgres The user name to connect to the repository database. Pay attention to the user's read permission to the repository database.
password string no password password = foo-bar-baz The password for connection to the repository database. If you use .pgpass, you should describe nothing about this parameter.
language auto/ja/en auto language = ja The language used in the reports. If you set auto, the language set by the browser should be used. (*b)
report items reported (*c) true/false true summary = true If you set a parameter for an item to true, the item should be occurred in the output report.

  1. If you do not set this parameter, please see PostgreSQL document.
  2. If you don't install php-intl package, messages will be shown in English.
  3. Various report items you can use are shown in the sample configuration file.

Cautions and restrictions

pg_stats_reporter has cautions and restrictions as followings. Please read them before you use.

FAQ

Q1. Pg_stats_reporters report can display reports in my web browser.

A1. You had better review settings such as firewalls (iptables etc.), SE Linux and so on.

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

A2. You had better review followings.
  • Setting of "database connection" in pg_stats_reporter.ini
  • Connection settings in PostgreSQL (postgresql.conf, pg_hba.conf)

Q3. I use Internet Explorer for a browser. But I think that the report is not displayed correctly.

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

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

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

Changes from pg_stats_reporter 2.0

Changes from pg_stats_reporter 2.0 are shown below.


See also

pg_statsinfo

Acknowledgments

Pg_stats_reporter uses libraries shown below. We are deeply graceful to the developers of the libraries.