Azure Database for PostgreSQL
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.
Before you start
Section titled “Before you start”- Create a collector identity. The agent ID, tenant ID and secret come from the app, as described in What you need first.
- Create the database login. Use the grants on
Database grants. With
pg_monitoralone, the collector reports metrics and the table list, butpg_dumpfails withpermission denied for table, so the schema arrives without itsCREATEstatements. - Check the server parameters. Three Azure defaults need changing:
| Parameter | Azure default | Set it to |
|---|---|---|
azure.extensions | empty | include PG_STAT_STATEMENTS |
pg_stat_statements.track | none | top or all |
track_io_timing | off | on |
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:
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 tableAzure 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_FILEis set. - A service principal, when
AZURE_CLIENT_SECRETis set, withAZURE_TENANT_IDandAZURE_CLIENT_ID. - Otherwise, the managed identity of the VM or container host. Set
client_idin the provider block to select a user-assigned identity.
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)- Allow the collector through the server’s firewall. The server accepts connections only from addresses allowed by its networking rules.
Install
Section titled “Install”The config
Section titled “The config”[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 tsvresource_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 = 5432databases = ["orders"]
[topology]interval = "60s"
[commands]enabled = trueallowed = ["explain", "collect_statistics"] # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commandsPoint resource_id at the primary server. The collector discovers its read replicas from there.
Run it
Section titled “Run it”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.tomlsecrets.env holds DBG_SERVER_SECRET and DBG_DB_PASSWORD. For a service principal, add
AZURE_TENANT_ID, AZURE_CLIENT_ID and AZURE_CLIENT_SECRET.
Certificates
Section titled “Certificates”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.
Without an Azure role
Section titled “Without an Azure role”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.
Check it is working
Section titled “Check it is working”docker logs -f dbg-collectorA healthy start logs opamp handshake complete and discovered component. Use these messages to troubleshoot:
failed during probe: timed out after 10son aself_hostedcomponent: add thenode_exporterblock above.ManagedIdentityCredential authentication failed. The requested identity has not been assigned to this resource: theclient_idnames an identity this host does not have.pg_stat_statements is not queryable in the connect database: create the extension inpostgres, as in Before you start.
Upgrade
Section titled “Upgrade”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
Section titled “Remove”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.