Step 2: Enable pg_stat_statements

Enable pg_stat_statements in your Postgres container configuration

To get query statistics in Postgres, we need to modify the Postgres config setting called shared_preload_libraries.

You can do this by stopping your Postgres container, and then starting it again with the correct configuration as part of the command line arguments:

docker run -d --name my-pg postgres -c "shared_preload_libraries='pg_stat_statements'"

Alternatively you can also use ALTER SYSTEM to modify the postgresql.auto.conf file through SQL:

--- See currently configured shared_preload_libraries
SHOW shared_preload_libraries;

--- Set new shared_preload_libraries (include any existing values and comma-separate them from the new value)
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

You will need to restart the Postgres container for the shared_preload_libraries change to take effect.

Be aware that in a later installation step, we optionally recommend adding the auto_explain library to shared_preload_libraries as well. To avoid an additional restart later, you can add auto_explain at this time already by setting shared_preload_libraries = 'pg_stat_statements,auto_explain'.

Verify that pg_stat_statements returns data

As a superuser, run the following statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, query FROM pg_stat_statements LIMIT 1;

If you have configured your database correctly, this will return a result like this:

 calls | query
-------+-------
     8 | SELECT * FROM t WHERE field = ?
(1 row)

If you get an error you might not have restarted the container, or passed the configuration settings the right way. If you get stuck feel free to reach out to us, we're happy to help.

Next we continue by installing the pganalyze collector: