Log all queries to a PostgreSQL server

Objective

To configure a PostgreSQL server to log the content of all queries.

Scenario

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.

Method

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

Any existing log_statement or 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 init.d script:

/etc/init.d/postgresql-8.3 restart

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 /var/lib/pgsql/data/pg_log/.

Testing

Connect to the database using psql and issue the command:

SELECT 2+2;

This should result in a log entry similar to:

LOG:  statement: SELECT 2+2;

Performance considerations

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.

See also

• Log all queries to a MySQL server

• Conventions
• Privacy
• Legal