Quickstart

Warning

The current version of PoWA is designed for PostgreSQL 9.4 and later. If you want to use PoWA on PostgreSQL < 9.4, please use the 1.x series

The following describes the installation of the two modules of PoWA:
  • powa-archivist with the PGDG packages (RedHat/CentOS 6/7, Debian) or from the sources
  • powa-web from the PGDG packages (RedHat/CentOS 7) or with python pip

Install PoWA from packages on RHEL/CentOS

Prerequisites

PoWA must be installed on the PostgreSQL instance that you are monitoring.

We suppose that you are using the packages from the PostgreSQL Development Group (https://yum.postgresql.org/ or https://apt.postgresql.org/). For example for PostgreSQL 9.6 on CentOS 7 a cluster is installed with the following commands:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96 postgresql96-server
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl start postgresql-9.6

You will also need the PostgreSQL contrib package to provide the pg_stat_statements extension:

yum install postgresql96-contrib

On Debian, that would be:

apt-get install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6

Installation of the PostgreSQL extensions

On RedHat/CentOS, you can simply install the packages provided by the PGDG repository according to your PostgreSQL version. For example for PostgreSQL 9.6:

yum install powa_96 pg_qualstats96 pg_stat_kcache96 hypopg_96

On Debian the PoWA package exists but pg_qualstats, pg_stat_kcache and hypopg are not packaged and you will have to compile them manually as described in the next section:

apt-get install postgresql-9.6-powa

Once all extensions are installed or compiled, add the required modules to shared_preload_libraries in the postgresql.conf of your instance:

shared_preload_libraries='pg_stat_statements,powa,pg_stat_kcache,pg_qualstats'

Now restart PostgreSQL. Under RHEL/CentOS 6:

/etc/init.d/postgresql-9.6 restart

Under RHEL/CentOS 7:

systemctl restart postgresql-9.6

On Debian:

pg_ctlcluster 9.6 main restart

Log in to your PostgreSQL as a superuser and create a powa database:

CREATE DATABASE powa ;

Create the required extensions in this new database:

\c powa
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION btree_gist;
CREATE EXTENSION powa;
CREATE EXTENSION pg_qualstats;
CREATE EXTENSION pg_stat_kcache;

PoWA needs the hypopg extension in all databases of the cluster in order to check that the suggested indexes are efficient:

CREATE EXTENSION hypopg;

One last step is to create a role that has superuser privileges and is able to login to the cluster (use your own credentials):

CREATE ROLE powa SUPERUSER LOGIN PASSWORD 'astrongpassword' ;

The Web UI requires you to log in with a PostgreSQL role that has superuser privileges as only a superuser can access to the query text in PostgreSQL, PoWA follows the same principle.

PoWA is now up and running on the PostgreSQL-side. You still need to set up the Web interface in order to access your history. By default powa-archivist stores history for 1 day and takes a snapshot every 5 minutes. This default settings can be changed easily afterwards.

Install the Web UI

The RPM packages work for now only on RedHat/CentOS 7. For RedHat/CentOS 6 or Debian, see the installation through pip or the full manual installation guide.

You can install the web-client on any server you like. The only requirement is that the web-client can connect to the previously set-up PostgreSQL cluster.

If you’re setting up PoWA on another server, you have to install the PGDG repo package again. This is required to install the powa_96-web package and some dependencies.

Again, for example for PostgreSQL 9.6 on CentOS 7:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Install the powa_96-web RPM package with its dependencies:

yum install powa_96-web

Create the /etc/powa-web.conf config-file to tell the UI how to connect to your freshly installed PoWA database. Of course, change the given cookie to something from your own. For example to connect to the local instance on localhost:

servers={
  'main': {
    'host': 'localhost',
    'port': '5432',
    'database': 'powa'
  }
}
cookie_secret="SUPERSECRET_THAT_YOU_SHOULD_CHANGE"

Don’t forget to allow the web server to connect to the PostgreSQL cluster, and edit your pg_hba.conf accordingly.

Then, run powa-web:

powa-web

The Web UI is now available on port 8888, for example on http://localhost:8888/. You may have to configure your firewall to open the access to the outside. Use the role created earlier in PostgreSQL to connect to the UI.

Build and install powa-archivist from the sources

Prerequisites

You will need a compiler, the appropriate PostgreSQL development packages, and some contrib modules.

While on most installation, the contrib modules are installed with a postgresql-contrib package, if you wish to install them from source, you should note that only the following modules are required:

  • btree_gist
  • pg_stat_statements

On RedHat/CentOS:

yum install postgresql96-devel postgresql96-contrib

On Debian:

apt-get install postgresql-server-dev-9.6 postgresql-contrib-9.6

Installation

Download powa-archivist latest release:

wget https://github.com/dalibo/powa-archivist/archive/REL_3_1_0.tar.gz

A convenience script is offered to build every project that PoWA can take advantage of:

#!/bin/bash
# This script is meant to install every PostgreSQL extension compatible with
# PoWA.
wget https://github.com/dalibo/pg_qualstats/archive/1.0.2.tar.gz -O pg_qualstats-1.0.2.tar.gz
tar zxvf pg_qualstats-1.0.2.tar.gz
cd pg_qualstats-1.0.2
(make && sudo make install)  > /dev/null 2>&1
cd ..
rm pg_qualstats-1.0.2.tar.gz
rm pg_qualstats-1.0.2 -rf
wget https://github.com/dalibo/pg_stat_kcache/archive/REL2_0_3.tar.gz -O pg_stat_kcache-REL2_0_3.tar.gz
tar zxvf pg_stat_kcache-REL2_0_3.tar.gz
cd pg_stat_kcache-REL2_0_3
(make && sudo make install)  > /dev/null 2>&1
cd ..
rm pg_stat_kcache-REL2_0_3.tar.gz
rm pg_stat_kcache-REL2_0_3 -rf
(make && sudo make install)  > /dev/null 2>&1
echo ""
echo "You should add the following line to your postgresql.conf:"
echo ''
echo "shared_preload_libraries='pg_stat_statements,powa,pg_stat_kcache,pg_qualstats'"
echo ""
echo "Once done, restart your postgresql server and run the install_all.sql file"
echo "with a superuser, for example: "
echo "  psql -U postgres -f install_all.sql"

This script will ask for your super user password, provided the sudo command is available, and install powa, pg_qualstats and pg_stat_kcache for you.

Warning

This script is not intended to be run on a production server, as it will install the development version of each extension and not the latest stable release. It has been removed since the 2.0.1 release of PoWA.

Once done, you should modify your PostgreSQL configuration as mentioned by the script, putting the following line in your postgresql.conf file:

shared_preload_libraries='pg_stat_statements,powa,pg_stat_kcache,pg_qualstats'

Optionally, you can install the hypopg extension the same way from https://github.com/dalibo/hypopg/releases.

And restart your server, according to your distribution’s preferred way of doing so, for example:

Init scripts:

/etc/init.d/postgresql-9.6 restart

Debian pg_ctlcluster wrapper:

pg_ctlcluster 9.6 main restart

Systemd:

systemctl restart postgresql

The last step is to create a database dedicated to the PoWA repository, and create every extension in it. The install_all.sql file performs this task:

psql -U postgres -f install_all.sql
CREATE DATABASE
You are now connected to database "powa" as user "postgres".
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

Install powa-web anywhere

You do not have to install the GUI on the same machine your instance is running.

Prerequisites

  • The Python language, either 2.6, 2.7 or > 3
  • The Python language headers, either 2.6, 2.7 or > 3
  • The pip installer for Python. It is usually packaged as “python-pip”, for example:

Debian:

sudo apt-get install python-pip python-dev

RedHat/CentOS:

sudo yum install python-pip python-devel

Installation

To install powa-web, just issue the following comamnd:

sudo pip install powa-web

Then you’ll have to configure a config file somewhere, in one of those location:

  • /etc/powa-web.conf
  • ~/.config/powa-web.conf
  • ~/.powa-web.conf
  • ./powa-web.conf

The configuration file is a simple JSON one. Copy the following content to one of the above locations:

servers={
  'main': {
    'host': 'localhost',
    'port': '5432',
    'database': 'powa'
  }
}
cookie_secret="SUPERSECRET_THAT_YOU_SHOULD_CHANGE"

The servers key define a list of server available for connection by PoWA-web. You should ensure that the pg_hba.conf file is properly configured.

The cookie_secret is used as a key to crypt cookies between the client and the server. You should DEFINETLY not keep the default if you value your security.

Other options are described in the full documentation.

Then, run powa-web:

powa-web

The UI is now available on the 8888 port. Login with the credentials of the powa PostgreSQL user.