Before version 4, all the performance data collected were stored locally. This had two majors drawbacks:
- it adds a non negligeable performance cost, both when collecting data and when using the user interface
- it's not possible to collect data on hot-standby servers
With version 4, it's now possible to store the data of one or multiples servers on an external PostgreSQL database. This chapter describes how to configure such remote mode.
Only the storage part changed. Therefore, it's still mandatory to configure at least pg_stat_statements_doc
on each PostgreSQL instance, and all the other stat_extensions
you want to use. The list of extension can of course be different on each instance.
A PostgreSQL 9.4 or upward is required. Ideally, you should setup a dedicated instance for storing the PoWA performance data, especially if you want to setup more than a few remote servers.
You need to setup a dedicated database and install the latest version of powa_archivist
. The powa-archivist_installation
and powa-archivist-configuration
documentation will explain in detail how to do so.
However, please note that if you don't want to gather performance data for the repository PostgreSQL server, the shared_preload_libraries configuration and instance restart is not required anymore.
You need to configure powa_archivist
and the stat_extensions
of your choice on each remote PostgreSQL server.
powa_archivist
provides some SQL functions for that.
You most likely want to declare a remote sever using the powa_register_server function. For instance:
SELECT powa_register_server(hostname => 'myserver.domain.com',
alias => 'myserver',
password => 'mypassword',
extensions => '{pg_stat_kcache,pg_qualstats,pg_wait_sampling}');
You can consult the powa_archivist_remote_servers_configuration
page for a full documentation of the available SQL API.
Do all the required configuration as documented in powa_collector
.
Then you can check that everything is working by simply launching the collector. For instance:
./powa-collector.py
Warning
It's highly recommended to configure powa-collector as a daemon, with any facility provided by your operating system, once the initial setup and testing is finished.
Gathering of remote data will start, as described by previous configuration.
You can follow the powa_web
documentation. Obviously, in case of remote setup you only need to configure a single connection information per PoWA remote repository.
Once all those steps are finished, you should have a working remote setup for PoWA!