1. What is pg_stats_reporter?
  2. Online and Offline Reports
  3. Installation
    1. Environment
    2. Installing pg_stats_reporter
    3. Initial Setup
    4. Checking of Installation
  4. Usage
    1. Accessing Online Report
    2. Generating Offline Report
  5. Uninstallation
  6. Configuration
  7. Cautions and Restrictions
  8. FAQ
  9. Changes from pg_stats_reporter 3.0
  10. See also
  11. Acknowledgments

pg_stats_reporter 3.1

What is pg_stats_reporter?

pg_stats_reporter is a graphical report generator for pg_statsinfo. It offers you various statistical information collected by pg_statsinfo in a graphical and interactive representation. pg_stats_reporter 3.1 is compatible with pg_statsinfo 2.5 or later but some features are not available for the versions earlier than 3.1. You can see an example report here.

Online and Offline Reports

pg_stats_reporter runs in two ways. It runs as a CGI script and connects to repository server of pg_statsinfo in common usage but it also provides a command-line interface to generate off-line reports, which is portable form of the graphical report.

Installation

RPM package is available here.
If you would like to install from source, you will see instructions in INSTALL.en file in the top of source tree.

Environment

pg_stats_reporter is tested on the following environment.
pg_statsinfo
version 2.5, 3.0, 3.1
PHP
version 5.3.3 (bundled with RHEL6), 5.4.16 (bundled with RHEL7), 5.5.22
OS
RHEL 6.6, 7.1
Web browser
Firefox : 37.0
Google Chrome : 42.0
HTTP Server
Apache HTTP Server : 2.2.15 (bundled with RHEL6), 2.4.6 (bundled with RHEL7)
libraries (These packages are included in the package of pg_stats_reporter)
Minimum display resolution
1024x786(SXGA)

Installing pg_stats_reporter

Full Installation

If you would like to use both of online and offline reports. You should install all files required by both of CGI and command line part of pg_stats_reporter. Following the steps below as a superuser will do that. pg_stas_reporter is distributed as an RPM package but subsidiary packages can be installed using yum.

Installing using yum

The required packages are are installed by the steps like following using yum.

$ su
# yum install httpd php php-pgsql php-intl php-cli
# rpm -ivh pg_stats_reporter-3.1.0-1.el6.noarch.rpm
Installing using RPM

The following steps shows how to install all required packages using rpm command.

$ su
# rpm -ivh httpd-2.2.15-39.el6.x86_64.rpm \
           php-common-5.3.3-38.el6.x86_64.rpm \
           php-5.3.3-38.el6.x86_64.rpm \
           php-pdo-5.3.3-38.el6.x86_64.rpm \
           php-pgsql-5.3.3-38.el6.x86_64.rpm \
           php-intl-5.3.3-38.el6.x86_64.rpm \
           php-cli-5.3.3-38.el6.x86_64.rpm \
           pg_stats_reporter-3.1.0-1.el6.noarch.rpm

Installing for offline reports only

If you don't need online reports, some of the packages are not required.

Installing using yum

Required packages for offline reports are installed by the steps like following. pg_stats_reporter package depends on php so --nodeps needed for this case.

$ su
# yum install php-pgsql php-intl php-cli
# rpm -ivh --nodeps pg_stats_reporter-3.1.0-1.el6.noarch.rpm
Installing using RPM

The fewer packages are required. --nodeps is required for pg_stats_reporter.

# rpm -ivh php-common-5.3.3-38.el6.x86_64.rpm \
           php-pdo-5.3.3-38.el6.x86_64.rpm \
           php-pgsql-5.3.3-38.el6.x86_64.rpm \
           php-intl-5.3.3-38.el6.x86_64.rpm \
           php-cli-5.3.3-38.el6.x86_64.rpm
# rpm -ivh --nodeps pg_stats_reporter-3.1.0-1.el6.noarch.rpm

* You will find the "php-intl" package in the following directory on the installation disc No. 2 of RHEL.

"Packages/php-intl-5.3.3-38.el6.x86_64.rpm"

* The package "php-intl" is not required if you don't want automatic language detection for online report to work.


Initial Setup

This section explains about required setups to run pg_stats_reporter.

Set up for the repository server to connect

Edit some fields in the configuration file so as to connect the repository database. Configuration parameters are described later. Sample configuration set up to connect to repository on local server is included in package.

Start HTTP Service

Start the HTTP Service when you use online reporting. You may also have to set up firewall and SELinux properly to allow pg_stats_reporter to work.

# service httpd start

On RHEL7 do the following instead.

