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.

• Period of report is from the beginning of yesterday (00:00 a.m.) to the present.
• Report target is all the monitored instance.
• Report is output to the current directory.

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.

• In some distribution, HTTP Server's document root home directory is /home/user. You should confirm that .pgpass is not visible from web browser and check file's permission.
• If you didn't set default time zone at php.ini, pg_stats_reporter might not work well. Please set the default time zone correctly at php.ini.
• pg_stats_reporter execute rounding off fractions, therefore a number between -1 and 1 may be displayed zero.
• About section names we cannot use "/" in Linux OS and "\ / : * ? < > |" in Windows OS. If you use reports of pg_stats_reporter in both Linux OS and Windows OS, you must not use all of them in pg_stats_reporter.ini.
• Don't set "global setting" as a value for section name in pg_stats_reporter.ini. Because "global setting" is reserved keyword.

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.

• Create report about database log messages
• Items about alerts are added to report
• Items about Autoanalyze are added to report
• Items about cancellation of Autovacuum are added to report

See also

pg_statsinfo

Acknowledgments

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

• jQuery : http://jquery.com/
• jQuery UI : http://jqueryui.com/
• jquery-ui-timepicker-addon : http://trentrichardson.com/examples/timepicker/
• dygraphs JavaScript Visualization Library : http://dygraphs.com/
• jqPlot : http://www.jqplot.com/
• tablesorter : http://plugins.jquery.com/tablesorter/
• Superfish : http://users.tpg.com.au/j_birch/plugins/superfish/
• Smarty : http://www.smarty.net/

---

Top > pg_stats_reporter

Copyright (c) 2012,2014, NIPPON TELEGRAPH AND TELEPHONE CORPORATION