search

powa-team / pg_stat_kcache

Gather statistics about physical disk access and CPU consumption done by backends.
Other
206 stars 25 forks source link

Have an error when I include the pg_stat_kcache extension #35

Closed HankDrews closed 1 year ago

HankDrews commented 1 year ago

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 1 year ago

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 1 year ago

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 GitHubhttps://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287249109__;Iw!!Az_Xe1LHMyBq19w!auk4Q3e9hqpSP_o2mEWKgj5zheZKowny474QhqY7VRwdfL-YZ6t2IddQQt3dm5zaDjY$, or unsubscribehttps://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 1 year ago

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 1 year ago

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 GitHubhttps://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287258855__;Iw!!Az_Xe1LHMyBq19w!aAAto_OJJ-Fw1c1X1JGeToDe85-viub50r_Hty6PsMFFFjAZy8uEQa2i3tBMUMjw8as$, or unsubscribehttps://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 1 year ago

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 GitHubhttps://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287258855__;Iw!!Az_Xe1LHMyBq19w!aAAto_OJJ-Fw1c1X1JGeToDe85-viub50r_Hty6PsMFFFjAZy8uEQa2i3tBMUMjw8as$, or unsubscribehttps://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 1 year ago

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 1 year ago

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 GitHubhttps://urldefense.us/v3/__https://github.com/powa-team/pg_stat_kcache/issues/35*issuecomment-1287264647__;Iw!!Az_Xe1LHMyBq19w!fP85BF8YVVC69TKqzUsLu3KXEYw915s9TJZNUg_UIDunHGt6UMRamMtlG7osywgxTO8$, or unsubscribehttps://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 1 year ago

Great news, thanks a lot!