PoWA on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and configuration of PoWA on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

PoWA, the PostgreSQL Workload Analyzer, is a workload analysis platform for PostgreSQL. A set of statistics collection extensions record what the server is doing, a background worker snapshots them on an interval into a repository database, and a web interface turns those snapshots into time series charts and ranked query lists.

Because PoWA stores a history rather than a single instantaneous reading, it answers the question that a live statistics view cannot: what changed, and when. pg_stat_statements can tell you which query has used the most time since the counters were last reset. PoWA can tell you that the same query was fine all week and then tripled its runtime at 14:20 on Thursday.

This image bundles the complete appliance on a single virtual machine:

• powa-archivist 5.2.0 — the repository schema and the snapshot background worker
• powa-web 5.2.0 — the web interface, served through nginx on TCP 80
• PostgreSQL 18 — configured as the first monitored server, so the appliance analyses a real database from the moment it starts
• The full statistics stack — pg_stat_statements, pg_qualstats, pg_stat_kcache, pg_wait_sampling, pg_track_settings and hypopg
• A dedicated data disk — the repository lives on its own 32 GiB volume, so retention can be widened by resizing storage rather than rebuilding

The three component versions, the services as a customer virtual machine runs them, and the dedicated volume the repository lives on.

The appliance is standalone and self demonstrating. PoWA registers the PostgreSQL instance it is installed in as server id 0, the <local> server, and the background worker snapshots that instance with no collector, no remote server and no database of yours required. You get a working analyzer on day one and attach your own servers when you are ready (Step 9 and Step 10).

What is included:

• PoWA 5.2.0, archivist and web interface as a matched pair, under the PostgreSQL licence
• PostgreSQL 18 from the PGDG repository, so the database and every extension keep receiving upstream security updates
• A first boot service that creates the administrator role and the session signing key uniquely per virtual machine
• A shipped demo workload tool, so collection can be proven end to end in about a minute

Every statistics source PoWA consumes, installed in the repository database, and the preload order that pg_stat_kcache requires.

Security model

PoWA can read every query text that runs on the servers it watches, so the interface is treated as privileged:

• Only TCP 80 (the web interface) and TCP 22 (SSH) are reachable from the network. PostgreSQL binds 127.0.0.1:5432 and powa-web binds 127.0.0.1:8888, so nginx is the single front door.
• The interface has no account store of its own. The login form's username and password are used to open a PostgreSQL connection, so the login credential is a database role. There is no application level default account that could be inherited from the image.
• No usable credential ships in the image. The powa_ui role does not exist in the captured image at all: it is created on each virtual machine at first boot with a password generated on that machine, and written to a 0600 root:root file. The session signing key is minted at the same moment, so a captured image cannot even forge a session cookie.
• powa-web cannot start before first boot has finished. Its unit is gated on a bootstrap marker that first boot writes only after the credential exists, so it can never come up against a placeholder key with no administrator role in existence.
• PostgreSQL's own postgres role is left as the packaging leaves it: no password, peer authentication over the local socket only.
• PoWA's own predefined roles are group roles, not logins. Creating the powa extension defines powa_admin, powa_snapshot, powa_signal_backend, powa_write_all_data, powa_read_all_data and powa_read_all_metrics so the repository can be shared with least privilege. All six ship exactly as the extension creates them: NOLOGIN, no password. The appliance's login role is deliberately named powa_ui so it never collides with them.

Prerequisites

• An Azure subscription with permission to create virtual machines
• An SSH key pair for administrative access
• A network security group allowing inbound TCP 22 (SSH) and 80 (PoWA web interface) from your own address ranges
• Recommended size: Standard_B2s (2 vCPU, 4 GiB) for the appliance analysing itself and a small number of remote servers. Widen the size when you attach busy servers, because retention and query cardinality are what drive both memory and disk.

Step 1: Deploy from the Azure Portal

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

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

Step 2: Connect and wait for first boot

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

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

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

