Turning on query logging for PostgreSQL in Amazon RDS

Source: https://www.postgresql.org/docs/9.4/runtime-config-logging.html

This is a very specific topic, but I needed to do it to track down performance of a slow web service call.

First, go to your Amazon RDS instances tab and select the database that needs logging turned on, then go to “See details” under instance actions.

Find the Parameter Group attached to the database. Click on it to edit it, or go to “Parameter groups” in the sidebar and find the corresponding parameter group to edit.

There are two parameters you want to look for: log_statement and log_min_duration_statement (see update below). log_statement accepts 3 values: none (default), ddl, mod, and all. For nearly all purposes, you want to change this to all. log_min_duration_statement simply sets a filter (in milliseconds) for when to log a SQL query – this lets you specifically target slow queries. The default value is -1 (disabled). In my case, most queries were in the 3-5ms range, so I set it to 10.

Once you save the parameters, it’ll take about a minute or so for them to be applied to your database(s) and logging to begin. Once they are done applying, go back to “See details” for your database instance, and scroll down to the “Logs” section. Logs (postgres.log files) are displayed from oldest to newest, so you actually have to click through to the last page (for me, page 15) to see the newest log files.

It turns out we had very few queries that were over 10ms, and the slowest one was 68ms, so the bottleneck was elsewhere.

UPDATE 2019-03-17

The original entry was written for Postgres 9.4 in 2018. It looks like I was out of my mind at the time, and this setting is called “log_min_duration_statement“. As a helpful person pointed out in the comments, log_statement = all will log all statements (schema updates and data modifications).

Here’s a summary of what the Postgres 9.4 documentation on these variables:

log_min_duration_statement will log the statement duration if it takes longer than the given time span; 0 will log all statement durations. This SO link indicates that the log_statement setting has no effect on which statements are logged by log_min_duration_statement.

While we’re at it, let’s talk about log_duration, too. This boolean value causes every duration to be logged, but differs from log_min_duration_statement in that log_duration does not log query text, while log_min_duration_statement will log query text of statements that exceed the duration.

Source: https://www.postgresql.org/docs/9.4/runtime-config-logging.html