{"id":11209,"date":"2019-03-29T20:42:32","date_gmt":"2019-03-29T20:42:32","guid":{"rendered":"https:\/\/dbtut.com\/?p=11209"},"modified":"2020-10-02T08:41:23","modified_gmt":"2020-10-02T08:41:23","slug":"postgresql-awr","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/","title":{"rendered":"PostgreSQL AWR"},"content":{"rendered":"

In PostgreSQL, you may need to review more than one thing to analyze database or whole cluster when you encounter a problem.<\/p>\n

For example, a performance problem has occurred. We may examine indexes, I\/O, CPU etc.<\/p>\n

We need to write different queries and review them one by one. Instead of doing these operations, we can use the pg_profile<\/strong> extension, which collects them all together and outputs them in HTML format.<\/p>\n

The pg_profile extension is based on the standard statistical views of PostgreSQL. It is similar to Oracle AWR architecture. Like Oracle, it takes snapshot at specified times and it gives you html format to interpret the statistical data between snapshots.<\/p>\n\n

Configure postgresql.conf<\/h2>\n

Before starting the installation, we need to make some configurations in postgresql.conf.<\/p>\n

To collect statistics, we need to modify the following parameters via postgresql.conf.<\/p>\n

track_activities = on\ntrack_counts = on\ntrack_io_timing = on\ntrack_functions = on<\/pre>\n
After the above steps are done, we are changing the pg_stat_statements parameters.<\/p>\n
shared_preload_libraries = 'pg_stat_statements'\npg_stat_statements.max = 1000\npg_stat_statements.track = 'top'\npg_stat_statements.save = off<\/pre>\n
Download and Install pg_profile<\/h2>\n
First, we need to download the pg_profile extension from github.<\/p>\n
https:\/\/github.com\/zubkov-andrei\/pg_profile<\/a><\/p>\n
After downloading the file to the server, go the directory you downloaded and follow the steps below.<\/p>\n
We’re running the Make command.<\/p>\n
make install<\/pre>\n
\"\"<\/p>\n
If you receive the below error while running this command, you can run this command at the end of the path parameter as follows.<\/p>\n
PATH=\/usr\/pgsql-10\/bin:$PATH\u00a0 make install<\/pre>\n\n
CREATE EXTENSION<\/h2>\n
If we run Make install and make the necessary configurations, we can connect to the Postgresql by using psql client to create extensions as follows.<\/p>\n
CREATE EXTENSION dblink;\nCREATE EXTENSION pg_stat_statements;\nCREATE SCHEMA profile;<\/pre>\n
Some objects will be created when we create the pg_profile extension. In order to create these objects in the schema we just created, we specify the schema as below when creating the extension.<\/p>\n
CREATE EXTENSION pg_profile SCHEMA profile;<\/pre>\n
You can use the following command to update the Extension.<\/p>\n
alter extension pg_profile update;<\/pre>\n
Create Crontab Job To Take Snapshot at Specified Intervals<\/h2>\n
I mentioned that pg_profile takes snapshot at specified times and interprets the statistical data between these snapshots and produces html outputs. Therefore, we need to add the snapshot functions to the crontab as follows. I’ve arranged to get a snapshot in half an hour. But you can do it for a shorter period. We open the crontab by using the command “crontab -e”, and add the following commands.<\/p>\n
*\/30 * * * *\u00a0\u00a0 psql -c 'SELECT profile.snapshot()' > \/var\/log\u00a0 2>&1\n\n*\/30 * * * *\u00a0\u00a0 psql -c 'SELECT snapshot()' > \/var\/log\u00a0 2>&1<\/pre>\n
In the command above I specified that the logs will be written in the var\/ log directory. You can change it if you want. With the help of the command below, you can get snapshot before the specified time.<\/p>\n
psql -c 'SELECT profile.snapshot()'<\/pre>\n
You can view snapshots with the help of the following command.<\/p>\n
select * from snapshot_show();<\/pre>\n
\"\"<\/p>\n
We can list snapshots taken with the help of the following command.<\/p>\n
\"\"<\/p>\n\n
Create a HTML Output<\/h2>\n
We can use the following command to create a html output. You can specify a path to create the html output in. If you do not specify a path, it creates in the directory you are in.<\/p>\n
\u00a0psql -qtc \"SELECT profile.report(1,8)\" --output awr_report_postgres_1_8.html<\/pre>\n
The output of the above script will be as follows.<\/p>\n
\"\"<\/p>\n
\u00a0<\/p>\n\n
<\/div>
<\/path><\/svg><\/i> \"Loading\"<\/p>
<\/div>","protected":false},"excerpt":{"rendered":"
In PostgreSQL, you may need to review more than one thing to analyze database or whole cluster when you encounter a problem. For example, a performance problem has occurred. We may examine indexes, I\/O, CPU etc. We need to write different queries and review them one by one. Instead of doing these operations, we can …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":366,"featured_media":11219,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[2961,2958,2960,2962,2959],"class_list":["post-11209","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres","tag-awr-report-in-postgres","tag-awr-report-in-postgresql","tag-pg_profile-postgresql","tag-postgres-awr-report","tag-postgresql-awr-report"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":11208,"today_views":0},"yoast_head":"\nPostgreSQL AWR - Database Tutorials<\/title>\n<meta name=\"description\" content=\"AWR Reports in PostgreSQL\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL AWR - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"AWR Reports in PostgreSQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-29T20:42:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-02T08:41:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png\" \/>\n\t<meta property=\"og:image:width\" content=\"770\" \/>\n\t<meta property=\"og:image:height\" content=\"393\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Faruk Erdem\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Faruk Erdem\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\"},\"author\":{\"name\":\"Faruk Erdem\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\"},\"headline\":\"PostgreSQL AWR\",\"datePublished\":\"2019-03-29T20:42:32+00:00\",\"dateModified\":\"2020-10-02T08:41:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\"},\"wordCount\":497,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png\",\"keywords\":[\"awr report in postgres\",\"awr report in postgresql\",\"pg_profile postgresql\",\"postgres awr report\",\"postgresql awr report\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\",\"name\":\"PostgreSQL AWR - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png\",\"datePublished\":\"2019-03-29T20:42:32+00:00\",\"dateModified\":\"2020-10-02T08:41:23+00:00\",\"description\":\"AWR Reports in PostgreSQL\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png\",\"width\":770,\"height\":393},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL AWR\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\",\"name\":\"Faruk Erdem\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"caption\":\"Faruk Erdem\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL AWR - Database Tutorials","description":"AWR Reports in PostgreSQL","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:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL AWR - Database Tutorials","og_description":"AWR Reports in PostgreSQL","og_url":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/","og_site_name":"Database Tutorials","article_published_time":"2019-03-29T20:42:32+00:00","article_modified_time":"2020-10-02T08:41:23+00:00","og_image":[{"width":770,"height":393,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png","type":"image\/png"}],"author":"Faruk Erdem","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Faruk Erdem","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/"},"author":{"name":"Faruk Erdem","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb"},"headline":"PostgreSQL AWR","datePublished":"2019-03-29T20:42:32+00:00","dateModified":"2020-10-02T08:41:23+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/"},"wordCount":497,"commentCount":3,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png","keywords":["awr report in postgres","awr report in postgresql","pg_profile postgresql","postgres awr report","postgresql awr report"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/","url":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/","name":"PostgreSQL AWR - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png","datePublished":"2019-03-29T20:42:32+00:00","dateModified":"2020-10-02T08:41:23+00:00","description":"AWR Reports in PostgreSQL","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/Ads\u0131z-12.png","width":770,"height":393},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/29\/postgresql-awr\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL AWR"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb","name":"Faruk Erdem","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","caption":"Faruk Erdem"},"url":"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11209","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/366"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=11209"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11209\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11219"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11209"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11209"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11209"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}