How to activate slow queries logs in postgresql?

https://access.redhat.com/solutions/69314

 SOLUTION UNVERIFIED - 已更新 2013年三月13日22:09 - 

English 

环境

• Red Hat Enterprise Linux 6
• postgresql-server

问题

• How to activate slow queries logs in postgresql?

决议

You could set this in postgresql.conf, To find the postgresql.conf you could use the find command like this:

Raw

    # find / -name postgresql.conf

Now, in the postgresql.conf file you should look for the line:

Raw

    #log_min_duration_statement = -1

and replace it with:

Raw

    log_min_duration_statement = 500

This will log any querie that takes more than 500ms (miliseconds) to be done. You could adjust this to the value that you may considerer correct.

After that, save the file and reload the PostgreSQL configuration:

Raw

    # service postgresql reload

Go to the directory where the logs of PostgreSQL are (comonly located in /var/lib/pgsql/data/pg_lo) and look for the lasted log file created.

Below, you will find an example of a query that took more than 500ms:

Raw

    2011-09-29 11:27:39 BRT LOG: duration: 2051.457 ms execute pdo_stmt_000000b4: select * from backuplog WHERE message LIKE '%xyz%'