pgwatch on Ubuntu 24.04 on Azure User Guide

Overview

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

pgwatch is CYBERTEC's flagship open-source PostgreSQL monitoring stack. This image bundles the complete appliance on a single virtual machine:

• pgwatch 5.2.0 — a Go metrics collector that gathers hundreds of PostgreSQL statistics on a schedule
• PostgreSQL 16 — serving as both the metrics store and the default monitored target
• Grafana Community Edition 12.x — the dashboard UI on TCP 3000, pre-loaded with the 22 PostgreSQL dashboards that ship with pgwatch

The appliance is self-demonstrating. The bundled PostgreSQL instance is registered as pgwatch's first monitored source, so the collector begins gathering real metrics the moment the machine boots. Sign in a couple of minutes later and the dashboards are already drawing live charts — connections, transactions per second, query runtimes, cache hit ratios, WAL activity — with no configuration at all. You then point pgwatch at your own PostgreSQL servers.

What is included:

• pgwatch 5.2.0 (BSD-3-Clause), installed from the official upstream release and SHA256-verified

• PostgreSQL 16 with pg_stat_statements enabled, bound to 127.0.0.1 only

• Grafana Community Edition (pinned 12.x, held against a major-version upgrade) on TCP 3000

• All 22 bundled pgwatch dashboards plus the metrics-store datasource, provisioned automatically

• The bundled PostgreSQL registered as a monitored source, so charts have data from first boot

• Per-VM credential rotation on first boot, and a shipped end-to-end self-test

Security model

This image is secure by default, and one part of that deserves an explicit explanation.

Grafana on TCP 3000 is the only public listener. PostgreSQL (127.0.0.1:5432) and the pgwatch admin API (127.0.0.1:8080) are bound to loopback and are never exposed to the network.

The pgwatch admin Web UI / REST API is deliberately kept off the network. Upstream pgwatch compiles a fixed JWT signing key into the binary (internal/webserver/jwt.go), and its token check verifies only that signature — it never consults the configured admin username or password. Any client that knows the key, which is public in the upstream source, can therefore mint a valid admin token for any reachable pgwatch instance, and that API discloses the connection strings of every monitored database. Upstream's own documentation states that the administrative Web UI "doesn't have by default any security".

Because the key is compiled in, no password setting can secure that endpoint. This image therefore binds it to loopback and fences it at the kernel level with systemd IPAddressDeny=any / IPAddressAllow=localhost, so it stays unreachable from the network even if the process ignored its own bind address. You reach it through an SSH tunnel (Step 12). A per-VM password is still set as defence in depth.

On every fresh virtual machine, pgwatch-firstboot.service generates a unique Grafana administrator password, a unique metrics-store database password, a unique pgwatch admin password, and a fresh Grafana secret_key before Grafana or the collector ever runs. The default admin/admin login is rejected, and no credential is baked into the image. A fail-closed guard runs before the public listener starts and refuses to start it if any secret still matches a known upstream default.

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 TCP 3000 (Grafana) from your address ranges
• Recommended size: Standard_B2s (2 vCPU, 4 GB RAM) or larger

Monitoring many databases, or retaining metrics for long periods, benefits from more memory and disk — see Step 13.

Step 1: Deploy from the Azure Portal

1. In the Azure Portal, search the Marketplace for pgwatch on Ubuntu 24.04 LTS and select the cloudimg offering.
2. Choose Create, then select your subscription, resource group, and region.
3. Set the virtual machine size to Standard_B2s or larger.
4. Under Administrator account, choose SSH public key and supply your key. The default administrative user is azureuser.
5. Under Inbound port rules, allow SSH (22) and HTTP (3000).
6. Review and create.

Step 2: Deploy from the Azure CLI

az group create --name pgwatch-rg --location eastus

az vm create \
  --resource-group pgwatch-rg \
  --name pgwatch-vm \
  --image cloudimg:pgwatch-ubuntu-24-04:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

az vm open-port --resource-group pgwatch-rg --name pgwatch-vm --port 3000 --priority 1001

# Retrieve the public IP address of the new virtual machine
az vm list-ip-addresses \
  --resource-group pgwatch-rg \
  --name pgwatch-vm \
  --query "[0].virtualMachine.network.publicIpAddresses[0].ipAddress" \
  --output tsv

These commands run on your own workstation, where the Azure CLI is installed and signed in — not on the virtual machine.

Step 3: Connect via SSH

ssh azureuser@<vm-ip>

Step 4: Verify the Services

Three services make up the appliance. All three should report active:

sudo systemctl is-active postgresql pgwatch grafana-server

Expected output:

active
active
active

Confirm the pgwatch release:

sudo /usr/bin/pgwatch --help 2>&1 | sed -n '2,6p'

Expected output:

Version info:
  Version:       5.2.0
  Config Schema: 00824
  Sink Schema:   01180
  Git Commit:    8a90e89a5bfddc216a1500d54b67b0a4529157db

Step 5: Confirm Only Grafana Is Exposed

PostgreSQL and the pgwatch admin API must be bound to loopback; only Grafana listens publicly:

sudo ss -tlnH | awk '{print $4}' | grep -E ':(3000|5432|8080)$' | sort

