Pggraph

Remote Datacollector PGGraph Variant - Jun 17, 2008 - Aurynn Shaw - ashaw@commandprompt.com

Premise

PGGraph is a graphing web application built around Postgres' standard statistics gathering, applying bright and shiny visualization around the data.

The standard mechanism of use for PGGraph has, historically, been the "Pull" model, wherein data gathering software has run on our hosting server, cmdalert, and gathered data on demand for use in the graphing end of things.

This has a great many difficulties, the most prominent being that the SSH tunnels that link cmdalert to the client systems are VERY fragile. This would not be a problem, excepting pggraph functions entirely over SSH tunnels.

Superior Mechanisms

The data collection aspect of PGGraph, bridge.py, doesn't really care where it lives. It can be on, literally, any system in the world and correctly connect to both the collection and storage systems and operate correctly.

In this instance, to bypass the issues involved with maintaining SSH tunnels, the data collector script is now being moved to the target, collection system.

As we control the storage server (cmdalert), we can enforce SSL connections, access rights, and provide an easy interface for our data collector to talk to from anywhere in the world.

Installation

Installation of this pggraph variant is somewhat more complex than the "classic" pggraph2, due in part to the present lack of comprehensive installation tools (this will be rectified). As such, the installation overview is such:

• upload /var/www/pggraph/_main_remote/bridge.py to the customer server
• Modify config.ini, and upload it to the customer server
• run install_2.py <schema> <prefix>
• Create a user and appropriate grants
• Add the user and customer server to pg_hba.conf
• run install_dir.py, answer the questions (identical to previous installer)
• SSH to the customer server and run bridge.py /path/to/config
• Add a cronjob on the customer server, if bridge.py exhibited no problems.
• Set up the mod_auth_pgsql on the cmdalert server, to provide lockdown for pggraph users

As you can see, setup of the new variant is somewhat more complicated than the initial version of pggraph2.
This deficiency will be rectified as time progresses and installation tools are properly developed.

More detailed installation

=== Upload bridge file, and config file ===

This is as it says, merely uploading the /var/www/pggraph/_main_remote/bridge.py and an edited config file to the customers' server.
I generally place these files in ~/pggraph.

The config file is documented on what is needed, and where, and should be easily modified to any given client.

=== install_2.py ===

install_2.py is just the database-structure creation from the original install.py. It takes two arguments, schema and prefix.

As the new design assumes that all new users will be schema-separated, this should be similar to the prefix, for correct separation of namespaces.
This excepts the event of customers such as MXLogix or Sebit, where multiple databases will be tracked under the same schema namespace. In this case, the prefix should be stable (mxlogic, sebit) while the prefix changes to denote which database system it refers to.

install_2.py will be upgraded to also take user & password information (to create the appropriate user and password), and will apply the appropriate GRANT and REVOKE statements to the user.