October 20, 2023
Summary: in this tutorial, you will learn how to configure
pg_stat_statements as an extension.
When using an enterprise version in Oracle such as databases, we see historic information such as what query ran through what user at a certain point in time, through some views. Such views may help in identifying the queries that ran some time ago and their overall statistics, such as the total number of times the query ran and the average time consumption. Such information can be visible in PostgreSQL using an extension called
pg_stat_statements. In this tutorial, we shall discuss how
pg_stat_statements can be configured as an extension.
In order to configure this extension, we must have a PostgreSQL server that has the
contrib module installed. This extension is part of the
contrib module and does not involve any complex procedure to install and configure. So, all it requires is the
postgresql-contrib-13 package on Ubuntu or the
postgresql13-contrib package on CentOS/Red Hat to install this extension.
How to do it…
We will configure the extensions using the following steps:
$ psql -c "ALTER SYSTEM SET shared_preload_libraries TO pg_stat_statements"
If there are multiple extensions that are to be loaded, we could use a command similar to the following:
$ psql -c "ALTER SYSTEM SET shared_preload_libraries TO auto_explain, pg_stat_statements, postgres_fdw"
2. Restart the PostgreSQL server to get the changes into effect.
$PGDATA is the path to the data directory:
$ pg_ctl -D $PGDATA restart
3. Create the extension in the database for which query statistics need to be collected:
$ psql -d rockdb -c "CREATE EXTENSION pg_stat_statements"
4. Validate by selecting the count from
$ psql -d rockdb -c "select count(*) from pg_stat_statements"
How it works…
pg_stat_statements is very simple. If the
contrib module is installed, it is just a matter of loading
pg_stat_statements libraries through
shared_preload_libraries as seen in step 1.
As any change to
shared_preload_libraries requires a restart, we have to restart PostgreSQL as seen in step 2. Upon restart, we should create the extension using the
CREATE EXTENSION statement and we would start looking at the queries we ran immediately through a simple validation as seen in steps 3 and 4.