PostgreStats Enterprise Installation Guide


1. Upload the PostgreStatsEnt install archive to your server
Enterprise was designed to be installed on any server in your network running Apache and having access to your database server(s). FTP/SCP the postgrestatsent.tar.gz install archive to your server's web directory. Unzip and extract the install folder by issuing the following command in the shell:

tar -zxf postgrestatsent.tar.gz
2. Verify PostgreSQL Stat Collector is running
If you know that the Stat Collector process is running already and collecting statistics on your database server(s), you can skip this step. In newer versions of PostgreSQL, the Stat Collector is on by default. For these versions, PostgreStats only requires the postgresql.conf line track_counts be set to "on", also the default. For older versions of Postgres, the following postgresql.conf file lines should be set to "on": stats_start_collector, stats_block_level and stats_row_level. If you've changed these values, first restart the PostgreSQL service. To verify the Stat Collector is collecting statistics, PSQL into any database in your implementation and issue this query:

SELECT * FROM pg_stat_database;
3. Set-up the Apache PostgreStats Enterprise Site
Set up a standard Apache website for PostgreStats Enterprise on your network. Once it's up and running, visit your PostgreStats Enterprise site where you'll first see the Set-up screen.
4. Creating the PostgreStats Database
The set-up screen will walk you through setting up the PostgreStats database which will house your statistics. It will also prompt you to configure your main conf file (ps_conf.php). Simply follow the online guide through each step.
5. First-time log-in
Once your database is set-up and your conf file has been updated with your new database credentials, on refresh of your PostgreStats site, you'll be prompted to log-in. In the event you missed this step on the set-up page, your default Enterprise site log-in credentials are:

USERNAME: postgrestats
PASSWORD: password
6. Configuring PostgreStats Enterprise
Once you are logged-in, click on the Servers and Databases link up top where on this screen, you'll first add servers to PostgreStats. For each server, you'll then add databases for which you'd like to collect statistics for. Note: A "Server" in PostgreStats Enterprise is a single PostgreSQL implementation with a unique port. EG: it is possible to set-up multiple "servers" with the same IP but having separate port numbers accepting connections for their respective PostgreSQL services.
7. Automate with Cron
Once your servers and databases are installed into Enterprise, all that is left is setting up the cron-job. The file to execute VIA cron, is ps_cron.php, located in the scripts directory of your Enterprise install. PostgreStats are updated each time ps_cron.php is executed. Enterprise was designed to collect statistics at higher frequencies or as often as you'd like (EG: once per hour) but stats should be updated at least once per day with the last execution as close to midnight as possible. We recommend you run this cron-job as root only. To install the cron-job, su to root, enter crontab edit mode by typing "crontab -e" and insert your new cron-job as in these examples:

55 23 * * * /usr/bin/php /path/to/postgrestatsent/scripts/ps_cron.php (Each night at 11:55pm)
50 * * * * /usr/bin/php /path/to/postgrestatsent/scripts/ps_cron.php (50 minutes past every hour)

NOTE: Be sure to update your path to PHP and your PostgreStats install directory path.
8. Locking Down Your Install
Once you have PostgreStats Enterprise customized and running smoothly on your server, we recommend you chown and chmod the main cron PHP script (ps_cron.php, located in the script directory of your install folder) to be owned by root with only root level privileges. To accomplish this, as root, CD to the scripts directory and issue the following two shell commands:

chown root ps_cron.php
chmod 700 ps_cron.php

This is just for added security and ensures that only root can access and execute the log update script. Lastly, because Enterprise has a full online GUI, we recommend only viewing your site using SSL to ensure all data, to and from is encrypted during transit.