# systemctl start http.service

If you would like to run HTTP server at the next time you boot the system, run the command below.

# chkconfig httpd on

On RHEL7,

# systemctl enable http.service

Checking of installation

pg_stats_reporter works retrieving data from repository database of pg_statsinfo. Please check if it is working beforehand.

You can confirm that pg_stats_reporter is properly installed by the following steps. The repository server should have some content to show.

Checking whether online reporting works correctly

See Accessing online report and go to the URL as explained there. The browser will come up with proper content if it is working well.

Checking whether offline (or command-line) reporting works correctly

See Generating offline reports and list available snapshots on command-line. The command gives some snapshot IDs if it is working well.

Usage

This section explain about the screen of online reports.

Accessing Online Report

Entering the URL like following into address bar on your browser, you will see initial screen like the next section.

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

Operation details

Operations on GUI in detail are following.

Report screen

① : Create new report button
Shows a popup to specify the period for new report. Details are shown in "Create new report" dialog section.
② : Repository bar
One foldable list for one repository. Clicking the repository name toggles the instance (server) list inside to be expanded or collapsed.
③ : Instance list
List of instances that the repository stores their statistics. Clicking an item selects it as the current instance for reporting.
④ : Reload config
Tell pg_stats_reporter to reload the configuration file. If you made some changes in pg_stats_reporter.ini file, clicking this button makes the changes effective in the new report.
⑤ : Menu
Jumps directly to the item selected in this page.
⑥ : Go to top button
Jumps to the top of this page.
⑦ : Toggle sidebar button
Shows or hides the sidebar alternatingly.
⑧ : Help button
Shows help popup.

The "Create new report" dialog

⑨ : Beginning of the report period
This field specifies the beginning date and time of the report period. You can fill it immediately or clicking it shows calendar widget to select date and time.
⑩ : End of the report period
This field specifies the ending date and time of the report period. Calendar widget is also available for this field.
⑪ : Create report
Finishes entering the period and make the new report.
⑫ : Cancel
Cancels entering the period and close this dialog.
Log viewer

Different from other items, log viewer is shown in a dedicated frame.


① : Search Option button
Toggles folding state of the search option section just below.
② : Filter condition fields
You can filter out unnecessary logs from the table below by filling these fields and click the "Search" button below.
③ : Search button
Start searching the repository for the filtered logs.
④ : Clear button
Empty all fields in the Search Option section.
⑤ : Column selector
Shows a dialog to select columns to be displayed in the result table.
⑥ : Filter Reset button
Clear all quick filter conditions.
⑦ : Page move buttons
Moves to the next or previous page.
⑧ : Quick filter
Quick filter for each column. Text fields accepts the regular expressions for tablesorter.
⑨ : Help button
Shows some help on this page.

Generating Offline Reports

Generating Reports

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

The very simple example below generates an offline report from snapshots in repository database named sample with default settings as following.

$ mkdir report
$ cd report
$ pg_stats_reporter -R sample
$ ls
css         js       sample_localhost_5432_1_20150528-0000_20150529-1650.html
index.html  package
-R, --repositorydb=DBNAME
Make a report connecting to the repository database with DBNAME. Default is the first reporsitory in configuration file.
-i, --instid=INSTANCEID
Make a report only for the target instance of INSTANCEID. All instances stored in the repository database by default.
-a, --all
Emit all report items ignoring default setting.
-O, --outputdir=DIRECTORY
Place output files in the directory DIRECTORY. The default is to put in the current directory. If the directory does not exist, it will be created.
-b, --beginid=SNAPID
Make a report for the period begins with the snapshot of SNAPID. The default behavior of this option is described below.
-e, --endid=SNAPID
Make a report for the period ends with the snapshot of SNAPID. The default behavior of this option is described at the end of this section.
-B, --begindate=DATE
Make a report for the period begins with DATE, in the format of "YYYY-MM-DD HH:MI:SS". The default behavior of this option is described at the end of this section.
-E, --enddate=DATE
make a report for the period ends with DATE, in the format of "YYYY-MM-DD HH:MI:SS". The default behavior of this option is described at the end of this section.

  1. Date specifications and Snapshot ID specifications are exclusive.
  2. If you specify only one end of report period, the opposite end is assumed to be the first, or the last of all snapshots.
  3. If both ends are omitted, the default report period begins yesterday midnight and lasts until now.

Listing snapshots

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

The option "-l" instructs to do this. The following example lists all snapshots stored in the repository database "sample".

