Pganalyze is used to tracking logs and slow queries. Pganalyze has feature of performance monitoring, user and roles privileges setup, logs and insights.<\/p>\n\n\n\n
a. Install the postgresql server with contributions setup using below commands in root user,<\/p>\n\n\n\n
$ sudo apt-get install postgresql postgresql-contrib<\/pre>\n\n\n\nb. Add this extension code at end of the file \/etc\/postgresql\/postgresql.conf as below,<\/p>\n\n\n\n
shared_preload_libraries = 'pg_stat_statements'\n\n# Increase the max size of the query strings Postgres records\n\ntrack_activity_query_size = 2048\n\n# Track statements generated by stored procedures as well\n\npg_stat_statements.track = all<\/pre>\n\n\n\nc. Restart the postgresql service by below commands,<\/p>\n\n\n\n
$ sudo service postgresql restart<\/pre>\n\n\n\n2. Installation and configuration of pganalyze-collector :<\/h3>\n\n\n\n
a. First, curl command the website of pganalyze-collector keys as below,<\/p>\n\n\n\n
curl -L packages.pganalyze.com\/pganalyze_signing_key.asc | apt-key add -<\/pre>\n\n\n\nb. And save the debian file in repository list,<\/p>\n\n\n\n
echo \"deb [arch=amd64] packages.pganalyze.com\/ubuntu\/trusty\/ stable main\" > \/etc\/apt\/sources.list.d\/pganalyze_collector.list<\/pre>\n\n\n\nc. Update the system repository and install pganalyze-collector as below commands,<\/p>\n\n\n\n
apt-get update\n\napt-get install pganalyze-collector<\/pre>\n\n\n\nd. Edit \/etc\/pganalyze-collector.conf and enter API key from the site https:\/\/app.pganalyze.com\/users\/sign_in (sign up and sign in before proceeding with this step)<\/p>\n\n\n\n
[pganalyze]\n\napi_key: NMQD47CHU6V3HGKA<\/pre>\n\n\n\ndb_name: postgres\n\ndb_username: postgres\n\ndb_password: password\n\ndb_host: localhost\n\ndb_port: 5432\n<\/p>\n\n\n\n
e. Test the collector first and then you’ll now need to reload the running pganalyze collector process, so it picks up the new configuration,<\/p>\n\n\n\n
$ pganalyze-collector \u2013test<\/pre>\n\n\n\nf. I [default] Test submission successful (1010 KB received) – proceed running the collector as a daemon<\/p>\n\n\n\n
$ systemctl reload pganalyze-collector<\/pre>\n\n\n\nWait for 20 Minutes, postgresql server will integrated in pganalyze through pganalyze-collector as shown above.<\/p>\n\n\n\n
3. Configuring Query Performance Monitoring and Log insights:<\/h3>\n\n\n\n
a. First, upgrade your system and pganalyze-collector if requires,<\/p>\n\n\n\n
$ sudo apt-get upgrade pganalyze-collector<\/pre>\n\n\n\nb. Edit \/etc\/pganalyze-collector.conf for query entry in server and log file for log insights,<\/p>\n\n\n\n
[server1]\n\ndb_name: postgres, *\n\ndb_username: postgres\n\ndb_password: password\n\ndb_host: localhost\n\ndb_port: 5432\n\ndb_log_location: \/var\/log\/postgresql\/postgresql-9.3-main.log<\/pre>\n\n\n\nc. We provide a helper for discovering the log directory, which you can run like this as root,<\/p>\n\n\n\n
$ pganalyze-collector \u2013discover-log-location\n\n2018\/12\/05 11:51:30 I [server1] Found log location, add this to your pganalyze-collector.conf in the [server1] section:\n\ndb_log_location = \/var\/log\/postgresql\/postgresql-9.3-main.log<\/pre>\n\n\n\nd. Now we can use the –test option of the collector to verify that log collection and parsing works,<\/p>\n\n\n\n
$ pganalyze-collector \u2013test\n\n2018\/12\/05 11:40:06 I [server1] Testing statistics collection...\n2018\/12\/05 11:40:07 I [server1] Test submission successful (15.8 KB received)\n2018\/12\/05 11:40:07 I [server1] Testing local log tailing...\n2018\/12\/05 11:40:13 I [server1] Log test successful\n2018\/12\/05 11:40:13 I Re-running log test with reduced privileges of \"pganalyze\" user (uid = 107, gid = 113)\n2018\/12\/05 11:40:13 I [server1] Testing local log tailing...\n2018\/12\/05 11:40:19 I [server1] Log test successful<\/pre>\n","protected":false},"excerpt":{"rendered":"Pganalyze is a monitoring system for PostgreSQL databases. It processes statistics data available from the built-in Postgres statistics views, as well as additional system level data like CPU and I\/O performance metrics.<\/p>\n","protected":false},"author":23,"featured_media":2131,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[424],"tags":[553,27],"class_list":["post-2110","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pentaho","tag-pganalyze","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/2110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/users\/23"}],"replies":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/comments?post=2110"}],"version-history":[{"count":0,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/2110\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/media\/2131"}],"wp:attachment":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/media?parent=2110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/categories?post=2110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/tags?post=2110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}