Pmm@postgres ERROR: relation "pg_stat_statements" does not exist at character 753

PostgreSQL PostgreSQL General Discussion
1

Hello,
Any one can help me for my problem on postgresql using pmm-admin,
I got the error log,

2021-01-11 13:25:00.013 WIB [19845] pmm@postgres ERROR:  relation "pg_stat_statements" does not exist at character 753
2021-01-11 13:25:00.013 WIB [19845] pmm@postgres STATEMENT:  SELECT /* pmm-agent:pgstatstatements */ "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"."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" WHERE queryid IS NOT NULL AND query IS NOT NULL

then is my pmm-admin version

root@webfarmcli:/var/log/postgresql# pmm-admin --version
ProjectName: pmm-admin
Version: 2.13.0
PMMVersion: 2.13.0
Timestamp: 2020-12-29 11:01:24 (UTC)
FullCommit: d59c04db689ae0bc7eff55b7bd717a54a63ad550
root@webfarmcli:/var/log/postgresql#

and then i cek on my postgresql console,

root=# SELECT * 
root-# FROM pg_available_extensions 
root-# WHERE 
root-#     name = 'pg_stat_statements' and 
root-#     installed_version is not null;
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.6             | 1.6               | track execution statistics of all SQL statements executed
(1 row)

root=#
root=# SHOW shared_preload_libraries;
 shared_preload_libraries 
--------------------------
 pg_stat_statements
(1 row)

root=#
root=# SELECT * FROM pg_stat_statements limit 1;

 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 
--------+-------+------------+------------------------------------------------------------------------------------------------------------+-------+------------+----------+----------+-----------+--------------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
  17187 | 13018 | 1727914665 | SELECT                                                                                                    +|     9 |   3.586581 | 0.350757 | 0.460403 |  0.398509 | 0.0369535392441193 |  324 |              24 |                0 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
        |       |            |                                 pg_database.datname,                                                      +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                 tmp.state,                                                                +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                 COALESCE(count,$1) as count,                                              +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                 COALESCE(max_tx_duration,$2) as max_tx_duration                           +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                         FROM                                                                              +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                 (                                                                         +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                   VALUES ($3),                                                            +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                                  ($4),                                                    +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
        |       |            |                                                  ($5),                                                    +|       |            |          |          |           |                    |      |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               | 
:

Fyi, i used postgresql 9.5 and 10 version.
Any idea?

1 Like
2

Hello @Gunks535,

It’s likely that you need to run CREATE EXTENSION pg_stat_statements; in the postgres database that’s used by PMM to connect. Connecting to postgres db the default (and only, afaik) behavior for PMM at this point, and you can additionally see that in your error log showing pmm@postgres.

Even though in your case pg_stat_statements is in shared_preload_libraries, and thus is already tracking activities, you need to have the necessary view created. That happens when CREATE EXTENSION pg_stat_statements; is executed in a specific database. You have the pg_stat_statements view in root database, apparently.

Thanks,
Sergey

3 Likes
3

Hello,
I’m already run command ass your suggest, but its still not resolve,

root=# CREATE EXTENSION pg_stat_statements;
ERROR: extension “pg_stat_statements” already exists
root=#

the log error still same on my previous,

and then, what you mean " You have the pg_stat_statements view in root database, apparently."?
because i’m not familiar with postgresql… :grin:
Could you explain to me?

1 Like
4

Hi,

By defaullt, psql’s prompt shows database you are connected to. For example:

postgres@ip-172-30-1-91:~$ psql
psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1))
Type "help" for help.

postgres=# <<< postgres is the database here

In your case it’s root=#, making it seem that you’re connected to the root database. You can try executing \c postgres in psql to connect to postgres database, where you need to run the create extension command. You can always run \conninfo to see more details about the connection.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \c sbtest
You are now connected to database "sbtest" as user "postgres".
sbtest=# \conninfo
You are connected to database "sbtest" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Thanks,
Sergey

1 Like
5

That’s it!
I don’t recognize if by default postgresql by default is postgres user, and the root user was created by postgres user, lol :rofl:
Thank you for your support, we can close it now., :grin: :grin: :smile:

3 Likes