Configuring Remote Deployment

Configuring a DB Instance for Performance Metric Collection

1. Log in to the powa database of the target DB instance as user root. (If the powa database does not exist, create it first.)
2. Create the powa plug-in in the powa database.

   select control_extension('create', 'pg_stat_statements');

   select control_extension('create', 'btree_gist');

   select control_extension('create', 'powa');

Configuring Local PostgreSQL

Take the PostgreSQL (powa-repository) on the ECS as an example.

• Version: PostgreSQL 12.6
• superuser: postgres
• Data path: /home/postgres/data

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 super user, create database powa, and install related plug-ins.
   

   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. View the powa_servers table to obtain the performance metric information of the instance.

      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)

      

      Information about the destination instance information includes important privacy information, such as its IP address, root user account, and plaintext password.

      Assess the plug-in security risks before you decide to use them.

Configuring PoWA-collector

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 the powa-collector of how to connect to the dedicated storage database (powa-repository).
• debug: Boolean type. It specifies whether to enable the 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

When PoWA-collector 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 example, 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.

Start the powa-web.

cd /home/postgres/.local/bin

./powa-web &