October 20, 2023
Summary: in this tutorial, you will learn how to configure pg_stat_statements as an extension.
Table of Contents
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.
Getting ready
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:
1. Add pg_stat_statements to shared_preload_libraries:
$ 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 pg_stat_statements:
$ psql -d rockdb -c "select count(*) from pg_stat_statements"
How it works…
Setting up 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.