PoWA

• In the Azure Portal, choose Create a resource and search for PoWA on Ubuntu 24.04 LTS by SecureAnyCloud.
• Select the offer and click Create.
• Choose your subscription, resource group and region.
• Set the virtual machine size to Standard_B2s or larger.
• Under Administrator account, select SSH public key and supply your key. The admin username is azureuser.
• Under Inbound port rules, allow SSH (22) and HTTP (80).
• Click Review and create, then Create.

The image includes a 32 GiB data disk that is provisioned with the virtual machine. The disk is mounted at /var/lib/powa and holds the PostgreSQL cluster, which contains the whole PoWA repository.

Once the virtual machine is running, connect over SSH from your workstation with ssh azureuser@<vm-public-ip>.

First boot creates this machine's administrator role and session signing key, starts the interface, proves that the credential works and takes the first snapshot. It normally completes within a minute of the machine reaching the running state. Confirm that it has finished:

systemctl is-enabled powa-firstboot.service test -f /var/lib/sac/data/powa-firstboot.done && echo "first boot complete" || echo "first boot still in progress"

First boot writes the administrator credential for this machine to a root-only file. Read it with:

sudo cat /root/powa-credentials.txt

The file shows the interface URL, the login URL, the administrator role name (powa_ui) and the password generated for this virtual machine.

Everything reachable from the network, the root only credentials file with the password masked, and a real authentication round trip proving the credential works and that a wrong one does not.

Open http://<vm-public-ip>/ in a browser. PoWA answers every unauthenticated request with the sign-in form.

Enter the username and password from Step 3, and leave Server set to main, which is the bundled PostgreSQL instance.

powa-web authenticates by opening a PostgreSQL connection, so the credential is a database role rather than an application account.

After you sign in, you land on the list of monitored servers. The <local> entry is the appliance's own PostgreSQL instance, and PoWA is already analysing it.

The bundled PostgreSQL 18 instance, registered as server id 0 and analysed with no further configuration.

The background worker takes a snapshot every minute. Check the repository directly with these queries:

sudo -u postgres psql -tAd powa -c "SELECT count(*) AS snapshots FROM powa_snapshot_metas WHERE srvid = 0"
sudo -u postgres psql -tAd powa -c "SELECT count(*) AS statements FROM powa_statements WHERE srvid = 0"

To read back the collection policy this image ships with, run:

sudo -u postgres psql -tAc 'SHOW powa.frequency'
sudo -u postgres psql -tAc 'SHOW powa.retention'
sudo -u postgres psql -tAc 'SHOW pg_qualstats.sample_rate'

The shipped collection policy, the local server PoWA registered for itself, and the history accumulated so far.

A new appliance has very little to chart, because PoWA plots the difference between snapshots and a quiet machine produces nearly identical ones. The image includes a tool that creates a small powa_demo database and drives a real transactional workload against it. It forces a snapshot on each side of the workload, so you can see the whole pipeline work in about a minute.

Run sudo powa-demo-workload.sh to use the default 45-second workload, or pass a duration such as sudo powa-demo-workload.sh 90. When you have finished with it, run sudo powa-demo-workload.sh --drop to remove the demo database.

Reload the interface afterwards, and the charts show data.

Open the <local> server and you land on General Overview. This is the appliance's main view. It plots the following over the selected time range: query runtime and query rate per second, block access (split into shared buffer hits, operating system cache hits and real disk reads), global connection activity, parallel query usage and backend age.

Query runtime, block access and connection activity plotted over time. The spike is the demo workload from Step 6.

The tabs across the top switch between subject areas for the same time range: WALs, JIT, Background Writer / Checkpointer, Archiver / Replication, Database activity, Database Objects, User functions, System resources, SLRU and Wait Events. The time range control at the top right sets the range for every chart on the page.

Choose a database from the breadcrumb at the top and scroll to Details for all queries. This is the ranked list that PoWA exists to produce. It lists every statement PoWA has seen, with call count, total and average execution time, blocks read, hit and dirtied, temporary block usage, input and output time, write-ahead log volume and JIT time.

Real statements ranked by what they actually cost, with the wait events they blocked on underneath.

Click any statement to open its own page, where the same metrics are plotted over time for that one query, alongside the predicates it filters on.

Below the query list, Wait events for all queries attributes lock and lightweight lock waits to the statements that suffered them. This is usually the fastest route to diagnosing contention rather than slowness.

At the bottom of the page, Index suggestions runs the wizard. It takes the predicates that pg_qualstats captured and proposes candidate indexes. It then uses hypopg to cost each candidate against the real planner without building it, so each suggestion arrives with an estimated gain attached. Click Optimize this database to run it.

To have PoWA analyse a database on this same instance, install the two extensions it needs in that database. Connect with sudo -u postgres psql -d <your-database>, then run CREATE EXTENSION pg_stat_statements; followed by CREATE EXTENSION hypopg;.

Both extensions are already present in template1, so any database you create on this appliance from now on inherits them automatically.

The appliance analyses itself out of the box. To add servers of your own, PoWA runs in remote mode. You install the statistics extensions on the server being watched, and a collector on this appliance connects out to that server and writes the snapshots into this repository.

On the remote server, add powa to shared_preload_libraries alongside the other collection extensions, restart the server, and create the same extension set in a database there. Then register the server from the Configuration page of the interface, or from SQL on the appliance with SELECT powa_register_server(hostname => '<remote-host>', port => 5432, username => '<remote-role>', password => '<remote-password>');.

Finally, install and start the collector on the appliance with sudo apt-get install -y powa-collector, and configure it against the repository. The collector is packaged in the same PGDG repository that this image already trusts. Full details are in the PoWA remote setup documentation.

This image ships a deliberately generous policy: a snapshot every minute, seven days of retention and full predicate sampling. That policy suits an appliance analysing itself and a handful of servers. It is the first thing to relax when you attach a very busy server.

The relevant settings are powa.frequency, powa.retention and pg_qualstats.sample_rate, and they live in /etc/postgresql/18/main/conf.d/10-sac-powa.conf. Edit that file and reload PostgreSQL with sudo systemctl reload postgresql@18-main. The reload applies powa.frequency and pg_qualstats.sample_rate, and powa.retention also takes effect on reload.

Because the repository sits on its own volume, you have a second option: resize the data disk in the Azure Portal and grow the filesystem, rather than trading away history.

PostgreSQL is bound to the loopback interface by design, because the repository holds the text of every query PoWA has seen. To use a local client, forward the port over SSH from your workstation with ssh -L 5432:127.0.0.1:5432 azureuser@<vm-public-ip>.

Then connect to 127.0.0.1:5432, database powa, as the powa_ui role with the password from Step 3.