Have an error when I include the pg_stat_kcache extension
HankDrews opened this issue · 8 comments
HankDrews commented
I'm receiving the following error after I log into the Web interface:
powa_take_snapshot(0): function "powa_kcache_snapshot" failed: function pg_stat_kcache() does not exist
I've installed both the pg_stat_statements and pg_stat_kcache extension into all databases I'm monitoring. Other than this error, everything else appears to be working and the graphs are updating normally.
I'm running Postgresql 14.5, powa 4.1.4 and powa-web 4.1.4. Let me know if you need further information.
rjuju commented
Hello
First, are you using powa in local mode or remote mode?
The, powa only requires pg_stat_statements and pg_stat_kcache (or any other stat extension) to be installed in the dedicated powa databases (on both the repository server and remote servers if you're using remote mode). The error seems to indicate that the extension isn't present in the powa database.
HankDrews commented
I'm running Postgresql and powa on the same server, so I would guess it local mode.
Henry K. Drews |
Phone: 703-561-3797| Mobile: 571-264-3189
***@***.***
…________________________________
From: Julien Rouhaud ***@***.***>
Sent: Friday, October 21, 2022 1:31 PM
To: powa-team/pg_stat_kcache ***@***.***>
Cc: Drews, Hank K. [US-US] ***@***.***>; Author ***@***.***>
Subject: EXTERNAL: Re: [powa-team/pg_stat_kcache] Have an error when I include the pg_stat_kcache extension (Issue #35)
CAUTION: This email originated from outside of Leidos. Be cautious when clicking or opening content.
Hello
First, are you using powa in local mode or remote mode?
The, powa only requires pg_stat_statements and pg_stat_kcache (or any other stat extension) to be installed in the dedicated powa databases (on both the repository server and remote servers if you're using remote mode). The error seems to indicate that the extension isn't present in the powa database.
—
Reply to this email directly, view it on GitHub<https://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287249109__;Iw!!Az_Xe1LHMyBq19w!auk4Q3e9hqpSP_o2mEWKgj5zheZKowny474QhqY7VRwdfL-YZ6t2IddQQt3dm5zaDjY$>, or unsubscribe<https://urldefense.us/v3/__https://github.com/notifications/unsubscribe-auth/AYFD72HMODUWUD2KXSM5NADWELHNPANCNFSM6AAAAAARLLH3KM__;!!Az_Xe1LHMyBq19w!auk4Q3e9hqpSP_o2mEWKgj5zheZKowny474QhqY7VRwdfL-YZ6t2IddQQt3dtQZZtVk$>.
You are receiving this because you authored the thread.Message ID: ***@***.***>
rjuju commented
Ok, so having powa in shared_preload_libraries, and configuring powa.frequency (and maybe others) in the postgresql.conf file?
Can you show the output of \dx connected on the powa database?
HankDrews commented
psql --username=powa --dbname=powa
psql (14.5)
Type "help" for help.
powa=# \conninfo
You are connected to database "powa" as user "powa" on host "cougar-pg-01.ds.annex.peratondev.lab" (address "10.238.27.22") at port "5432".
powa=# \dx
List of installed extensions
Name | Version | Schema | Description
------------------------+---------+--------------------+---------------------------------------------------------------------------------------------------------------------
address_standardizer | 3.2.2 | public | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
bool_plperl | 1.0 | public | transform between bool and plperl
btree_gin | 1.3 | public | support for indexing common datatypes in GIN
btree_gist | 1.6 | public | support for indexing common datatypes in GiST
citext | 1.6 | public | data type for case-insensitive character strings
credcheck | 0.2.0 | public | credcheck - postgresql plain text credential checker
fuzzystrmatch | 1.1 | public | determine similarities and distance between strings
hstore | 1.8 | public | data type for storing sets of (key, value) pairs
hstore_plperl | 1.0 | public | transform between hstore and plperl
hypopg | 1.3.1 | public | Hypothetical indexes for PostgreSQL
jsonb_plperl | 1.0 | public | transform between jsonb and plperl
login_hook | 1.3 | login_hook | login_hook - hook to execute login_hook.login() at login time
orafce | 3.22 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pg_background | 1.0 | public | Run SQL queries in the background
pg_qualstats | 2.0.4 | public | An extension collecting statistics about quals
pg_stat_kcache | 2.2.1 | information_schema | Kernel statistics gathering
pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed
pg_track_settings | 2.1.0 | public | Track settings changes
pg_trgm | 1.6 | public | text similarity measurement and index searching based on trigrams
pg_wait_sampling | 1.1 | public | sampling based statistics of wait events
pgaudit | 1.6.2 | public | provides auditing functionality
pgcrypto | 1.3 | public | cryptographic functions
plperl | 1.0 | pg_catalog | PL/Perl procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.2.2 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.2.2 | public | PostGIS raster types and functions
postgis_sfcgal | 3.2.2 | public | PostGIS SFCGAL functions
postgis_tiger_geocoder | 3.2.2 | tiger | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 3.2.2 | topology | PostGIS topology spatial types and functions
powa | 4.1.4 | public | PostgreSQL Workload Analyser-core
rum | 1.3 | public | RUM index access method
tablefunc | 1.0 | public | functions that manipulate whole tables, including crosstab
(32 rows)
Henry K. Drews |
Phone: 703-561-3797| Mobile: 571-264-3189
***@***.***
…________________________________
From: Julien Rouhaud ***@***.***>
Sent: Friday, October 21, 2022 1:41 PM
To: powa-team/pg_stat_kcache ***@***.***>
Cc: Drews, Hank K. [US-US] ***@***.***>; Author ***@***.***>
Subject: EXTERNAL: Re: [powa-team/pg_stat_kcache] Have an error when I include the pg_stat_kcache extension (Issue #35)
CAUTION: This email originated from outside of Leidos. Be cautious when clicking or opening content.
Ok, so having powa in shared_preload_libraries, and configuring powa.frequency (and maybe others) in the postgresql.conf file?
Can you show the output of \dx connected on the powa database?
—
Reply to this email directly, view it on GitHub<https://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287258855__;Iw!!Az_Xe1LHMyBq19w!aAAto_OJJ-Fw1c1X1JGeToDe85-viub50r_Hty6PsMFFFjAZy8uEQa2i3tBMUMjw8as$>, or unsubscribe<https://urldefense.us/v3/__https://github.com/notifications/unsubscribe-auth/AYFD72B2R72FEMAORO3SHQTWELIS3ANCNFSM6AAAAAARLLH3KM__;!!Az_Xe1LHMyBq19w!aAAto_OJJ-Fw1c1X1JGeToDe85-viub50r_Hty6PsMFFFjAZy8uEQa2i3tBMt_anD6s$>.
You are receiving this because you authored the thread.Message ID: ***@***.***>
HankDrews commented
powa=# select current_setting('shared_preload_libraries');
current_setting
----------------------------------------------------------------------------------------
credcheck,pgaudit,pg_stat_statements,powa,pg_stat_kcache,pg_qualstats,pg_wait_sampling
Henry K. Drews |
Phone: 703-561-3797| Mobile: 571-264-3189
***@***.***
…________________________________
From: Drews, Hank K. [US-US] ***@***.***>
Sent: Friday, October 21, 2022 1:42 PM
To: powa-team/pg_stat_kcache ***@***.***>; powa-team/pg_stat_kcache ***@***.***>
Cc: Author ***@***.***>
Subject: Re: EXTERNAL: Re: [powa-team/pg_stat_kcache] Have an error when I include the pg_stat_kcache extension (Issue #35)
psql --username=powa --dbname=powa
psql (14.5)
Type "help" for help.
powa=# \conninfo
You are connected to database "powa" as user "powa" on host "cougar-pg-01.ds.annex.peratondev.lab" (address "10.238.27.22") at port "5432".
powa=# \dx
List of installed extensions
Name | Version | Schema | Description
------------------------+---------+--------------------+---------------------------------------------------------------------------------------------------------------------
address_standardizer | 3.2.2 | public | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
bool_plperl | 1.0 | public | transform between bool and plperl
btree_gin | 1.3 | public | support for indexing common datatypes in GIN
btree_gist | 1.6 | public | support for indexing common datatypes in GiST
citext | 1.6 | public | data type for case-insensitive character strings
credcheck | 0.2.0 | public | credcheck - postgresql plain text credential checker
fuzzystrmatch | 1.1 | public | determine similarities and distance between strings
hstore | 1.8 | public | data type for storing sets of (key, value) pairs
hstore_plperl | 1.0 | public | transform between hstore and plperl
hypopg | 1.3.1 | public | Hypothetical indexes for PostgreSQL
jsonb_plperl | 1.0 | public | transform between jsonb and plperl
login_hook | 1.3 | login_hook | login_hook - hook to execute login_hook.login() at login time
orafce | 3.22 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pg_background | 1.0 | public | Run SQL queries in the background
pg_qualstats | 2.0.4 | public | An extension collecting statistics about quals
pg_stat_kcache | 2.2.1 | information_schema | Kernel statistics gathering
pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed
pg_track_settings | 2.1.0 | public | Track settings changes
pg_trgm | 1.6 | public | text similarity measurement and index searching based on trigrams
pg_wait_sampling | 1.1 | public | sampling based statistics of wait events
pgaudit | 1.6.2 | public | provides auditing functionality
pgcrypto | 1.3 | public | cryptographic functions
plperl | 1.0 | pg_catalog | PL/Perl procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.2.2 | public | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.2.2 | public | PostGIS raster types and functions
postgis_sfcgal | 3.2.2 | public | PostGIS SFCGAL functions
postgis_tiger_geocoder | 3.2.2 | tiger | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 3.2.2 | topology | PostGIS topology spatial types and functions
powa | 4.1.4 | public | PostgreSQL Workload Analyser-core
rum | 1.3 | public | RUM index access method
tablefunc | 1.0 | public | functions that manipulate whole tables, including crosstab
(32 rows)
Henry K. Drews |
Phone: 703-561-3797| Mobile: 571-264-3189
***@***.***
________________________________
From: Julien Rouhaud ***@***.***>
Sent: Friday, October 21, 2022 1:41 PM
To: powa-team/pg_stat_kcache ***@***.***>
Cc: Drews, Hank K. [US-US] ***@***.***>; Author ***@***.***>
Subject: EXTERNAL: Re: [powa-team/pg_stat_kcache] Have an error when I include the pg_stat_kcache extension (Issue #35)
CAUTION: This email originated from outside of Leidos. Be cautious when clicking or opening content.
Ok, so having powa in shared_preload_libraries, and configuring powa.frequency (and maybe others) in the postgresql.conf file?
Can you show the output of \dx connected on the powa database?
—
Reply to this email directly, view it on GitHub<https://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287258855__;Iw!!Az_Xe1LHMyBq19w!aAAto_OJJ-Fw1c1X1JGeToDe85-viub50r_Hty6PsMFFFjAZy8uEQa2i3tBMUMjw8as$>, or unsubscribe<https://urldefense.us/v3/__https://github.com/notifications/unsubscribe-auth/AYFD72B2R72FEMAORO3SHQTWELIS3ANCNFSM6AAAAAARLLH3KM__;!!Az_Xe1LHMyBq19w!aAAto_OJJ-Fw1c1X1JGeToDe85-viub50r_Hty6PsMFFFjAZy8uEQa2i3tBMt_anD6s$>.
You are receiving this because you authored the thread.Message ID: ***@***.***>
rjuju commented
That's a lot of extensions!
Anyway, it seems that you installed pg_stat_kcache in the information_schema schema and not public. I would advise you to drop the extension and recreate it in the public schema. Once done, the next snapshot should work as intended.
HankDrews commented
That appears to fix the issue.
Thanks for all your help.
Henry K. Drews |
Phone: 703-561-3797| Mobile: 571-264-3189
***@***.***
…________________________________
From: Julien Rouhaud ***@***.***>
Sent: Friday, October 21, 2022 1:46 PM
To: powa-team/pg_stat_kcache ***@***.***>
Cc: Drews, Hank K. [US-US] ***@***.***>; Author ***@***.***>
Subject: EXTERNAL: Re: [powa-team/pg_stat_kcache] Have an error when I include the pg_stat_kcache extension (Issue #35)
CAUTION: This email originated from outside of Leidos. Be cautious when clicking or opening content.
That's a lot of extensions!
Anyway, it seems that you installed pg_stat_kcache in the information_schema schema and not public. I would advise you to drop the extension and recreate it in the public schema. Once done, the next snapshot should work as intended.
—
Reply to this email directly, view it on GitHub<https://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287264647__;Iw!!Az_Xe1LHMyBq19w!fP85BF8YVVC69TKqzUsLu3KXEYw915s9TJZNUg_UIDunHGt6UMRamMtlG7osywgxTO8$>, or unsubscribe<https://urldefense.us/v3/__https://github.com/notifications/unsubscribe-auth/AYFD72BIRIQJMZ7UQJ5QYRLWELJJFANCNFSM6AAAAAARLLH3KM__;!!Az_Xe1LHMyBq19w!fP85BF8YVVC69TKqzUsLu3KXEYw915s9TJZNUg_UIDunHGt6UMRamMtlG7osrlHBT-g$>.
You are receiving this because you authored the thread.Message ID: ***@***.***>
rjuju commented
Great news, thanks a lot!