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:
LOCAL0.* -/var/log/pgsql
in the catch all log files area, add:
LOCAL0.none
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.)
restart syslog:
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.
Enjoy.
I used pgFouine with PostgreSQL 8.3 under Debian without problems. For my Postgresql 8.4 under Ubuntu 10.10 i changed
redirect_stderr = on
to
logging_collector = on
other settings are:
log_destination = ‘stderr’
logging_collector = on
log_duration = off
log_statement = ‘none’
log_min_duration_statement = 0
When i use any SQL statement e.g.:
postgres psql -lt
it logs the duration together with the stament on stderr. In 8.3 this was only in the logfile.
LOG: duration: 1.672 ms statement: SELECT d.datname as “Name”,
pg_catalog.pg_get_userbyid(d.datdba) as “Owner”,
pg_catalog.pg_encoding_to_char(d.encoding) as “Encoding”,
d.datcollate as “Collation”,
d.datctype as “Ctype”,
pg_catalog.array_to_string(d.datacl, E’\n’) AS “Access privileges”
FROM pg_catalog.pg_database d
ORDER BY 1;
The problem is that it appears in psql interactive mode during tab completion:
postgres=# select * from pg_LOG: duration: 4.161 ms statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN (‘r’, ‘S’, ‘v’) AND substring(pg_catalog.quote_ident(c.relname),1,3)=’pg_’ AND pg_catalog.pg_table_is_visible(c.oid)
UNION
SELECT pg_catalog.quote_ident(n.nspname) || ‘.’ FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || ‘.’,1,3)=’pg_’ AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || ‘.’,1,3) = substring(‘pg_’,1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || ‘.’ || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN (‘r’, ‘S’, ‘v’) AND substring(pg_catalog.quote_ident(n.nspname) || ‘.’ || pg_catalog.quote_ident(c.relname),1,3)=’pg_’ AND substring(pg_catalog.quote_ident(n.nspname) || ‘.’,1,3) = substring(‘pg_’,1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || ‘.’,1,3) = substring(‘pg_’,1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000
Even using syslog as you described did not fix the problem.
Can you help me?
I found the solution. A parameter client_min_messages was wrong. Sorry.