Monday, 23 January 2012

Monitor slow Postgresql queries

To monitor slow Postgresql queries, open a terminal and enter (the folder locations are for Ubuntu 11.10, they may be slightly different for other Linux distributions)

gksudo gedit /etc/postgresql/9.1/main/postgresql.conf

(change 9.1 to your version of Postgresql)

Search for and modify the following conf parameters

# Switch logging on
logging_collector = on

# Switch logging durations on
log_duration = on

# Log queries that are over 200ms
log_min_duration_statement = 200

# Log is recorded as a CSV file
log_destination = csvlog

Save the file and restart Postgresql

sudo /etc/init.d/postgresql restart

The logs will then be saved in the following folder


You will need to be root to access the logs, probably easy to start Nautilus as root

gksudo nautilus


  1. There is no need for a "restart", "reload" is good enough. You could also use a SQL-statement to reload (superuser permissions needed):
    SELECT pg_reload_conf();

    1. Cheers - I noticed that with restart the log file is also restarted - which I prefer for testing - I don't think reload does this?