pg_stat_statements enabled, but the table does not exist

Question

I've postgresql-9.4 up and running, and I've enabled pg_stat_statements module lately by the help of official documentation.

But I'm getting following error upon usage:

postgres=# SELECT * FROM pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: SELECT * FROM pg_stat_statements;


postgres=# SELECT pg_stat_statements_reset();
ERROR:  function pg_stat_statements_reset() does not exist
LINE 1: SELECT pg_stat_statements_reset();

I'm logged in to psql with the postgres user. I've also checked the available extension lists:

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'
;
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.2             |                   | track execution statistics of all SQL statements executed
(1 row)

And here's the results of the extension versions query:

postgres=# SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';
        name        | version | installed | superuser | relocatable | schema | requires |                          comment                          
--------------------+---------+-----------+-----------+-------------+--------+----------+-----------------------------------------------------------
 pg_stat_statements | 1.2     | f         | t         | t           |        |          | track execution statistics of all SQL statements executed
(1 row)

Any help will be appreciated.

Answer 1

Extension isn't installed:

SELECT * 
FROM pg_available_extensions 
WHERE 
    name = 'pg_stat_statements' and 
    installed_version is not null;

If the table is empty, create the extension:

CREATE EXTENSION pg_stat_statements;

Answer 2

I've faced with this issue at configuring Percona Monitoring and Management (PMM) because by some strange reason PMM connecting to database with name postgres, so pg_stat_statements extension have to be created in this database:

yourdb# \c postgres
postgres# CREATE EXTENSION pg_stat_statements SCHEMA public;

Answer 3

I Had the same issue when deploying the environment using liquibase for the first time. I understand that my reply maybe is not related with your problem but was the first google result so I think that other guys like me can arrive here with my the same Liquibase Issue.

These are PosGreSQL metadata tables that are retrieved by liquibase when you generate your first xml file.

In my case it only was useless autogenerated code, so I solved it deleteing these lines:

 <changeSet author="martinlarizzate (generated)" id="1588181532394-7">
        <createView fullDefinition="false" viewName="pg_stat_statements"> SELECT pg_stat_statements.userid,
    pg_stat_statements.dbid,
    pg_stat_statements.queryid,
    pg_stat_statements.query,
    pg_stat_statements.calls,
    pg_stat_statements.total_time,
    pg_stat_statements.min_time,
    pg_stat_statements.max_time,
    pg_stat_statements.mean_time,
    pg_stat_statements.stddev_time,
    pg_stat_statements.rows,
    pg_stat_statements.shared_blks_hit,
    pg_stat_statements.shared_blks_read,
    pg_stat_statements.shared_blks_dirtied,
    pg_stat_statements.shared_blks_written,
    pg_stat_statements.local_blks_hit,
    pg_stat_statements.local_blks_read,
    pg_stat_statements.local_blks_dirtied,
    pg_stat_statements.local_blks_written,
    pg_stat_statements.temp_blks_read,
    pg_stat_statements.temp_blks_written,
    pg_stat_statements.blk_read_time,
    pg_stat_statements.blk_write_time
   FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);</createView>
    </changeSet>

Answer 4

Follow below steps:

• Create the extension

  CREATE EXTENSION pg_stat_statements;
  

• Change in config

  alter system set shared_preload_libraries='pg_stat_statements';
  

• Restart

  $ systemctl restart postgresql
  

• Verify changes applied or not.

  select * from pg_file_Settings where name='shared_preload_libraries';
  

  The applied attribute must be 'true'.