{"id":6435,"date":"2015-12-09T06:44:07","date_gmt":"2015-12-09T05:44:07","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/"},"modified":"2015-12-09T06:44:07","modified_gmt":"2015-12-09T05:44:07","slug":"monitoring-tools-for-postgresql-powa","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/","title":{"rendered":"Monitoring tools for PostgreSQL \u2013 POWA"},"content":{"rendered":"<p>The last posts introduced the <a href=\"http:\/\/dbi-services.com\/blog\/sql-monitoring-in-postgresql-1-the-logging-system\/\" target=\"_blank\">logging system<\/a>, <a href=\"http:\/\/dbi-services.com\/blog\/sql-monitoring-in-postgresql-2-pg_stat_statements\/\" target=\"_blank\">pg_stat_statements<\/a>, <a href=\"http:\/\/dbi-services.com\/blog\/sql-monitoring-in-postgresql-3-pg_activity\/\" target=\"_blank\">pg_activity<\/a> and <a href=\"http:\/\/dbi-services.com\/blog\/monitoring-tools-for-postgresql-pgcluu\/\" target=\"_blank\">pgcluu<\/a>. This post will look at <a href=\"http:\/\/dalibo.github.io\/powa\/\" target=\"_blank\">POWA: PostgreSQL Workload Analyzer<\/a>.<\/p>\n<p>For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor:<\/p>\n<ul>\n<li>pg_stat_statements (<a href=\"http:\/\/dbi-services.com\/blog\/sql-monitoring-in-postgresql-2-pg_stat_statements\/\" target=\"_blank\">see last post<\/a>)<\/li>\n<li><a href=\"https:\/\/github.com\/dalibo\/pg_stat_kcache\" target=\"_blank\">pg_stat_kcache<\/a>: gathers statistics about reads and writes done by the file system layer<\/li>\n<li><a href=\"https:\/\/github.com\/dalibo\/pg_qualstats\" target=\"_blank\">pg_qualstats<\/a>: gathers statistics of predicates found in where statements and join clauses<\/li>\n<li><a href=\"www.postgresql.org\/docs\/current\/static\/btree-gist.html\" target=\"_blank\">btree_gist<\/a>: provides <a href=\"www.postgresql.org\/docs\/current\/static\/xindex.html\" target=\"_blank\">GiST<\/a> index operator classes that implement B-tree equivalent behavior for various data types<\/li>\n<\/ul>\n<p>As pg_stat_statements is already installed in my PostgreSQL instance lets start by installing the pg_stat_kcache extension. <\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/ [PG3] unzip pg_stat_kcache-master.zip\npostgres@oel7:\/var\/tmp\/ [PG3] cd pg_stat_kcache-master\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] make\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] make install\n<\/pre>\n<p>As usual: quite easy. As with pg_stat_statements we need to adjust the <a href=\"www.postgresql.org\/docs\/current\/static\/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES\" target=\"_blank\">shared_preload_libraries<\/a> parameter to have the extension loaded:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; show shared_preload_libraries;\n shared_preload_libraries \n--------------------------\n pg_stat_statements\n(1 row)\n\nTime: 0.230 ms\n(postgres@[local]:4445) [postgres] &gt; alter system set shared_preload_libraries=pg_stat_statements,pg_stat_kcache;\nALTER SYSTEM\nTime: 2.995 ms\n<\/pre>\n<p>After the PostgreSQL instance was restarted:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] pg_ctl -D \/u02\/pgdata\/PG3\/ stop -m fast\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] pg_ctl -D \/u02\/pgdata\/PG3\/ start\n<\/pre>\n<p>&#8230; the extension can be created:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; show shared_preload_libraries;\n     shared_preload_libraries      \n-----------------------------------\n pg_stat_statements,pg_stat_kcache\n(1 row)\n\n(postgres@[local]:4445) [postgres] &gt; create extension pg_stat_kcache;\nCREATE EXTENSION\nTime: 68.483 ms\n\n(postgres@[local]:4445) [postgres] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(3 rows)\n<\/pre>\n<p>The next, and final, extension which should be installed is <a href=\"https:\/\/github.com\/dalibo\/pg_qualstats\" target=\"_blank\">pg_qualstats<\/a>. The procedure is almost the same:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/ [PG3] unzip pg_qualstats-master.zip\npostgres@oel7:\/var\/tmp\/ [PG3] cd pg_qualstats-master\npostgres@oel7:\/var\/tmp\/pg_qualstats-master\/ [PG3] make\npostgres@oel7:\/var\/tmp\/pg_qualstats-master\/ [PG3] make install\n<\/pre>\n<p>Again we&#8217;ll need to adjust <a href=\"www.postgresql.org\/docs\/current\/static\/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES\" target=\"_blank\">shared_preload_libraries<\/a>:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; show shared_preload_libraries;\n     shared_preload_libraries      \n-----------------------------------\n pg_stat_statements,pg_stat_kcache\n(1 row)\n\nTime: 0.215 ms\n(postgres@[local]:4445) [postgres] &gt; alter system set shared_preload_libraries=pg_stat_statements,pg_stat_kcache,pg_qualstats;\nALTER SYSTEM\nTime: 4.692 ms\n<\/pre>\n<p>Then restart the server:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] pg_ctl -D \/u02\/pgdata\/PG3\/ stop -m fast\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] pg_ctl -D \/u02\/pgdata\/PG3\/ start\n<\/pre>\n<p>Finally create the extension:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; show shared_preload_libraries;\n             shared_preload_libraries             \n--------------------------------------------------\n pg_stat_statements, pg_stat_kcache, pg_qualstats\n(1 row)\n\nTime: 0.285 ms\n(postgres@[local]:4445) [postgres] &gt; create extension pg_qualstats;\nCREATE EXTENSION\nTime: 143.439 ms\n(postgres@[local]:4445) [postgres] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(4 rows)\n<\/pre>\n<p>The <a href=\"www.postgresql.org\/docs\/current\/static\/btree-gist.html\" target=\"_blank\">btree_gist<\/a> extension is there by default and we just need to add it:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@[local]:4445) [postgres] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(4 rows)\n\n(postgres@[local]:4445) [postgres] &gt; create extension btree_gist;\nCREATE EXTENSION\nTime: 21.112 ms\n(postgres@[local]:4445) [postgres] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST\n pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(5 rows)\n<\/pre>\n<p>Having all the requirements available we now start by installing <a href=\"https:\/\/github.com\/dalibo\/powa-archivist\" target=\"_blank\">powa-archivist<\/a>. This is the POWA extension that gathers the performance statistics. The usual steps apply:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/ [PG3] unzip powa-archivist-master.zip\npostgres@oel7:\/var\/tmp\/ [PG3] cd powa-archivist-master\npostgres@oel7:\/var\/tmp\/powa-archivist-master\/ [PG3] make\npostgres@oel7:\/var\/tmp\/powa-archivist-master\/ [PG3] make install\n<\/pre>\n<p>Again, adjust <a href=\"www.postgresql.org\/docs\/current\/static\/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES\" target=\"_blank\">shared_preload_libraries<\/a>:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; show shared_preload_libraries;\n             shared_preload_libraries             \n--------------------------------------------------\n pg_stat_statements, pg_stat_kcache, pg_qualstats\n(1 row)\n\nTime: 0.243 ms\n(postgres@[local]:4445) [postgres] &gt; alter system set shared_preload_libraries=pg_stat_statements, pg_stat_kcache, pg_qualstats, powa;\nALTER SYSTEM\nTime: 69.219 ms\n<\/pre>\n<p>Restart the server:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] pg_ctl -D \/u02\/pgdata\/PG3\/ stop -m fast\npostgres@oel7:\/var\/tmp\/pg_stat_kcache-master\/ [PG3] pg_ctl -D \/u02\/pgdata\/PG3\/ start\n<\/pre>\n<p>Create the extension:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST\n pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(5 rows)\n\n(postgres@[local]:4445) [postgres] &gt; create extension powa;\nCREATE EXTENSION\nTime: 742.831 ms\n(postgres@[local]:4445) [postgres] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST\n pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n powa               | 3.0.0   | public     | PostgreSQL Workload Analyser-core\n(6 rows)\n<\/pre>\n<p>At this point in time it is advisable to create a dedicated database for the powa repository and add all the extensions:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; create database powa;\nCREATE DATABASE\nTime: 1664.653 ms\n(postgres@[local]:4445) [postgres] &gt; \\c powa\nYou are now connected to database \"powa\" as user \"postgres\".\n(postgres@[local]:4445) [powa] &gt; create extension pg_stat_statements;\nCREATE EXTENSION\nTime: 25.448 ms\n(postgres@[local]:4445) [powa] &gt; create extension btree_gist;\nCREATE EXTENSION\nTime: 134.281 ms\n(postgres@[local]:4445) [powa] &gt; create extension pg_qualstats;\nCREATE EXTENSION\nTime: 25.683 ms\n(postgres@[local]:4445) [powa] &gt; create extension pg_stat_kcache;\nCREATE EXTENSION\nTime: 53.798 ms\n(postgres@[local]:4445) [powa] &gt; create extension powa;\nCREATE EXTENSION\nTime: 98.410 ms\n(postgres@[local]:4445) [powa] &gt; \\dx\n                                     List of installed extensions\n        Name        | Version |   Schema   |                        Description                        \n--------------------+---------+------------+-----------------------------------------------------------\n btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST\n pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals\n pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering\n pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed\n plpgsql            | 1.0     | pg_catalog | PL\/pgSQL procedural language\n powa               | 3.0.0   | public     | PostgreSQL Workload Analyser-core\n(6 rows)\n<\/pre>\n<p>There are <a href=\"http:\/\/powa.readthedocs.org\/en\/latest\/powa-archivist\/configuration.html\" target=\"_blank\">some configuration parameters<\/a> that allow you to control the powa extension:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:4445) [postgres] &gt; show powa.frequency;\n powa.frequency \n----------------\n 5min\n(1 row)\n\nTime: 0.319 ms\n(postgres@[local]:4445) [postgres] &gt; show powa.retention;\n powa.retention \n----------------\n 1d\n(1 row)\n\nTime: 0.241 ms\n(postgres@[local]:4445) [postgres] &gt; show powa.database;\n powa.database \n---------------\n powa\n(1 row)\n\nTime: 0.241 ms\n(postgres@[local]:4445) [postgres] &gt; show powa.coalesce;\n powa.coalesce \n---------------\n 100\n(1 row)\n\nTime: 0.362 ms\n<\/pre>\n<p>So far for the work to be done inside the PostgreSQL instance. No we need the web interface. In general you can install the web interface anywhere. I&#8217;ll be doing it on the same host by installing the requirements first:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7: [PG3] sudo yum install python-pip python-devel\n<\/pre>\n<p>After that pip can be used to install the web interface:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7: [PG3] sudo pip install powa-web\n<\/pre>\n<p>We need to create small configuration file for the web interface:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7: [PG3] sudo echo \"servers={\n  'main': {\n    'host': 'localhost',\n    'port': '5432',\n    'database': 'powa'\n  }\n}\ncookie_secret=\\\"A_SECRET\\\" \" &gt; \/etc\/powa-web.conf\n<\/pre>\n<p>Once this is available the web interface can be started:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/powa-archivist-master\/ [PG3] powa-web\n<\/pre>\n<p>You should be able to access the interface at port 8888:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1.png\" alt=\"powa1\" width=\"1688\" height=\"440\" class=\"aligncenter size-full wp-image-5929\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa2.png\" alt=\"powa2\" width=\"1681\" height=\"620\" class=\"aligncenter size-full wp-image-5930\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa3.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa3.png\" alt=\"powa3\" width=\"1688\" height=\"615\" class=\"aligncenter size-full wp-image-5931\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa4.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa4.png\" alt=\"powa4\" width=\"1658\" height=\"889\" class=\"aligncenter size-full wp-image-5932\" \/><\/a><\/p>\n<p>After some time (you&#8217;ll need to give powa some time to collect) the dashboard will be populated:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa5.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa5.png\" alt=\"powa5\" width=\"1852\" height=\"870\" class=\"aligncenter size-full wp-image-5934\" \/><\/a><\/p>\n<p>If you select a database you can scroll down to the list of sql statements:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa6.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa6.png\" alt=\"powa6\" width=\"1804\" height=\"533\" class=\"aligncenter size-full wp-image-5935\" \/><\/a><\/p>\n<p>Clicking on one of these gives nice graphs (the following are all graphs for one statement):<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa7.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa7.png\" alt=\"powa7\" width=\"1801\" height=\"373\" class=\"aligncenter size-full wp-image-5936\" \/><\/a><br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa8.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa8.png\" alt=\"powa8\" width=\"1795\" height=\"509\" class=\"aligncenter size-full wp-image-5937\" \/><\/a><br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa9.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa9.png\" alt=\"powa9\" width=\"1791\" height=\"524\" class=\"aligncenter size-full wp-image-5938\" \/><\/a><br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa10.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa10.png\" alt=\"powa10\" width=\"1795\" height=\"507\" class=\"aligncenter size-full wp-image-5939\" \/><\/a><br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa11.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa11.png\" alt=\"powa11\" width=\"1789\" height=\"504\" class=\"aligncenter size-full wp-image-5940\" \/><\/a><\/p>\n<p>Conclusion: POWA is a very nice tool for gathering and displaying statistics around a PostgreSQL instance. Especially that you can store all the statistics in a separate database and can control on how long you want to keep them makes it a very good choice. Traveling back in time to troubleshoot issues becomes very easy.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the&hellip;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":6447,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[143,98],"class_list":["post-6435","post","type-post","status-publish","format-standard","has-post-thumbnail","category-database","tag-monitoring","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.9) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Monitoring tools for PostgreSQL \u2013 POWA - dbi services Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Monitoring tools for PostgreSQL \u2013 POWA\" \/>\n<meta property=\"og:description\" content=\"The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-09T05:44:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1688\" \/>\n\t<meta property=\"og:image:height\" content=\"440\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"Monitoring tools for PostgreSQL \u2013 POWA\",\"datePublished\":\"2015-12-09T05:44:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/\"},\"wordCount\":461,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/powa1-1.png\",\"keywords\":[\"Monitoring\",\"SQL\"],\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/\",\"name\":\"Monitoring tools for PostgreSQL \u2013 POWA - dbi services Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/powa1-1.png\",\"datePublished\":\"2015-12-09T05:44:07+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/powa1-1.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/powa1-1.png\",\"width\":1688,\"height\":440},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Monitoring tools for PostgreSQL \u2013 POWA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\",\"name\":\"dbi services\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialized in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Monitoring tools for PostgreSQL \u2013 POWA - dbi services Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/","og_locale":"en_US","og_type":"article","og_title":"Monitoring tools for PostgreSQL \u2013 POWA","og_description":"The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/","og_site_name":"dbi services Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2015-12-09T05:44:07+00:00","og_image":[{"width":1688,"height":440,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1-1.png","type":"image\/png"}],"author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"Monitoring tools for PostgreSQL \u2013 POWA","datePublished":"2015-12-09T05:44:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/"},"wordCount":461,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/#organization"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1-1.png","keywords":["Monitoring","SQL"],"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/","url":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/","name":"Monitoring tools for PostgreSQL \u2013 POWA - dbi services Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1-1.png","datePublished":"2015-12-09T05:44:07+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/powa1-1.png","width":1688,"height":440},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Monitoring tools for PostgreSQL \u2013 POWA"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/#organization","name":"dbi services","url":"https:\/\/www.dbi-services.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialized in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6435"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6435\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/6447"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}