Azure Database for PostgreSQL flexible server

Prerequisites for DBtune agent setup

Network access

Please ask your infosec department to whitelist the following URL for external access:

https://app.dbtune.com.
Ensure, your client host can communicate to the Azure Database for PostgreSQL flexible server.

Permissions

Your Azure account must have sufficient privileges at the Resource group or Subscription level to modify server parameters and manage RBAC role assignments.

Steps to install the DBtune on Azure Database for PostgreSQL flexible server:

Step 1

Run the below command against your Azure Database for PostgreSQL flexible using psql from your client host.

Note: Replace azure_db_instance with your Azure flexible Server name and your_db_name with your database name:

psql -h <azure_db_instance>.postgres.database.azure.com -p 5432 -U postgres <your_db_name>

Create a new DB user dbtune_agent with a password and assign it the pg_monitor role:

CREATE USER dbtune_agent with PASSWORD '<Strong password>';

GRANT pg_monitor TO dbtune_agent;

Step 2

Next, enable the pg_stat_statements extension on the Azure portal .

In the left panel, navigate to Server parameters under Settings

Search for "extensions" and select PG_STAT_STATEMENTS under azure.extensions. Click Save:

Alternatively, you can run it through Azure CLI:

az postgres flexible-server parameter set \
  --resource-group $RG \
  --server-name $SERVER_NAME \
  --name azure.extensions \
  --value pg_stat_statements

Next, enable pg_stat_statement.track by setting it to either ALL or TOP and Save. DBtune works with both options:

You can enable it through Azure CLI:

az postgres flexible-server parameter set \
  --resource-group <your-resource-group> \
  --server-name <your-server-name> \
  --name pg_stat_statements.track \
  --value top

Next, create the extension on the database. Go back to your psql session.

Create the pg_stat_statements extension in your default database. This database will be referenced in the connection_url configuration inside dbtune.yaml.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Managed identity and permissions:

The DBtune agent communicates securely with the Azure Resource Manager (ARM) API through Managed identity, which provides password-less security. It authenticates with Microsoft entra ID, so you don't need to store credentials.

Next step is to enable managed identity on the client machine where you'll install the DBtune agent.

Step 3

Go to Azure portal, On your Azure VM, navigate to Security → Identity and check whether System assigned identity is enabled.

If it's not enabled, enable System assigned and click Save.

you can also assign it through Azure CLI:

az vm identity assign \
  --resource-group RG_VM \
  --name DBtuneVM

Step 4

Navigate to Azure Database for PostgreSQL flexible Server Resource Group. Open Access control (IAM) and create the Custom role here. This ensures the agent has the necessary permissions to manage the database, regardless of where the VM is located.

Set the unique name for the custom role and click Next:

Next, navigate to the JSON tab and click Edit to enable the JSON field. Copy the below required permissions into the "actions" field.

"actions": [
    "Microsoft.DBforPostgreSQL/flexibleServers/restart/action",
    "Microsoft.DBforPostgreSQL/flexibleServers/read",
    "Microsoft.DBforPostgreSQL/flexibleServers/providers/Microsoft.Insights/metricDefinitions/read",
    "Microsoft.DBforPostgreSQL/flexibleServers/configurations/read",
    "Microsoft.DBforPostgreSQL/flexibleServers/configurations/write",
    "Microsoft.Insights/metrics/read",
    "Microsoft.Insights/metricDefinitions/read"
]

Click Save, then Next, and finally Create:

You can also assign it through Azure CLI, Create a json file with the roles mentioned above and assignable scope(/subscriptions/<subscription_id>/resourceGroups/<resource_group_name>).

Run the following command:

az role definition create --role-definition dbtune-role.json

The custom role is now defined with the necessary permissions.

Now, we will assign the custom role to the managed identity. This step links the specific permissions defined in the custom role to the Virtual machine, allowing the dbtune agent to operate.

Step 5

Next, Navigate to Azure Database for PostgreSQL flexible Server Resource Group. Open Access control (IAM) add the role assignments :

In the Role section, search for the custom role you created, select and click Next:

On the next screen, select Managed identity.

