How to activate slow queries logs in postgresql?

​​ ​​

环境

• 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%'