{"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\/sites\/2\/2022\/04\/powa1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa2.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa3.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa4.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa5.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa6.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa7.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa8.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa9.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa10.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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\/sites\/2\/2022\/04\/powa11.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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;]<\/p>\n","protected":false},"author":29,"featured_media":6447,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[143,77,98],"type_dbi":[],"class_list":["post-6435","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-database-management","tag-monitoring","tag-postgresql","tag-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Monitoring tools for PostgreSQL \u2013 POWA - dbi 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;]\" \/>\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 Blog\" \/>\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\/sites\/2\/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=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/monitoring-tools-for-postgresql-powa\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/powa1-1.png\",\"keywords\":[\"Monitoring\",\"PostgreSQL\",\"SQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"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 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\\\/sites\\\/2\\\/2022\\\/04\\\/powa1-1.png\",\"datePublished\":\"2015-12-09T05:44:07+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\\\/sites\\\/2\\\/2022\\\/04\\\/powa1-1.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/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\":\"Accueil\",\"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 Blog\",\"description\":\"\",\"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\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Monitoring tools for PostgreSQL \u2013 POWA - dbi 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;]","og_url":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/","og_site_name":"dbi Blog","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\/sites\/2\/2022\/04\/powa1-1.png","type":"image\/png"}],"author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/monitoring-tools-for-postgresql-powa\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/powa1-1.png","keywords":["Monitoring","PostgreSQL","SQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"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 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\/sites\/2\/2022\/04\/powa1-1.png","datePublished":"2015-12-09T05:44:07+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/sites\/2\/2022\/04\/powa1-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/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":"Accueil","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 Blog","description":"","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":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"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"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}