In this article, we will see how to install or create pg_stat_statements extension in PostgreSQL.
What is pg_stat_activity?
The pg_stat_statements module provides a means for tracking planning and execution statistics of all SQL statements executed by a server.
Install or create pg_stat_statements extension in PostgreSQL::
1. Check wheter pg_stat_statements extension is installed or not using below command.
SELECT * FROM pg_available_extensions;
If not installed, then install the following contrib package.
sudo yum install postgresql15-contrib
Where 15 is the version PostgreSQL server. In your case give the respective PostgreSQL server version number.
2. Now, add extension pg_stat_statement.
Extensions in PostgreSQL server are database level. We have create extension for each database separately.
connect to the database to which we want to add extension.
\c test123 test123=# CREATE EXTENSION pg_stat_statements; CREATE EXTENSION
3. Now, open postgresql.conf file and add the following lines at the bottom of the file.
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
4. Now restart the postgresql server.
sudo systemctl restart postgresql-15
5. Now, you can run following command to get the statements information.
select * from pg_stat_statements;
So we have seen how to create or add pg_stat_statements extension in PostgreSQL server.