Satellite 6: How to enable postgres query logging to detect slow running queries

Solution Verified - Updated

Environment

Red Hat Satellite 6

Issue

If postgres is consuming large amounts of CPU on your Satellite 6 server it may be due to specific queries running longer than expected.

Resolution

With the following configuration changes you can able more verbose logging via Postgres's built in tooling.

Please run the following:

  • Edit /var/lib/pgsql/data/postgresql.conf or /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf on 6.8 or later.

  • Change log_min_duration_statement to 500:

log_min_duration_statement = 500 # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds

This will log any query that runs longer than 0.5 seconds.

  • Reload configuration:
echo "SELECT pg_reload_conf();" |  sudo -u postgres psql
  • Now in /var/lib/pgsql/data/pg_log you can tail today's logfile, eg:
# tail -f postgresql-Tue.log 
2016-10-25 11:58:06 EDT LOG:  received SIGHUP, reloading configuration files
2016-10-25 11:58:17 EDT LOG:  received SIGHUP, reloading configuration files
2016-10-25 11:58:17 EDT LOG:  parameter "log_min_duration_statement" changed to "500"

and should start to see queries that take more than 0.5 seconds show up.

To disable this extended logging just revert the configuration change and reload it again.

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.