Running pmm2 with Postgresql V10, monitoring user with access to pg_stat_statement view on postgres database.
We can see most of the query run by monitoring user against database postgres, however PMM QA does not show query by other users or query run against other database in cluster. I can see all queries if psql using monitoring user.
Does PMM need super user access to pg_stat_statement? Also, do I need to add extension all database in cluster to view query by databases?
I’m far from the expert here so someone else please correct where I’m wrong but 1) making the monitoring user SUPER certainly makes for less fuss but at then that’s another user with lots of access so you’ll have to decide which approach is right for you.
when I was testing pg_stat_monitor and pg_stat_statements I had to enable the plugin as the user I was monitoring with against the postgres db…when I originally enabled the plugin as root, then monitoring with user pmm I could only see the queries that the pmm user performed.
lastly I believe when you enable it against the postgres db all db’s should be enabled automatically (certainly newly added db’s) but I thought someone told me you’d have to manually enable it for other existing db’s but I can’t find that anywhere and that could have been pg_stat_monitor only… again, not the expert here, just giving a few things to try until someone else gives the right answer
Thanks for response. So in my case I did enable the pg_stat_stement as postgres user in main postgres db and created a view using below and when I do log in locally using the monitoring user and able to query everything from table but limited data is shown in PMM QA dashboard which is quite strange so not sure where the filter is applied.
But I’ll try creating 2nd monitoring user with super permissions and see what happens.
The document does says it should capture all database query in cluster but wasn’t able to filter time base and when I did by OID no database there which is strange.
