powa-team/pg_stat_kcache

pg_stat_statements must be loaded via shared_preload_libraries

Paulpatou opened this issue · 7 comments

Paulpatou commented

Hi,
I'm trying to use the extension pg_stat_kcache but i have an error in powa-web ==>Test_remote_NAMESRV-Training: Error while calling public.powa_statements_src: ERREUR: pg_stat_statements must be loaded via shared_preload_libraries CONTEXT: fonction PL/pgsql powa_statements_src(integer), ligne 4 à RETURN QUERY
My postgresql.conf is configured==>

shared_preload_libraries='pg_stat_statements,pg_qualstats,pg_stat_kcache,hypopg,pg_track_settings,powa,'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

When I try to add the extension, I have this error =>

CREATE EXTENSION pg_stat_kcache;

ERROR:  ERREUR:  This module can only be loaded via shared_preload_libraries
État SQL :XX000

I use last git pg_stat_kcache with postgresql 9.4 on ubuntu 16

image

Thanks for help

rjuju commented

Hello,

Did you restart postgres after adding pg_stat_statements (at least) in the shared_preload_librairies?

Paulpatou commented

Yes, but no change

rjuju commented

That's weird. Can you show the results of those queries?

show shared_preload_libraries;
select count(*) from pg_stat_statements;
set powa.debug = on;
select powa_take_snapshot();
frost242 commented

Also, did you take a look at your log file right after the restart ?

Paulpatou commented

Okay, I finally found my problem an extra comma in the shared_preload_libraries =

But I have two other questions, why do all my extensions have red crosses except one?(In the column "Sampled")
image

I also checked my logs on my postgres test server and at restart I regularly get this error

2019-12-03 10:58:58 CET [6605-1011] LOG:  démarrage du processus d'écriture en tâche de fond « powa »
2019-12-03 10:58:58 CET [24184-1] FATAL:  la base de données « powa » n'existe pas
2019-12-03 10:58:58 CET [6605-1012] LOG:  processus de travail: powa (PID 24184) quitte avec le code de sortie 1
2019-12-03 10:59:08 CET [6605-1013] LOG:  démarrage du processus d'écriture en tâche de fond « powa »
2019-12-03 10:59:08 CET [24185-1] FATAL:  la base de données « powa » n'existe pas
2019-12-03 10:59:08 CET [6605-1014] LOG:  processus de travail: powa (PID 24185) quitte avec le code de sortie 1

It is normal that he does not find the powa database because it is on another postgres server dedicated to powa ( I use the remote setup)
But I must have done something wrong in the configuration I think.If you have any idea, thanks

rjuju commented

sampled actually means that the extension is declared in the powa_functions table, although it could technically be desactivated.

Since you're using the remote mode, you have to manually configure the list of remotely available extensions, as the UI can't guess it automatically, as documented in https://powa.readthedocs.io/en/latest/remote_setup.html#declare-the-list-of-remote-servers-and-their-extensions

In your case, I think that you need to refer to https://powa.readthedocs.io/en/latest/components/powa-archivist/configuration.html#powa-activate-extension if you want to add a new stat extension after initial setup.

For the error in the logs, this is because you added powa in the remote server's shared_preload_libraries so it's registering the background worker, which will automatically try to connect to the database. If you want to only rely on remote snapshot, you can avoid those messages by not adding powa to shared_preload_libraries, as documented in https://powa.readthedocs.io/en/latest/remote_setup.html#setup-the-main-repository-database.

Paulpatou commented

Great thanks I was able to solve all my problems.