Click Select members, Under Managed identity choose the VM where you'll run the DBtune agent, then click Review + assign to assign the role:

You can run it through Azure CLI:

Check the Principal ID:

az vm identity show \
  --resource-group <VM resourcegroup> \
  --name <VM name>

Assign the role:

az role assignment create \
  --assignee <VM_PRINCIPAL_ID> \
  --role "Custom role you created" \
  --scope /subscriptions/<subscription_id>/resourceGroups/<Flex_DB_Resourcegroup> 

Step 6

Copy the below curl command and run it on your Linux VM to install the DBtune agent.

curl https://raw.githubusercontent.com/dbtuneai/dbtune-agent/refs/heads/main/setup.sh > /tmp/dbtune-agent.sh && sh /tmp/dbtune-agent.sh

Step 7

Create a dbtune.yaml file on your VM and copy the contents below.

Add your Azure flexible Server details—subscription_id, resource_group_name, server_name, and connection_url (You can find these details in the Azure portal) Copy the api_key and database_id from the Agent setup page on https://app.dbtune.com

Example yaml.config file:

# Example config file for Azure Database for PostgreSQL flexible server
postgresql:
  connection_url: "postgresql://user:password@your-server.postgres.database.azure.com:5432/<dbname>"
	include_queries: true
	 
azure_flex:
  subscription_id: "<your-subscription-id>"
  resource_group_name: "<your-resourcegroup-name>"
  server_name: "<your-azure-db-server-name>"

dbtune:
	server_url: https://app.dbtune.com 
  api_key: <copy-from-app-settings>
  database_id: <copy-from-app-settings>
  
debug: false

Finally, run the DBtune agent on your Linux VM. You can use the screen feature to keep it running in the background. To detach the screen session, press Ctrl + A, then D.

screen

./dbtune-agent --azure-flex

Alternatively run the DBtune agent using Docker with PostgreSQL flexible server-specific configuration:

docker run --restart always \
    -e DBT_POSTGRESQL_CONNECTION_URL="postgresql://..." \
    -e DBT_AZURE_FLEX_SUBSCRIPTION_ID=your-subscription-id \
    -e DBT_AZURE_FLEX_RESOURCE_GROUP_NAME=your-resource-group-name \
    -e DBT_AZURE_FLEX_SERVER_NAME=your-flexible-server-name
    -e DBT_DBTUNE_SERVER_URL=https://app.dbtune.com \
    -e DBT_DBTUNE_API_KEY=your-dbtune-api-key \
    -e DBT_DBTUNE_DATABASE_ID=your-dbtune-database-id \
    public.ecr.aws/dbtune/dbtune/agent:latest

The best way to continue your local testing using Docker is the following:

Setup a Service principal that uses the role you created earlier for the DBtune agent. This can be done from the az cli if you have the right level of permissions on the subscription. First make sure you are logged in for the correct subscription, and then run:

az ad sp create-for-rbac --name <custom_name> --role <name-of-your-custom-role> --scopes /subscriptions/<subscription_id>/resourceGroups/<resource_group_name>

For the scope that the permissions should apply over, this scope has to contain the Azure Database for PostgreSQL flexible server, and cannot be larger than the assignable scopes of the role. From memory, your role was assignable across the subscription, so you could use /subscriptions/<YOUR_SUBSCRIPTION_ID>.

The command will output a JSON blob if successful with at least following the keys: appId, password, tenant. These are the credentials the DBtune agent needs and you will need to save these. Here is the Microsoft documentation for this step if you need further information.

Pass the Service principal’s credentials into docker as extra environment credentials, the agent will then be able to use them. Using everything you had earlier, add the following lines before the name of the image:

docker run \
...
-e AZURE_CLIENT_ID=<value of appId from last step> \
-e AZURE_CLIENT_SECRET=<value of password from last step> \
-e AZURE_TENANT_ID=<value of tenant from last step> \
public.ecr.aws/dbtune/dbtune/agent:latest

For information about common environment variables (DBT_DBTUNE_* and DBT_POSTGRESQL_*), see agent overview - common environment variables.