Installing & Configuring POWA (PostgreSQL Workload Analyzer) in Linux

POWA is another very good tool for PostgreSQL DBA. It helps analyzing the stats of queries using few extensions and in UI based format. Below are some important details how to install and configure POWA in linux and view the stats over web.

Some of the important points of POWA -

• POWA current version supports PostgreSQL version 9.4 and above but if your PostgreSQL version is less than 9.4 then there is a different version which is POWA version 1.x for it. 
• POWA Supports various extensions ( pg_stat_statement, pg_qualstat, pg_stat_kcache,hypopg).

Some of the Pre-requisite -

• POWA must be installed on same instance where PG is installed.
• Shared_preload_libraries needs to be modified ( Postgres service needs reboot).
• Contrib package needs to be installed.
• POWA user should be created which needs superuser privileges.
• POWA Web needs to be configured to view the stats.

Links from where Extensions can be downloaded which are needed for POWA -

• hypopg - https://github.com/HypoPG/hypopg/releases
• POWA - https://github.com/powa-team/
• POWA Installation - https://powa.readthedocs.io/en/latest/quickstart.html#install-powa-from-packages-on-rhel-centos
• PG_Qualstat - https://github.com/powa-team/pg_qualstats/archive/1.0.2.tar.gz
• PG STAT Kcache - https://github.com/powa-team/pg_stat_kcache/archive/REL2_1_0.tar.gz

Steps to Implement POWA:

• Install all the extension which are not available. 
• Mandatory Extensions are - "pg_stat_statements,powa,pg_stat_kcache,pg_qualstats, hypopg” in one DB and hypopg in all the DB. 
• Download the extensions with the above links.(make || make install)
• Make changes in postgresql.conf file for the parameter - shared_preload_libraries='pg_stat_statements,powa,pg_stat_kcache,pg_qualstats’ 
• Next create Database POWA and create the extensions. 
• \c to POWA

1. CREATE EXTENSION pg_stat_statements;
2. CREATE EXTENSION btree_gist;
3. CREATE EXTENSION powa;
4. CREATE EXTENSION pg_qualstats;
5. CREATE EXTENSION pg_stat_kcache;
6. CREATE EXTENSION hypopg; to all DB
7. CREATE ROLE powa SUPERUSER LOGIN PASSWORD 'astrongpassword’;

• Install powa-web . 
• Dependency to install powa web are - python, python-psycopg2, python-sqlalchemy & python-tornado.
• Copy Powa Web config file to /etc/powa.conf
• Edit powa web config file with address and port of web.
• Start powa web
• Check the console from web.  

Install Powa -Web:

• wget https://pypi.io/packages/source/p/powa-web/powa-web-3.1.3.tar.gz
• tar -zxvf powa-web-3.1.3.tar.gz
• cd powa-web-3.1.3
• cp ./powa-web.conf-dist ./powa-web.conf
• ./powa-web
• Copy powa-web.conf to /etc/ and start powa web from root user. You should be able to view the web version here - http://172.18.1.9:8888/login

Output of Powa-Web config file:

us02tespd02 ~ # cat /etc/powa-web.conf
servers={
  'us02tespd02': {
    'host': 'localhost',
    'port': '5432',
    'database': 'powa'
  },
'us02tespd03': {
    'host': '172.18.1.8',
    'port': '5432',
    'database': 'powa'
  }
}

cookie_secret="SECRET_STRING"
address="172.18.1.9"
port=8888