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.4.0

Install

To install pgsnap, you can untar the pgsnap tarball anywhere you want.

You'll need the CLI release of PHP. For example, on Debian/Ubuntu, you should have:

apt-get install php5-pgsql php5-cli

Flash graphics with Open Flash Chart
You can have flash graphics for databases' size, tablespaces's size and tables's size. To activate the graphs, you need to download Open Flash Chart PHP library from its website. Then, you have to extract four files: open_flash_chart_object.php, open-flash-chart.php, open-flash-chart.swf, and swfobject.js. pgsnap needs to find these files in the external directory.

Usage

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

General options

-a

,

--all

build a report for all databases on the PostgreSQL server

-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)

-S

,

--without-sysobjects

get report without system objects informations

--delete-if-exists

delete output directory if it already exists

--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

  • 0.5.0
    • add an argument to -a option... this argument is the complete path to a folder which will contain every reports and an index.html allowing to navigate from one to the other
    • add a --server-only option, to get a view of all DB, no specific DB objects
  • specific orasnap
    • FK without indexes : owner, constraint name, column name
    • Tablespace : list default tablespace for each user
    • Disabled constraints : owner, table, constraint, type, status
  • HTML interface
    • jQuery support: sort in tables, filtering
    • add a way to filter locks report (by dbname, by lock mode)