Skip to content

Azure Database for PostgreSQL

Last updated

View as Markdown

Set up the collector for Azure Database for PostgreSQL flexible server. Run the collector container yourself anywhere that can reach the server on port 5432, such as an Azure VM, Azure Container Instances, or a Kubernetes cluster with the Helm chart.

With the azure_flexible provider, the collector reads the server from Azure Resource Manager, finds its read replicas, and reads the server’s Azure Monitor metrics. It connects to PostgreSQL with a password.

  1. Create a collector identity. The agent ID, tenant ID and secret come from the app, as described in What you need first.
  2. Create the database login. Use the grants on Database grants. With pg_monitor alone, the collector reports metrics and the table list, but pg_dump fails with permission denied for table, so the schema arrives without its CREATE statements.
  3. Check the server parameters. Three Azure defaults need changing:
ParameterAzure defaultSet it to
azure.extensionsemptyinclude PG_STAT_STATEMENTS
pg_stat_statements.tracknonetop or all
track_io_timingoffon

shared_preload_libraries already lists pg_stat_statements by default, so no restart is needed unless someone removed it. The other three parameters are dynamic. Read the current values first:

Terminal window
az postgres flexible-server parameter list -g <resource-group> -s <server> \
--query "[?name=='azure.extensions'||name=='shared_preload_libraries'||name=='pg_stat_statements.track'||name=='track_io_timing'].{name:name,value:value}" -o table

Azure refuses CREATE EXTENSION for any extension not listed in azure.extensions; see Microsoft’s azure.extensions. The value is a comma-separated list, so preserve the entries already in it. 4. Create the extension in the postgres database while connected as the server admin:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The collector reads statement statistics from postgres, so creating the extension only in your application database is not enough. See pg_stat_statements. 5. Give the collector’s Azure identity the Reader role on the server. The collector reads the server and its replicas before connecting, so without the role it collects nothing; see Without an Azure role. The identity is selected from the environment:

  • AKS workload identity, when AZURE_FEDERATED_TOKEN_FILE is set.
  • A service principal, when AZURE_CLIENT_SECRET is set, with AZURE_TENANT_ID and AZURE_CLIENT_ID.
  • Otherwise, the managed identity of the VM or container host. Set client_id in the provider block to select a user-assigned identity.
Terminal window
az role assignment create --assignee <identity client id> --role Reader \
--scope $(az postgres flexible-server show -g <resource-group> -n <server> --query id -o tsv)
  1. Allow the collector through the server’s firewall. The server accepts connections only from addresses allowed by its networking rules.
[dbgorilla]
agent_id = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret = "${DBG_SERVER_SECRET}"
[[component]]
name = "orders"
engine = "postgres"
[component.provider]
type = "azure_flexible"
# az postgres flexible-server show -g <resource-group> -n <server> --query id -o tsv
resource_id = "/subscriptions/<subscription>/resourceGroups/<resource-group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>"
[component.auth]
method = "password"
user = "dbg_readonly"
password = "${DBG_DB_PASSWORD}"
[component.connect]
host = "<server>.postgres.database.azure.com"
port = 5432
databases = ["orders"]
[topology]
interval = "60s"
[commands]
enabled = true
allowed = ["explain", "collect_statistics"] # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands

Point resource_id at the primary server. The collector discovers its read replicas from there.

Terminal window
docker run -d --name dbg-collector --restart unless-stopped \
-v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
--env-file secrets.env \
dbgorillapublic.azurecr.io/dbg-collector:0.16.0 \
--config-file /etc/dbg-collector/collector.toml

secrets.env holds DBG_SERVER_SECRET and DBG_DB_PASSWORD. For a service principal, add AZURE_TENANT_ID, AZURE_CLIENT_ID and AZURE_CLIENT_SECRET.

The server requires TLS: require_secure_transport is on by default, and it refuses unencrypted connections with no pg_hba.conf entry … no encryption.

Leave ssl_mode and ca_cert unset. The server’s certificate names its <server>.postgres.database.azure.com host and chains to DigiCert Global Root G2, which the collector image already trusts. To pin the root instead, download DigiCert Global Root G2, mount it, and point ca_cert at it. Microsoft lists the roots it may move to on TLS in Azure Database for PostgreSQL.

If the collector’s identity cannot read the server, discovery fails on every pass and the component is skipped. The log names the missing permission:

discovery failed and no prior topology; skipping component this pass
… ARM get flexible server "<server>" failed (403 Forbidden): … does not have authorization to perform action 'Microsoft.DBforPostgreSQL/flexibleServers/read' …

To collect from the database without Azure access, use type = "self_hosted" with the same [component.connect] block, and turn off the host-metrics probe because Azure does not answer it:

[component.provider]
type = "self_hosted"
[component.provider.node_exporter]
mode = "disabled"

The collector then sends the schema and PostgreSQL metrics. Azure Monitor metrics and replica discovery require the azure_flexible provider.

Terminal window
docker logs -f dbg-collector

A healthy start logs opamp handshake complete and discovered component. Use these messages to troubleshoot:

  • failed during probe: timed out after 10s on a self_hosted component: add the node_exporter block above.
  • ManagedIdentityCredential authentication failed. The requested identity has not been assigned to this resource: the client_id names an identity this host does not have.
  • pg_stat_statements is not queryable in the connect database: create the extension in postgres, as in Before you start.

Change the image tag in docker run and recreate the container. The collector’s identity is in collector.toml, so the new container is the same collector.

Remove the container with docker rm -f dbg-collector. The database remains in DBGorilla with the status Disconnected. To delete it, follow Removing the database from DBGorilla.

The collector’s other settings are on Collector configuration.