Clicking pghero under Mastodon Administration menu, the interface shows the
Query stats must be enabled for slow queries orange warning, in the
interface that looks like this:
After clicking the blue Enable button, instead of a success, the error
The database user does not have permission to enable query stats is
shown in the top row or the interface as a red ribbon:
A small hint toward resolving this error could be found in the
pghero#7
which mentions running a SQL statement under user postgres like this:
CREATE extension pg_stat_statements;
To run such a command, establish an access to the postgres database first.
If running
Mastodon under docker-compose
navigate to the directory where docker-compose.yml is located and run the
following:
sudo docker exec -it mastodon_db_1 psql -h localhost -U postgres
When in psql, run these commands:
postgres=# \c mastodon;
You are now connected to database "mastodon" as user "postgres".
mastodon=# CREATE extension pg_stat_statements;
CREATE EXTENSION
mastodon=# exit
Refreshing pghero interface nows outputs a verbose message
Make Query Stats available by adding the following lines to postgresql.conf
and then restart the server for changes to take effect:
Still assuming the above
guide, the postgres/
folder is in the same one as docker-completely.yml. Edit the file
postgres/postgresql.conf there and add/uncomment the exact same lines
from the screenshot above:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
I have put these under the sections CLIENT CONNECTION DEFAULTS and
STATISTICS respectively. The shared_preload_libraries have a comment
there stating also # (change requires restart), so do the following:
sudo docker-compose down
sudo docker-compose up -d
Refreshing pghero interface now shows a green success line stating
No slow queries instead:
Although I am not sure at this point what is this configuration actually good for, I have found out how to get rid of the warning and here's the guide. Enjoy!