Deploying PoWA on a Self-Managed PostgreSQL Instance

Preparations

There is a self-managed PostgreSQL instance:

• Version: PostgreSQL 12.6
• Administrator account: postgres
• PostgreSQL dedicated storage database: powa-repository
• Data path: /home/postgres/data

Deploying PoWA

1. Add pg_stat_statements to shared_preload_libraries in the /home/postgres/data/postgresql.conf file.

   

2. Restart the database.

   pg_ctl restart -D /home/postgres/data/

3. Log in to the database as the postgres user, create database powa, and install related extensions.
   

   The created database must be named powa. Otherwise, an error is reported and certain functions do not take effect while PoWA is running.

   [postgres@ecs-ad4d ~]$ psql -U postgres -d postgres
   psql (12.6)
   Type "help" for help.
   postgres=# create database powa;
   CREATE DATABASE
   postgres=# \c powa
   You are now connected to database "powa" as user "postgres".
   powa=# create extension pg_stat_statements ;
   CREATE EXTENSION
   powa=# create extension btree_gist ;
   CREATE EXTENSION
   powa=# create extension powa;
   CREATE EXTENSION

4. Configure the instance whose performance metrics need to be collected.
   1. Add the instance information.

      powa=# select powa_register_server(
      	hostname => '192.168.0.1',
      	alias => 'myInstance',
      	port => 5432,
      	username => 'user1',
      	password => '**********',
      	frequency => 300);
      	 powa_register_server
      	----------------------
      	 t
      	(1 row)

   2. Obtain the information about the target instance from the powa_servers table.

      powa=# select * from powa_servers;
      id | hostname | alias | port | username |  password  | dbname | frequency | powa_coalesce | retention | allow_ui_connection |version
      ----+---------------+------------+------+----------+------------+--------+-----------+---------------+-----------+------------------
      0 |           | <local>|  0 |          |            |        |        -1 |   100         | 00:00:00  | t           |
      1 | 192.168.0.1 | myInstance | user1 | 5432     | ********** | powa   |       300 |           100 | 1 day       | t             |
      (2 rows)

      

      The preceding operations involve important privacy information such as the IP address, root account, and plaintext password of the target instance.

      Before using this extension, assess its security risks.

Configuring PoWA-collector

Start the PoWA-collector.

cd /home/postgres/.local/bin
./powa-collector.py &

When PoWA-collector starts, it searches for configuration files in the following sequence:

1. /etc/powa-collector.conf
2. ~/.config/powa-collector.conf
3. ~/.powa-collector.conf
4. ./powa-collector.conf

The configuration files must contain the following options:

• repository.dsn: URL. It is used to notify PoWA-collector of how to connect to the dedicated storage database (powa-repository).
• debug: Boolean type. It specifies whether to enable PoWA-collector in debugging mode.

Take the configuration file ./powa-collector.conf as an example.

{
    "repository": {
	"dsn": "postgresql://postgres@localhost:5432/powa"
	},
	"debug": true
}

Configuring PoWA-web

Start the PoWA-web.

cd /home/postgres/.local/bin
./powa-web &

When PoWA-web starts, it searches for configuration files in the following sequence:

1. /etc/powa-web.conf
2. ~/.config/powa-web.conf
3. ~/.powa-web.conf
4. ./powa-web.conf

Take the configuration file ./powa-web.conf as an example.

# cd /home/postgres/.local/bin
# vim ./powa-web.conf
# Write the configuration information and save it.
servers={
	  'main': {
	    'host': 'localhost',
	    'port': '5432',
	    'database': 'powa',
	    'username': 'postgres',
	    'query': {'client_encoding': 'utf8'}
	  }
}
cookie_secret="SECRET_STRING"

In this section, the connection policy in the pg_hab.conf file of the powa-repository database is set to trust (password-free connection). Therefore, the password is not configured.