Step 3: Retrieve your credentials

The administrator credential for this machine is written to a root only file:

sudo cat /root/powa-credentials.txt

You will see 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.

Step 4: Sign in to the interface

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 signing in you land on the list of monitored servers. The <local> entry is the appliance's own PostgreSQL instance, and it is already being analysed.

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

Step 5: Confirm collection is running

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

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"

You can also read back the collection policy this image ships with:

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.

Step 6: Generate a demo workload

A brand new appliance has very little to chart, because PoWA plots the difference between snapshots and a quiet machine produces nearly identical ones. The image ships a tool that creates a small powa_demo database, drives a real transactional workload against it and forces a snapshot on each side of it, 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, sudo powa-demo-workload.sh --drop removes the demo database.

Reload the interface afterwards and the charts will have data in them.

Step 7: Read the dashboards

Open the <local> server and you land on General Overview. This is the appliance's main view: 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, all plotted over the selected time range.

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

The tabs across the top move 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 is what every chart on the page obeys.

Step 8: Find the queries that cost the most

Choose a database from the breadcrumb at the top and scroll to Details for all queries. This is the ranked list PoWA exists to produce: every statement it 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, which 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 pg_qualstats captured, proposes candidate indexes, and uses hypopg to cost each one against the real planner without building it. Suggestions therefore arrive with an estimated gain attached rather than as a guess. Click Optimize this database to run it.

Step 9: Analyse your own database

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> and run CREATE EXTENSION pg_stat_statements; followed by CREATE EXTENSION hypopg;.

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

Step 10: Attach a remote PostgreSQL server

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

On the remote server, add powa to shared_preload_libraries alongside the other collection extensions, restart it, and create the same extension set in a database there. Then register it 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 this image already trusts. Full details are in the PoWA remote setup documentation.

Step 11: Tune retention and snapshot frequency

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

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

Because the repository sits on its own volume, the other lever is storage: resize the data disk in the Azure Portal, then grow the filesystem, rather than trading away history.

Step 12: Reach PostgreSQL directly

PostgreSQL is bound to the loopback interface, which is deliberate: the repository holds the text of every query PoWA has seen. To use a local client against it, 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.

Troubleshooting

The interface returns 502. powa-web is not running. Check it with sudo systemctl status powa-web.service. On a machine that has just started, first boot may still be in progress; the unit deliberately refuses to start until first boot has written /var/lib/cloudimg/powa-bootstrap-ready.

The sign in form comes back with "Auth failed". The credential is a PostgreSQL role, so this is a database authentication failure. Re read /root/powa-credentials.txt, and confirm the Server field is set to main. To prove the shipped credential end to end, run sudo powa-cred-roundtrip.sh, which asserts a real login, an authenticated data request, and that a deliberately wrong password is refused.

The interface reports a version mismatch. powa-web refuses to sign you in when its own version and the powa extension version differ in major or minor. This image ships them as a matched 5.2.0 pair. If you have upgraded one half, upgrade the other, and remember that the extension version the interface checks is the one recorded in the database, so an upgraded package still requires ALTER EXTENSION powa UPDATE.

The charts are empty. PoWA plots the difference between snapshots, so it needs at least two of them across a period with activity. Confirm snapshots are accumulating with the queries in Step 5, then generate activity as in Step 6. If powa_snapshot_metas is not growing at all, check that powa is still in shared_preload_libraries and that the background worker started.

First boot did not finish. Read its log with sudo journalctl -u powa-firstboot.service. The unit is a one shot that is gated on /var/lib/cloudimg/powa-firstboot.done, so it will not silently re run over a partially configured machine.

Support

This image is published and supported by cloudimg. For deployment assistance, contact support through the cloudimg website.

PoWA is developed by the PoWA team and is distributed under the PostgreSQL licence. cloudimg is not affiliated with, sponsored by or endorsed by the PoWA team or the PostgreSQL Community Association. PostgreSQL and PoWA are used here only to identify the software this image contains.