PGSnap

pgsnap is a PostgreSQL tool that mimics orasnap performance report tool for Oracle.

It's written in PHP but one uses it on the command line. Many HTML files, aka reports, are written on a folder. You only need a web browser to read the complete report.

Demos

Here are three examples of the output:

Download

pgsnap-0.3.2

Install

apt-get install php5-pgsql php5-cli

Usage

./pgsnap [OPTIONS]... [DBNAME]

General options

-d DBNAME

specify database name to connect to (default: your login name)

-o outputdir

specify directory name where the reports will be created (default: the database name followed by 'snap_', followed by the date and time)

--help

show this help, then exit

--version

output version information, then exit

Connection options

-h HOSTNAME

database server host or socket directory (default: "localhost")

-p PORT

database server port (default: "5432")

-U NAME

database user name (default: your login name)

-W

prompt for password

To Do

  • specific orasnap:
    • Tablespace : list default tablespace for each user
    • Disabled constraints : owner, table, constraint, type, status
    • FK without indexes : owner, constraint name, column name
    • cached tables (pg_buffercache)
    • Buffer Hit Ratio (Consistent Gets DB Blk Gets Physical Reads Hit Ratio)
  • HTML interface:
    • jQuery support : sort in tables, filtering
    • graph on database size and usage
    • graph on tablespace size
    • graph on table and index size and usage
  • pollux asks for:
    • limiting the schemas used with a command line option (to get pg_catalog out)
    • being able to get a report from a standard user (not a superuser)