Skip to main content

Query Log for Postgres

Create a query workload from a PostgreSQL query log.

How to get query logged​

Locate postgresql.conf​

  • On Debian-based systems, postgresql.conf is located in /etc/postgresql/$version/main/,
  • and on Red Hat-based systems, it's in /var/lib/pgsql/data/.

If you still can’t find it, you can type in terminal,

locate postgresql.conf

or execute the following SQL query

SHOW config_file;

Configure logging parameters​

And then, you need to change these parameters inside PostgreSQL configuration file.

    vi postgresql.conf
    log_statement = 'all'
    log_directory = 'pg_log'
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
    logging_collector = on

On older versions of PostgreSQL prior to 8.0, replace 'all' with 'true' for the log_statement:

     log_statement = 'true'

You can only log slow queries which execution time is longer than a threshold by setting following parameter.

    log_min_duration_statement = 5000

Restart server​

Then you need to restart the database server to make the configuration taking effect:

sudo /etc/init.d/postgresql restart

or

sudo service postgresql restart

Now, all or some of the statements submitted to the server should now appear in the log file.