Go to page content

Debugging db (query) issues

Techniques for finding root cause when queries are involved.

Why

When you face unexpected behavior as a result of some database query (z_db:q et al), you either have to hunt down the queries and re-run them by hand, which is really time consuming and painful, or, which this cookbook will detail, have postgresql log the queries for you.

The principal documentation and with more details, may be found here: http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html

Assumptions

A working system where you want to inspect the db queries being run.

How

Edit your postgresql.conf file, enabling logstatement = 'all'. This file has a lot of options commented out, with comments which you may review. Options of interest in this scenario all begin with "log" (or similar).

By having log_destination = 'stderr' and logging_collector = on, you can capture your logging output to file.

Troubleshooting

No trouble shooting tips at this time.

Disclaimer:

This has only been tested by the cookbook author on a Win 7 x64 machine, running postgresql 8.4.

Successfully replacing the version in the path for the postgresql documentation, for 8.3 and 9.0 indicates that this feature works across a wider range of versions of postgresql.

This page is part of the Zotonic documentation, which is licensed under the Apache License 2.0.