Log all queries to a PostgreSQL server
|Debian (Etch, Lenny, Squeeze)|
|Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty, Precise, Trusty)|
To configure a PostgreSQL server to log the content of all queries.
Suppose that you have written a program that makes queries to a PostgreSQL database. Some of the queries are constructed programmatically, however they are not returning the expected results. You wish to check that the queries reaching the database server have the intended content.
Edit the main PostgreSQL configuration file,
postgresql.conf. On Debian-based systems this is located in
/etc/postgresql/8.3/main/ (replacing 8.3 with the version of PostgreSQL you are running), and on Red Hat-based systems in
/var/lib/pgsql/data/. Insert the lines:
log_statement = all log_min_error_statement = error
log_min_error_statement settings should be commented out or removed. On older releases of PostgreSQL (prior to version 8.0) replace ‘all’ with ‘true’:
log_statement = true log_min_error_statement = error
Restart the PostgreSQL server process, using the
service command if this is available on your system:
service postgresql-8.3 restart
or if not, using the corresponding
In either case replace 8.3 with the version of PostgreSQL you are running, or on some distributions remove the version number and hyphen entirely.
The content of all queries to the server should now appear in the log. The location of this will depend on the configuration. On Debian-based systems the default is
/var/log/postgresql/postgresql-8.3-main.log (replacing 8.3 with the version of PostgreSQL you are running). On Red Hat-based systems it is located in
Connect to the database using
psql and issue the command:
This should result in a log entry similar to:
LOG: statement: SELECT 2+2;
Logging every query will reduce the performance of the database server, especially if its workload consists of many simple queries. For this reason you will probably want to disable it once you have obtained the information you need.