The point of this is to get some nice statistics on the queries being run on your postgres db. For example, look at this sample report generated by pgFouine. If you want this, continue the tutorial.
First off, this tutorial assumes you have postgres installed and queries are being run in a database. I have version 8.4 setup on my Ubuntu Karmic box.
This tutorial is based off the pgFouine tutorial.
Go to your postgres.conf directory (mine was /etc/postgresql/8.4/main/postgres.conf). Change/uncomment the following parameters as such (mine didn’t have redirect_stderr param, so I didn’t set it–maybe I should include it?):
log_destination = 'syslog' redirect_stderr = off silent_mode = on log_min_duration_statement = 0 log_duration = off log_statement = 'none'
FYI log_min_duration_statement = 0 gets every query logged.
Now setup syslog by editing your syslog conf. On my box it was: sudo vim /etc/rsyslog.d/50-default.conf
Add an entry with like entries:
in the catch all log files area, add:
to the list of like commands (may have to include a semicolon)
(LOCAL0 is what “syslog_facility” is set to in you postgres.conf–these should match).
this tells syslog to log files to the FILE /var/log/pgsql (note that the settings in postgres.conf for log_directory and log_filename aren’t used for syslog logging.)
sudo /etc/init.d/rsyslog restart
download and extract pgfouine, cd into its directory and to generate a report type:
./pgfouine.php -file /path/to/logfile > myreport.html
This creates the report in the current directory and then u can access it in the browser.