Name
pg_reporter -- make HTML reports from PostgreSQL.
Synopsis
pg_reporter [OPTIONS] [TEMPLATE]
It make a HTML report with TEMPLATE from data in PostgreSQL database. pg_repoter supports the following modes. See also Description for details.
| Mode | Description |
|---|---|
| Initialization mode | Initialize a directory used by CGI mode and HTTPD mode. |
| Command line mode | Make a report with specified parameters. |
| CGI mode | Work as a CGI module to make reports. |
| HTTPD mode | Work as a HTTPD server to make reports. |
Here are sample reports:
Description
pg_reporter is a general purpose reporting tool for PostgreSQL. You can write your own template, and reports will be generated with the template. You only have to know SQL and HTML to write templates.
Attached templates make HTML reports for pg_statsinfo. They support navigation for the snapshot list in CGI or HTTPD modes, so you can get graphical reports only with a couple of mouse clicks.
Examples
Make a directory in the current working directory for reports.
$ pg_reporter --init=./htdocs
Run as a HTTPD mode.
$ pg_reporter -D ./htdocs
Access with a web browser to port 8080 on the localhost.
http://localhost:8080/
The HTTPD mode accepts URLs in the following forms:
http://host/id/template-name?parameters
- host : Hostname where pg_reporter is running
- id : Database ID to connect
- template-name : A template basename used to make a report
- parameters : Parameters as "keyword=value&..."
On the other hand, you can also make a report directly from command line with options. In the following example, connecting to stats database, using a template details.xml, and writing a report into details.html.
$ pg_reporter -d stats -t details.xml -o details.html -a "begin=1&end=10"
Options
pg_reporter accepts the following command line options.
Options for initialization mode
- -i DIR
--init=DIR - A path for a directory to be initialized.
Options for command line mode
- -t TEMPLATE
--template=TEMPLATE - A path for a template file to make a report
- -o OUTPUT
--output=OUTPUT - A path to write a report. The default is standard output.
- -s XSLT
--stylesheet=XSLT - A path for a XSLT file to make a report
- -a "key1=value1&key2=value2&..."
--arg="key1=value1&key2=value2&..." - A list of parameters to be passed to SQLs in the template.
Options for HTTPD mode
- -D DIR
--httpd-dir=DIR - A directory path for the root document tree
- -P PORT
--httpd-port=PORT - Port number (default: 8080)
- -L ADDRESS
--httpd-listen=ADDRESS - Addresses to listen (default: localhost). You can set the value to '*' to accept all connections.
Connection Options
Options to connect to servers.
-
-d dbname
--dbname dbname - Specifies the name of the repository database to be connected. If this is not specified, the database name is read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used.
- -h host
--host host - Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.
- -p port
--port port - Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.
- -U username
--username username - User name to connect as.
- -W
--password - Force pg_reporter to prompt for a password before connecting to a database.
- This option is never essential, since pg_reporter will automatically prompt for a password if the server demands password authentication. However, it will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.
Generic Options
- -e
--echo - Echo commands sent to server.
- -E
--elevel - Choose the output message level from DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. The default is INFO.
- --help
- Show usage of the program.
- --version
- Show the version number of the program.
Environment
-
PGDATABASE
PGHOST
PGPORT
PGUSER - Default connection parameters
This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Environment Variables).
Configuration
Connection information is described in .dbnames file.
- Syntax
- id host port database username [password]
- Details
- id
- Database ID to be looked up from URL
- host
- Hostname of the server
- port
- Port number of the server
- database
- The name of database
- usernname
- User name used to connect the database
- password
- Password to login.
- You can omit the parameter if password is not required.
- id
- Example
DB001 db1.example.com 5432 postgres postgres postgres DB002 db2.example.com 5432 stats postgres postgres
- Note
- One record cannot divided into two or more lines.
- A comment starts with #.
- Parameters must be separated with whitespaces or tabs.
- If the same id values are found, the first one will be used.
Restrictions
pg_reporter has the following restrictions and limitations.
- HTTPD mode is not well tested.
- It cannot handle high load and keep it in LAN for security reasons.
- CGI mode requries the reporting directory is in DOCUMENT_ROOT.
- You cannot put the reporting directory in sub directries.
Details
System
Here is a flow of pg_reporter. It is a single-threaded application.
Directory
Here is a contents of the directory made by initialization mode.
- css : A directory for cascaded style sheets
- files : A directory for general files
- js : A directory for general JavaScript
- template : A directory for templates
- xsl : A directory for general XSLT files
- .dbnames : The database connection information
Running mode
The running mode is selected with the following rule:
| Mode | Options |
|---|---|
| Initialization mode | --init |
| Command line mode | template file |
| CGI mode | no options |
| HTTPD mode | --httpd-dir |
Installation
Build
pg_reporter can be build with PGXS as same as standard contrib modules. NOTE: If you want build the pg_reporter with PostgreSQL souce-code, PostgreSQL have to be built with "--with-libxml".
$ cd pg_reporter $ make USE_PGXS=1 $ make USE_PGXS=1 install
Registoration to database
You don't have to run registoration script for pg_reporter. But if you will make a report for pg_statsinfo, you need to install pg_statsinfo.
Apache HTTP Server configuration
Those operations are required only if you will run in CGI mode. First, install Apache HTTP Server.
# rpm -ivh httpd-2.2.11-1.x86_64.rpm
Next, add the following lines in the configuration file for Apache HTTP Server.
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^/files$ /report [R,L,QSA]
RewriteRule ^/([0-9A-Za-z]+)/([^\./]+)$ \
/cgi-bin/pg_reporter?id=$1&template=$2 [R,L,QSA]
</IfModule>
<Directory "/var/www/html/report">
Options Indexes
</Directory>
Copy pg_reporter executable file to CGI directory for Apache HTTP Server. And initialize a directory for reporting.
$ cp $PGHOME/bin/pg_reporter /var/www/cgi-bin/ $ pg_reporter -i /var/www/html
Make a database connection information file.
$ vi /var/www/html/.dbnames
Run Apache HTTP Server.
# service httpd start
Requirements
- PostgreSQL version
- PostgreSQL 8.4, 9.0
- OS
- RHEL 5.3, CentOS 5.3, Windows XP
- HTTP Server
- Apache HTTP Server 2.2
- Web browser
- Firefox 3.5, 3.6
See also
pg_statsinfo, XML FunctionsAcknowledgment
Thanks for "HTML5.JP" (http://www.html5.jp/). pg_reporter uses JavaScript libraries provided from the site.