pgwatch

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

az group create --name pgwatch-rg --location eastus
az vm image terms accept --urn sac1647283583153:pgwatch-ubuntu-24-04:default:latest
az vm create \
  --resource-group pgwatch-rg \
  --name pgwatch-vm \
  --image sac1647283583153: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

Run these commands on your own workstation, where the Azure CLI is installed and signed in. Do not run them on the virtual machine.

Connect to the VM as the default administrative user:

ssh azureuser@<vm-ip>

Three services make up the appliance. Confirm that all three 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

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

First boot writes a unique set of passwords 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 the file as sensitive. It is mode 0600, owned by root.

The image ships an end-to-end self-test. It proves the whole pipeline, not merely that a web page loads. 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

The collector writes into the pgwatch_metrics database. Each metric becomes a table in the public schema, with a time, a 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 that 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

Open Grafana in a browser:

http://<vm-ip>:3000

Sign in with the user name admin and the ADMIN_PASSWORD value from Step 6. Every SecureAnyCloud image rejects the default admin/admin credential.

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

You can confirm the same from the command line:

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

Expected output:

22

Start with the numbered dashboards:

• 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: the slowest and most frequent statements, from pg_stat_statements
• 4. Tables Overview and 5. Table Details: table sizes, bloat, scan patterns and vacuum activity

Open 1. Global Database Overview. The appliance monitors its own bundled PostgreSQL, so the charts already show real data. They display connection counts, queries and transactions per second, and average query runtime for the local-postgres source:

The 0. Health Check dashboard 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 at intervals of 60 seconds and above.

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

View the current configuration, which contains 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. The pg_monitor role 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;

List pg_stat_statements in that server's shared_preload_libraries as well, which requires a restart of that server. Without it, the query-performance dashboards stay empty, but 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.

The file holds credentials, so keep it readable only by the collector:

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 the Overview explains, the API is bound to loopback and fenced at the kernel level because upstream's token check can be bypassed. Never publish it. 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 on your workstation 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.

The appliance runs these components:

• pgwatch 5.2.0 on 127.0.0.1:8080: the metrics collector. Its admin API is loopback-only.
• PostgreSQL 16 on 127.0.0.1:5432: the metrics store and default monitored target.
• Grafana CE 12.x on 0.0.0.0:3000: the dashboard UI and the only public listener.

Key paths:

• /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: the end-to-end self-test

Metrics are retained for 14 days by default. 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 you monitor many servers:

df -h / | tail -1

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.

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 with an Azure Application Gateway. Then restrict port 3000 to the proxy.

Set root_url under [server] in /etc/grafana/grafana.ini to the public HTTPS address so that generated links are correct.

Dashboards are empty. Give the collector a minute. Most metrics in the exhaustive preset run on 60-second intervals or slower. Confirm that 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 that the monitoring role has pg_monitor, and confirm that 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. The bundled instance already has both.

You 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 that the appliance is healthy. Run the self-test from Step 7. It reports a specific BROKEN-<reason> string when something is wrong.

• Restrict TCP 3000 to known address ranges, and put TLS in front of Grafana before you expose it.
• Never publish TCP 8080. A password cannot secure the pgwatch admin API, so use the SSH tunnel in Step 12.
• Never publish TCP 5432. The bundled PostgreSQL is a component of the appliance, not a public service.
• Change the Grafana administrator (admin) password after your first sign-in, and add named user accounts instead of sharing the admin account.
• Give each monitored server its own least-privilege pg_monitor role, and prefer sslmode=require in remote connection strings.
• The query-performance dashboards display real query text, so restrict access accordingly.
• Keep the machine patched with sudo apt-get update && sudo apt-get upgrade.

pgwatch is distributed under the BSD-3-Clause licence. Grafana Community Edition is licensed under AGPL-3.0, and PostgreSQL under the PostgreSQL Licence. The image bundles unmodified upstream software, and those licences govern your use of it.

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

SecureAnyCloud 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 SecureAnyCloud.

Need Help?

Contact SecureAnyCloud support at secureanycloud.com.

Deploy on Azure

Launch pgwatch on Ubuntu 24.04 LTS on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com