Expected output:

*:3000
127.0.0.1:5432
127.0.0.1:8080

Step 6: Retrieve the Admin Password

First boot writes a unique password set to a root-only file:

sudo cat /stage/scripts/pgwatch-credentials.log

The file contains the Grafana sign-in details, the metrics-store database credentials, and the loopback-only pgwatch admin credentials. Treat it as sensitive — it is mode 0600, owned by root.

Step 7: Run the Shipped Self-Test

The image ships an end-to-end self-test. It proves the whole pipeline — not merely that a web page loads — and you can run it at any time:

sudo /usr/local/sbin/pgwatch-selftest.sh

It checks that Grafana answers, that the per-VM password works while admin/admin is rejected, that upstream's published database default is rejected, that the admin API is unreachable off-loopback, that metrics have actually been collected and are fresh, and that datapoints come back through Grafana's datasource — the same path the dashboards use.

Expected output (a single OK line):

OK pgwatch grafana-200 + default-admin-401 + perVM-200 + upstream-db-default-rejected + admin-api-loopback-only + 19 metric rows (40s fresh) + 19 datapoints through the Grafana datasource

Step 8: Inspect the Metrics Store

The collector writes into the pgwatch_metrics database. Each metric becomes a table in the public schema, with a time, dbname, and a JSONB data payload.

List the metrics being collected:

sudo -u postgres psql -d pgwatch_metrics -c "SELECT dbname, metric FROM admin.all_distinct_dbname_metrics ORDER BY 2 LIMIT 8"

Expected output:

     dbname     |         metric         
----------------+------------------------
 local-postgres | archiver
 local-postgres | archiver_pending_count
 local-postgres | backends
 local-postgres | bgwriter
 local-postgres | change_events
 local-postgres | checkpointer
 local-postgres | cpu_load
 local-postgres | datfrozenxid
(8 rows)

Confirm rows are arriving and are recent:

sudo -u postgres psql -d pgwatch_metrics -c "SELECT count(*) AS rows, max(time) AS newest FROM public.db_stats"

Expected output:

 rows |            newest             
------+-------------------------------
   25 | 2026-07-26 10:27:32.880017+00

Step 9: First Login

Open Grafana in a browser:

http://<vm-ip>:3000

Sign in with the user name admin and the ADMIN_PASSWORD value from Step 6. The default admin/admin credential is rejected on every cloudimg image.

Step 10: The Bundled Dashboard Library

Choose Dashboards and open the pgwatch folder. All 22 dashboards that ship with pgwatch are pre-provisioned and already bound to the metrics store:

Confirm the same from the command line:

ls -1 /var/lib/grafana/dashboards/pgwatch/*.json | wc -l

Expected output:

22

The numbered dashboards are the recommended starting points:

• 0. Health Check — an at-a-glance status board for a single instance
• 1. Global Database Overview — connections, QPS, TPS, and query runtimes across all monitored databases
• 2. Database Overview — a deeper single-database view
• 3. Query Performance Analysis — slowest and most frequent statements, from pg_stat_statements
• 4. Tables Overview and 5. Table Details — table sizes, bloat, scan patterns, and vacuum activity

Step 11: Live Data From First Boot

Open 1. Global Database Overview. Because the appliance monitors its own bundled PostgreSQL, the charts are already drawing real data — here showing connection counts, queries and transactions per second, and average query runtime for the local-postgres source:

0. Health Check gives the single-instance summary — instance state, uptime, server version, connection usage, cache hit ratio, transaction rollback ratio, database size, and WAL volume:

If a panel is empty immediately after deployment, wait a minute and refresh — the slower metrics in the exhaustive preset are collected on intervals of 60 seconds and above.

Step 12: Monitor Your Own PostgreSQL Servers

This is the main task after deployment. Monitored servers are listed in /etc/pgwatch/sources.yaml.

View the current configuration (the bundled self-monitoring entry):

sudo sed -E 's|(postgresql://pgwatch:)[^@]+|\1<per-vm-password>|' /etc/pgwatch/sources.yaml | grep -vE '^\s*#' | grep -v '^$'

Expected output:

- name: local-postgres
  kind: postgres
  conn_str: postgresql://pgwatch:<per-vm-password>@127.0.0.1:5432/postgres
  preset_metrics: exhaustive
  is_enabled: true
  group: default

Prepare the database you want to monitor

On the target PostgreSQL server, create a monitoring role. pg_monitor grants read access to the statistics views without granting access to your data:

CREATE ROLE pgwatch WITH LOGIN PASSWORD 'a-strong-password';
GRANT pg_monitor TO pgwatch;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements must also be listed in that server's shared_preload_libraries, which requires a restart of that server. Without it, the query-performance dashboards stay empty; the rest still work.

Register it with pgwatch

Append an entry to /etc/pgwatch/sources.yaml:

- name: prod-db-01
  kind: postgres
  conn_str: postgresql://pgwatch:a-strong-password@db01.example.internal:5432/postgres
  preset_metrics: exhaustive
  is_enabled: true
  group: default

Useful values for kind are postgres, postgres-continuous-discovery (automatically discovers every database on the instance), pgbouncer, pgpool, and patroni. Useful values for preset_metrics are minimal, basic, standard, exhaustive, and full.

Keep the file readable only by the collector, since it holds credentials:

sudo chown pgwatch:pgwatch /etc/pgwatch/sources.yaml && sudo chmod 0600 /etc/pgwatch/sources.yaml

Then reload the collector:

sudo systemctl restart pgwatch

pgwatch re-reads its sources every 120 seconds, so a restart is optional — but it applies the change immediately. The new server appears as an additional value in the dbname selector at the top of each dashboard.

Reaching the pgwatch admin API

pgwatch also has a REST API and Web UI on port 8080 for managing sources and metrics. As explained in the Overview, it is bound to loopback and kernel-fenced because upstream's token check is bypassable, so it must never be published. Reach it from your workstation with an SSH tunnel:

ssh -L 8080:127.0.0.1:8080 azureuser@<vm-ip>

Then browse to http://127.0.0.1:8080 locally and sign in with the PGWATCH_WEB_USER and PGWATCH_WEB_PASSWORD values from Step 6. Editing sources.yaml directly, as above, is the recommended approach.

Step 13: Server Components

Component	Version	Listens on	Purpose
pgwatch	5.2.0	127.0.0.1:8080	Metrics collector; admin API is loopback-only
PostgreSQL	16	127.0.0.1:5432	Metrics store and default monitored target
Grafana CE	12.x	0.0.0.0:3000	Dashboard UI — the only public listener

Key paths:

Path	Purpose
/etc/pgwatch/sources.yaml	Monitored servers (0600, owned by pgwatch)
/etc/pgwatch/pgwatch.env	Collector configuration and secrets (0600)
/etc/pgwatch/metrics.yaml	Metric definitions and presets
/var/lib/grafana/dashboards/pgwatch/	The 22 bundled dashboards
/stage/scripts/pgwatch-credentials.log	Per-VM credentials (0600, root only)
/usr/local/sbin/pgwatch-selftest.sh	End-to-end self-test

Metrics are retained for 14 days by default, and PostgreSQL stores them in weekly partitions that are dropped automatically. To change the retention window, edit PW_RETENTION in /etc/pgwatch/pgwatch.env and restart the collector.

Check disk headroom before monitoring many servers:

df -h / | tail -1

Step 14: Managing the Services

Check status:

sudo systemctl status pgwatch --no-pager | head -12

View collector logs:

sudo journalctl -u pgwatch --no-pager -n 20

Restart components:

sudo systemctl restart pgwatch

The collector, the metrics store, and Grafana all start automatically on boot.

Step 15: Put TLS in Front (Production)

Grafana serves plain HTTP on port 3000. For production, terminate TLS in front of it — for example with nginx or Caddy on the same VM, or an Azure Application Gateway — and restrict port 3000 to the proxy. Set root_url under [server] in /etc/grafana/grafana.ini to the public HTTPS address so generated links are correct.

Step 16: Troubleshooting

Dashboards are empty. Give the collector a minute; most metrics in the exhaustive preset run on 60-second intervals or slower. Confirm rows are arriving with the query in Step 8, then check sudo journalctl -u pgwatch -n 50 for connection errors to the monitored server.

A newly added server does not appear. Check the collector log for authentication or network failures, verify the monitoring role has pg_monitor, and confirm the target's pg_hba.conf and firewall permit the connection from this VM.

Query performance dashboards are empty. The monitored server needs pg_stat_statements in shared_preload_libraries and the extension created in the database. This is already done for the bundled instance.

Cannot sign in to Grafana. Confirm the password with the Step 6 command. After several failed attempts Grafana temporarily blocks the account; wait a few minutes and retry.

Confirming the appliance is healthy. Run the self-test from Step 7 — it reports a specific BROKEN-<reason> string when something is wrong.

Step 17: Security Recommendations

• Restrict TCP 3000 to known address ranges, and put TLS in front of Grafana before exposing it
• Never publish TCP 8080; the pgwatch admin API cannot be secured by a password, so use the SSH tunnel in Step 12
• Never publish TCP 5432; the bundled PostgreSQL is an internal component
• Change the Grafana administrator password after first sign-in, and add named user accounts rather than sharing admin
• Give each monitored server its own least-privilege pg_monitor role, and prefer sslmode=require in remote connection strings
• Note that the query-performance dashboards display real query text; restrict access accordingly
• Keep the machine patched with sudo apt-get update && sudo apt-get upgrade

Step 18: Support and Licensing

pgwatch is distributed under the BSD-3-Clause licence. Grafana Community Edition is licensed under AGPL-3.0, and PostgreSQL under the PostgreSQL Licence. This image bundles unmodified upstream software; your use is governed by those licences.

• pgwatch documentation: pgwat.ch
• pgwatch source: github.com/cybertec-postgresql/pgwatch

cloudimg provides 24/7 support for image deployment and configuration.

Deploy on Azure

Find pgwatch on Ubuntu 24.04 LTS in the Azure Marketplace, published by cloudimg.

Need Help?

Contact cloudimg support at cloudimg.co.uk.