$ pg_stats_reporter -l -R sample
-l, --list
Show the list of snapshots.
-R, --repositorydb=DBNAME
List snapshots in the repository database DBNAME. Default is the first repository in configuration file.
-i, --instid=INSTANCEID
List snapshots of the target instance of INSTANCEID. All instances stored in the repository database by default.

Listing target instances

$ pg_stats_reporter -L [-R DBNAME]

The option "-L" instructs to do this. The following example command shows the list of the all target instances stored in the repository "sample".

$ pg_stats_reporter -L -R sample
-L, --dblist
List all of the target instances.
-R, --repositorydb=DBNAME
List snapshots in the repository database DBNAME. Default is the first repository in configuration file.

Show snapshot size

$ pg_stats_reporter -s [-R DBNAME]

The options "-s" instructs to do this. The example command below shows the amount of disk space consumed by the snapshots in the repository database "sample".

$ pg_stats_reporter -s -R sample
-s, --size
Show amount of disk space used by snapshots.
-R, --repositorydb=DBNAME
List snapshots in the repository database DBNAME. Default is the first repository in configuration file.

Recreate index page

$ pg_stats_reporter --index [-O DIRECTORY]

The options "--index" instructs to do this. The example command below creates the index page of the reports stored in /var/report. Doing this after you have deleted some reports updates the index page so as to display the content correctly.

$ pg_stats_reporter --index -O /var/report
--index
Recreate index page of existing reports.
-O, --outputdir=DIRECTORY
Make index page for directory DIRECTORY, defaults to the current directory.

Uninstallation

pg_stats_reporter installed by RPM package is uninstalled by the following steps. The configuration file ('/etc/pg_stats_repoter.ini') is left alone by the steps, so you should delete it manually if it is not necessary.

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

You will see the instructions to uninstall manually in INSTALL.en in the top source directory.

Configuration File

All settings for pg_stats_reporter are described in the configuration file "pg_stats_reporter.ini" in /etc directory, which is composed of two main sections, global settings and per-repository database settings.

Global settings

The section marker "[global_setting]" indicates the beginning of the global settings. If you installed following instructions, you don't need to change the settings here from the sample configuration file.

Parameter Type of value Default value Example Description
install_directory string mandatory install_directory = /var/www Installation directory of this tool
log_page_size 1 - 1000 1000 log_page_size = 1000 Maximum number of rows of the log viewer table

Per-repository settings

Every repository requires one section describing settings about it. The section tag is the display name in the sidebar for the repository.

Available setting parameters are shown below.

Parameter Type of value Default value Example Description
[repository name] character mandatory [repository 1] Section marker for this repository. The name is displayed as repository name in the sidebar of web interface.
host hostname or IP address (*a) host = localhost Hostname or IP address of the repository server.
port port number (*a) port = 5432 Port number to connect to repository server.
dbname string (*a) dbname = postgres Name of the repository database.
username string (*a) username = postgres Name of the User to connect to the repository database.
password string no password password = foo-bar-baz Password to connect to the repository server. Leave unspecified when .pgpass is used.
language auto/ja/en auto language = ja Language for web interface. auto means that the language will be determined according to the browser's request. Assumed to be "en" if php-intl is not installed.
Report items selection true/false true summary = true Specifies whether to show or not for each report item. All report items should have their own settings. They are prewritten in the sample configuration file.

  1. These settings follow PostgreSQL settings as default.

Cautions and Restrictions

This section provides important information that can affect stability and information about what might be confusing. Be sure to read them before use.

FAQ

Q1. I can only see a blank page on browser when accessing online report.

A1. It might be caused by settings of firewall, SELinux or memory usage limitation of PHP. They might be changed.

Q2. pg_stats_reporter shows an error dialog of connection failure to repository at the first connection.

A2. Repository connection settings in pg_stats_reporter.ini or access control setting of the repository database might be wrong. Make sure that they are correct.

Q3. My Internext Explorer displays the report but it looks broken.

A3. Use Firefox or Chrome instead. Internet Explorer is not recommended.

Q4. Can I run pg_stats_reporter on worker mode of Apache?

A4. Unfortunately, PHP is not recommended to be used on multithreaded MPM and we tested pg_stats_reporter only on prefork mode.

Q5. I see the name of a no-longer-monitored database in the repository bar.

A5. It is brought about by the data for such instances left in the repository. You can remove such items by manually deleting the corresponding data in repository. See details here.

Changes from pg_stats_reporter 3.0

Changes from pg_stats_reporter 3.0 are shown below.


See also

pg_statsinfo

Acknowledgments

Pg_stats_reporter uses many libraries. We are deeply grateful to the developers of these libraries.