Microsoft Azure Support

Starting with version 1.13.0, pgmetrics can fetch metrics exposed by Azure Database for PostgreSQL instances via Azure Monitor APIs. The Single Server, Flexible Server and Hyperscale (Citus) types are supported.

Currently, pgmetrics can collect the metrics listed here (links go to Microsoft Azure documentation):

pgmetrics collects these metrics using the Azure Monitor REST API.

Invoking

To collect the information, pgmetrics has to know the Resource Identifier URI of your database. This contains the subscription ID (which is a UUID) as well as the resource group and resource identifiers. It looks like /subscriptions/689fake-fake-abcd-abcd-45fakefakef1/resourceGroups/resource-group-1/providers/Microsoft.DBforPostgreSQL/flexibleServers/database-server-1. This URI has to be passed to pgmetrics using the command-line option --az-resource.

The Azure credential information must be passed via environment variables, see the next section.

Here is an example invocation, directly using hard-coded credentials for the database:

export AZR=/subscriptions/689fake-fake-abcd-abcd-45fakefakef1/resourceGroups/resource-group-1/providers/Microsoft.DBforPostgreSQL/flexibleServers/database-server-1
export AZ_TENANT_ID=x     # see below
export AZ_CLIENT_ID=x     # see below
export AZ_CLIENT_SECRET=x # see below
export PGPASSWORD=mysecretpass
export PGSSLMODE=require
./pgmetrics \
  -h database-server-1.postgres.database.azure.com \
  -U postgres \
  --az-resource=$AZR \
  -w -fjson mydatabasename

In this case, the database host is “database-server-1.postgres.database.azure.com”, the database name is “mydatabasename”, the user is “postgres” and the password is “mysecretpass”. The connection is made via SSL. The Azure resource ID is the string set in $AZR. The variables AZ_TENENT_ID, AZ_CLIENT_ID and AZ_CLIENT_SECRET provide one way of authentication of the REST API calls that pgmetrics makes (the next section has more information).

This produces a JSON output similar to the following:

{
  "meta": {
    "version": "1.12",
    "at": 1652938989,
    "collected_dbs": [
      "mydatabasename"
    ],
    "local": false,
    "user_agent": "pgmetrics/v1.13.0",
    "user": "postgres"
  },
  "start_time": 1652937932,
  "system_identifier": "7099314309885263910",
  "checkpoint_lsn": "0/3006738",
  "prior_lsn": "",
  "redo_lsn": "0/30065F8",
  "timeline_id": 1,
  "next_xid": 669,
  "oldest_xid": 479,
  "oldest_active_xid": 669,
  "checkpoint_time": 1652938555,
  "wal_flush_lsn": "0/40007F0",

  [... elided ...]

  "azure": {
    "resource_name": "database-server-1",
    "resource_type": "Microsoft.DBforPostgreSQL/flexibleServers",
    "resource_region": "westus",
    "metrics": {
      "active_connections": 4.5,
      "connections_succeeded": 6,
      "cpu_percent": 10.84,
      "disk_queue_depth": 0,
      "iops": 1,
      "maximum_used_transactionIDs": 222.5,
      "memory_percent": 38.28,
      "network_bytes_egress": 463000,
      "network_bytes_ingress": 279080,
      "read_iops": 0,
      "read_throughput": 0,
      "storage_free": 31673110528,
      "storage_percent": 1,
      "storage_used": 144216064,
      "txlogs_storage_used": 58720256,
      "write_iops": 0.5,
      "write_throughput": 4912.5
    }
  }
}

Supplying Credentials

Azure supports many ways of authentication. Depending on your use case and need, you can use one of these authentication options with pgmetrics:

Service principal with a secret

Set the environment variables:

  • AZURE_CLIENT_ID - Application ID of an Azure service principal
  • AZURE_TENANT_ID - ID of the application’s Azure AD tenant
  • AZURE_CLIENT_SECRET - Password of the Azure service principal

before invoking pgmetrics.

Service principal with a certificate

Set the environment variables:

  • AZURE_CLIENT_ID - ID of an Azure AD application
  • AZURE_TENANT_ID - ID of the application’s Azure AD tenant
  • AZURE_CLIENT_CERTIFICATE_PATH - Path to a certificate file including private key (without password protection)

before invoking pgmetrics.

Username and password (without 2FA)

Set the environment variables:

  • AZURE_CLIENT_ID - ID of an Azure AD application
  • AZURE_USERNAME - A username (usually an email address)
  • AZURE_PASSWORD - That user’s password

before invoking pgmetrics.

Managed identity

If you’re running pgmetrics from an Azure VM that has access to Azure REST APIs (granted via System-assigned managed identities), no environment variables are required.

If you want to use User-assigned managed identities, set the environment variables:

  • AZURE_CLIENT_ID - User-assigned managed identity client id

before invoking pgmetrics.

Azure CLI login (for dev/test only)

pgmetrics can also be invoked from a system where you’ve previously installed the Azure CLI and authenticated it (using “az login”). Use this setup only for development or testing.

A Note About Billing

Usage of the Azure Monitor REST APIs will incur costs, based on their pricing plans. This means that invoking pgmetrics to collect these metrics will incur charges on your Azure subsription.