Azure Flexible Server for PostrgeSQL

Azure Flexible Server for PostgreSQL agent setup

For an overview of how the DBtune agent works, see agent overview.

Prerequisites

Before starting the DBtune agent, there is some setup you are required to complete on your database.

Azure Flexible Server requires you to allow specific extensions explicitly before they can be used, pg_stat_statements (which the DBtune agent requires) is one of these. First, allowlist the extension by adding it to the azure.extensions server parameter.

Once you have allowlisted pg_stat_statements, ensure you have completed the remaining database setup steps.

Azure managed identity IAM permissions

The agent is setup to utilize the managed identity attached to an Azure Virtual Machine. The IAM role associated with those credentials is required to be able to perform the following actions:

"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"
]

Setting up the agent

Run the DBtune agent using Docker with Azure Flexible Server-specific configuration:

docker run --restart always --detach \
    -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 --azure-flex

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

Azure Flexible Server-specific environment variables

Variable	Description
DBT_AZURE_FLEX_SUBSCRIPTION_ID	The ID of the Azure subscription that your Azure Felible Server instance is in.
DBT_AZURE_FLEX_RESOURCE_GROUP_NAME	The name of the Resource Group that your Azure Flexible Server instance is in.
DBT_AZURE_FLEX_SERVER_NAME	The name of your Azure Flexible Server instance.

Parameters tuned

DBtune supports multiple tuning modes that determine which parameters can be optimized. For a detailed explanation of tuning modes, see tuning modes.

Reload-only tuning mode

The following parameters are tuned in reload-only mode:

The following parameters are tuned in reload-only mode:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. checkpoint_completion_target
5. effective_io_concurrency
6. max_parallel_workers_per_gather
7. max_parallel_workers
8. max_wal_size
9. min_wal_size
10. bgwriter_lru_maxpages
11. bgwriter_delay
12. effective_cache_size
13. maintenance_work_mem
14. default_statistics_target
15. max_parallel_maintenance_workers

Restart tuning mode

Currently, restart tuning mode is not supported for Azure Flexible Server.