PostgreSQL and MySQL on Azure

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

See the section bibliography for the reference material consulted while preparing these pages.

Examples on this page target Azure CLI 2.90.0 — re-verify exact flag names and behavior against the az postgres flexible-server command reference and the az mysql flexible-server command reference before relying on them, since Entra-only authentication, elastic clusters, and the extensions allowlist have all seen flag/behavior changes within the last few release cycles.

This page goes deep on Azure’s two managed relational database services built on the open-source engines — Azure Database for PostgreSQL flexible server (compute tiers, high availability, networking, Entra-only authentication, the extensions allowlist and pgvector, the built-in connection pooler, replicas and backups, elastic clusters, and the Single Server retirement) and, more briefly, Azure Database for MySQL flexible server — plus a passwordless Spring Boot JDBC configuration for both. The account-level concerns that sit above either service — resource organization, networking building blocks, and secret/identity management — are covered on Managed Databases on Azure and Identity and Access, which this page assumes as prerequisites; storing a fallback administrator password (rather than the Entra-only configuration this page defaults to) is covered on Key Vault and Secrets. Bookshelf’s catalog service uses a PostgreSQL flexible server named pg-bookshelf with a catalog database.

Azure Database for PostgreSQL flexible server

Flexible server is the current generation of the managed service: a single-tenant PostgreSQL engine running inside a Linux VM that Azure owns and patches, giving control over start/stop scheduling, maintenance windows, and zone placement that the retired Single Server deployment model didn’t expose (The Single Server retirement).

Compute tiers

Tier Best for HA support

Burstable

A fraction of a vCPU with credits that accumulate while idle and drain under load — development, CI databases, and genuinely low-traffic internal tools. Cheapest tier, but cannot be directly resized into General Purpose or Memory Optimized (a different VM family) without a size-change operation.

Not supported with zone-redundant HA.

General Purpose

Balanced compute-to-memory ratio for most production workloads with predictable performance — Bookshelf’s pg-bookshelf runs here.

SameZone and ZoneRedundant.

Memory Optimized

High memory-to-CPU ratio for memory-intensive workloads (large working sets, heavy caching) that benefit from more RAM per vCPU than General Purpose provides at the same core count.

SameZone and ZoneRedundant.

Each tier exposes its own set of --sku-name values (for example Standard_B1ms for Burstable, Standard_D2s_v3 for General Purpose); the allowed list depends on both --tier and the target region.

References: Compute options in Azure Database for PostgreSQL flexible server, Compute and storage options.

Creating a PostgreSQL flexible server

A server is created into exactly one of two networking modes, and this choice cannot be changed after creation — migrating between public access and VNet integration means creating a new server and migrating data onto it:

Mode Behavior

Public access (allowed IP addresses)

The server gets a public endpoint reachable from the internet, gated by firewall rules (Firewall rules, server parameters, extensions allowlist, and pgvector); simplest to set up, appropriate when access is fully mediated by firewall rules/Entra auth and there’s no VNet to join.

Private access (VNet integration)

The server is injected into a delegated subnet of an existing virtual network with no public endpoint at all, resolved via a linked private DNS zone — the recommended mode once Bookshelf’s catalog-api already runs inside a VNet (AKS Clusters or Container Apps).

Bookshelf also enables Entra-only authentication at creation time, so the server never has a password-based administrator account to begin with:

In the Azure portal

  1. Search for Azure Database for PostgreSQL flexible servers → + Create → Flexible server.

  2. On Basics, choose the subscription/resource group, server name (pg-bookshelf), region (eastus2), PostgreSQL version, and workload type, which preselects a Compute + storage tier — select Configure server to change tier/SKU/storage explicitly.

  3. On Authentication, choose Microsoft Entra authentication only (instead of PostgreSQL authentication only or both) and select a Microsoft Entra user, group, service principal, or managed identity as the Entra administrator — no admin password is set at all in this mode.

  4. On Networking, choose Public access (then configure firewall rules on the next step) or Private access (VNet integration) (then pick or create a virtual network, subnet, and private DNS zone) — this selection is fixed for the server’s lifetime.

  5. On High availability, optionally enable Zone redundant or Same zone HA (High availability: SameZone and ZoneRedundant; only offered for General Purpose/Memory Optimized).

  6. Select Review + create.

With the Azure CLI

export RG=rg-bookshelf
export LOCATION=eastus2
export PG_SERVER=pg-bookshelf
export PG_DATABASE=catalog

# Object id of the existing Microsoft Entra group that becomes the server's Entra administrator
# (create it first with: az ad group create --display-name catalog-api-admins --mail-nickname catalog-api-admins)
export CATALOG_API_ADMIN_OBJECT_ID=$(az ad group show --group catalog-api-admins --query id -o tsv)

# Public access, Entra-only auth -- no admin password is created
az postgres flexible-server create \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --location "$LOCATION" \
  --tier GeneralPurpose \
  --sku-name Standard_D2s_v3 \
  --storage-size 128 \
  --version 17 \
  --public-access "$(curl -s ifconfig.me)" \
  --microsoft-entra-auth Enabled \
  --password-auth Disabled \
  --admin-object-id "$CATALOG_API_ADMIN_OBJECT_ID" \
  --admin-display-name "catalog-api-admins" \
  --admin-type Group \
  --database-name "$PG_DATABASE" \
  --yes

Private access instead joins an existing subnet delegated to Microsoft.DBforPostgreSQL/flexibleServers, and takes a linked private DNS zone in place of --public-access:

az postgres flexible-server create \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --location "$LOCATION" \
  --tier GeneralPurpose \
  --sku-name Standard_D2s_v3 \
  --vnet vnet-bookshelf \
  --subnet snet-postgres \
  --private-dns-zone pg-bookshelf.private.postgres.database.azure.com \
  --microsoft-entra-auth Enabled \
  --password-auth Disabled \
  --admin-object-id "$CATALOG_API_ADMIN_OBJECT_ID" \
  --admin-display-name "catalog-api-admins" \
  --admin-type Group \
  --database-name "$PG_DATABASE" \
  --yes

--password-auth Disabled removes the classic admin-username/admin-password path entirely: every connection, administrative or application, authenticates with a Microsoft Entra access token exchanged for a PostgreSQL session (Spring Boot: passwordless JDBC). A server that instead needs a fallback password administrator (for a legacy tool that cannot obtain an Entra token) omits those two flags, sets --admin-user/--admin-password, and stores the password in Key Vault per Key Vault and Secrets rather than in application configuration.

References: az postgres flexible-server create command reference, Networking overview for Azure Database for PostgreSQL flexible server, Use Microsoft Entra ID authentication with Azure Database for PostgreSQL flexible server, Quickstart: Create a server with the Azure CLI.

High availability: SameZone and ZoneRedundant

High availability provisions a fully replicated, synchronously-updated standby server that the service automatically fails over to; it is only available on General Purpose and Memory Optimized (not Burstable):

Mode Behavior

Zone-redundant HA

The standby is placed in a different availability zone from the primary; automatic failover targets 60—​120 seconds with zero data loss on a zone-level outage. Requires the region to have multiple zones and enough capacity in a second one.

Same-zone HA

The standby is placed in the same availability zone as the primary — protects against a host/hardware failure but not a whole-zone outage; used where the region lacks zones or zonal capacity for the redundant option. Azure can migrate a same-zone deployment to zone-redundant automatically once zonal capacity becomes available.

 
az postgres flexible-server update \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --high-availability ZoneRedundant \
  --standby-zone 3

Failover (planned or automatic) keeps the server’s hostname and any PgBouncer connection unchanged (Built-in PgBouncer), so client connection strings never need to change.

References: High availability concepts in Azure Database for PostgreSQL flexible server, Configure high availability.

Firewall rules, server parameters, extensions allowlist, and pgvector

A public-access server without any firewall rule accepts no data-plane connections at all; a rule opens a specific IP range (or, for services without a fixed IP, the special 0.0.0.0 "allow public access from any Azure service" rule):

az postgres flexible-server firewall-rule create \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --rule-name allow-catalog-api-outbound \
  --start-ip-address 20.10.20.0 \
  --end-ip-address 20.10.20.255

A private-access server needs no firewall rule at all — its only reachable path is the delegated subnet.

Server (engine) parameters are configured with az postgres flexible-server parameter set, the same mechanism that manages the extensions allowlist: PostgreSQL’s CREATE EXTENSION only succeeds for an extension named in the server-level azure.extensions parameter, a safety boundary since an arbitrary extension could run arbitrary code inside the managed engine:

az postgres flexible-server parameter set \
  --resource-group "$RG" \
  --server-name "$PG_SERVER" \
  --name azure.extensions \
  --value "VECTOR,PG_TRGM,PG_STAT_STATEMENTS"

With vector allowlisted, it is enabled per database exactly as on any other PostgreSQL instance:

CREATE EXTENSION vector;

See PostgreSQL & pgvector for pgvector’s types, distance operators, HNSW/IVFFlat index tuning, and client libraries — this page only covers turning the extension on. pgvector 0.8.6 is the version bundled with the currently supported PostgreSQL major versions on flexible server; confirm the exact bundled version for a target engine version against the extension-versions table before depending on a pgvector feature introduced in a specific release.

In the Azure portal

  1. Open pg-bookshelf → Settings → Networking → add a firewall rule (public-access servers only).

  2. Open pg-bookshelf → Settings → Server parameters → search azure.extensions → add VECTOR to the allowed value list → Save.

  3. Connect with any PostgreSQL client (psql, a portal query tool, or an application) and run CREATE EXTENSION vector; against the catalog database.

References: az postgres flexible-server firewall-rule command reference, Manage firewall rules using the Azure CLI, Allow extensions in Azure Database for PostgreSQL flexible server, Considerations when using extensions and modules.

Built-in PgBouncer

Flexible server ships a managed PgBouncer instance alongside the engine, reachable on port 6432 of the same hostname used for the direct engine connection (port 5432) — there is no separate proxy to deploy or patch. PgBouncer restarts automatically on the newly-promoted primary after a failover, so a client pointed at port 6432 keeps the same connection string across HA events. It matters most for a workload (like a typical Spring Boot app with its own connection pool, or a serverless/scale-to-zero compute layer) that opens many short-lived or idle connections, since PgBouncer multiplexes them onto a smaller number of actual PostgreSQL backend connections:

az postgres flexible-server parameter set \
  --resource-group "$RG" \
  --server-name "$PG_SERVER" \
  --name pgbouncer.enabled \
  --value true
psql "host=pg-bookshelf.postgres.database.azure.com port=6432 dbname=catalog sslmode=require"

References: PgBouncer in Azure Database for PostgreSQL flexible server, Configure PgBouncer.

Read replicas, backups, and geo-redundant backup

A read replica is an asynchronously-replicated, read-only copy of the primary, in the same or a different region, used to offload read traffic or as a basis for a cross-region disaster-recovery topology (it is not itself a failover target the way HA’s synchronous standby is):

az postgres flexible-server replica create \
  --resource-group "$RG" \
  --replica-name pg-bookshelf-replica-westus2 \
  --source-server "$PG_SERVER"

Automated backups (full plus continuous log/WAL archiving, enabling point-in-time restore within the retention window) run without extra configuration; the retention window and geo-redundancy are the two knobs:

az postgres flexible-server update \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --backup-retention 14

Geo-redundant backup (storing backup data in the region’s paired region, in addition to the primary region) can only be set at server creation, not toggled afterward:

az postgres flexible-server create \
  --resource-group "$RG" \
  --name pg-bookshelf-geo \
  --location "$LOCATION" \
  --tier GeneralPurpose \
  --sku-name Standard_D2s_v3 \
  --geo-redundant-backup Enabled \
  --public-access "$(curl -s ifconfig.me)" \
  --microsoft-entra-auth Enabled \
  --password-auth Disabled \
  --admin-object-id "$CATALOG_API_ADMIN_OBJECT_ID" \
  --admin-display-name "catalog-api-admins" \
  --admin-type Group

Point-in-time restore creates a new server from a timestamp within the retention window; it never overwrites the source server in place:

az postgres flexible-server restore \
  --resource-group "$RG" \
  --name pg-bookshelf-restored \
  --source-server "$PG_SERVER" \
  --restore-time "2026-09-20T05:00:00Z"

References: Read replicas in Azure Database for PostgreSQL flexible server, Backup and restore concepts, az postgres flexible-server restore command reference.

Major-version upgrade

An in-place major-version upgrade replaces the running engine major version without changing the server’s name, connection endpoint, or configuration — it does not support a server with an active read replica (the replica must be deleted first and can be recreated afterward), and Microsoft publishes a set of upgrade validation checks to run before committing to the upgrade. pg-bookshelf was created on PostgreSQL 17 above, so the example validates and then performs an upgrade to 18:

# Pre-upgrade validation only -- reports blockers without changing the server
az postgres flexible-server upgrade \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --version 18 \
  --validate-only

# The actual in-place upgrade from 17 to 18
az postgres flexible-server upgrade \
  --resource-group "$RG" \
  --name "$PG_SERVER" \
  --version 18

References: Major version upgrades in Azure Database for PostgreSQL flexible server, Perform major version upgrade, az postgres flexible-server upgrade command reference.

Elastic clusters

An elastic cluster distributes a single logical PostgreSQL database (powered by the Citus extension) across multiple flexible-server nodes, sharding tables by a chosen distribution column — the option for a workload that has genuinely outgrown a single server’s vertical scaling headroom, rather than a Bookshelf-scale catalog database. Elastic clusters are generally available, including Terraform support for defining them as infrastructure-as-code, and current releases run on PostgreSQL 18 (via Citus 14):

az postgres flexible-server create \
  --resource-group "$RG" \
  --name pg-bookshelf-cluster \
  --location "$LOCATION" \
  --tier MemoryOptimized \
  --sku-name Standard_M32ds_v3 \
  --node-count 3 \
  --database-name catalog_sharded

Because a cluster shards data, application code must be sharding-aware (a distribution key on every table that needs cross-node joins, and query patterns that avoid unnecessary cross-shard operations); a single-node General Purpose/Memory Optimized server remains the right default until sharding is an actual requirement.

References: Elastic clusters in Azure Database for PostgreSQL flexible server, Quickstart: Create an elastic cluster.

The Single Server retirement

Single Server was the previous-generation deployment model (no VNet integration, no zone-redundant HA, capped PostgreSQL major-version support); Microsoft retired it on 28 March 2025, and any Single Server instance still running past that date is subject to Microsoft-driven migration rather than a supported, indefinitely-running configuration. Every example on this page targets flexible server, the only current deployment model for new work; a project still referencing Single Server (an old az postgres server create command, a hostname of the form <name>.postgres.database.azure.com created years ago against the old API, or documentation predating 2023) should treat that as a signal to migrate rather than a still-current option, using Microsoft’s own migration tooling rather than a manual dump/restore where possible.

References: Azure Database for PostgreSQL Single Server will be retired — migrate to Flexible Server by 28 March 2025, What happened to Azure Database for PostgreSQL Single Server.

Azure Database for MySQL flexible server

MySQL flexible server mirrors PostgreSQL flexible server’s shape closely enough that the concepts above mostly transfer directly; this section only calls out where MySQL’s specifics differ. Bookshelf would use this for a component that specifically needs MySQL compatibility rather than PostgreSQL (for example a third-party package that assumes MySQL) — named mysql-bookshelf with a catalog database for consistency with the PostgreSQL example above.

Compute tiers are the same three names — Burstable, General Purpose, Memory Optimized — with the same constraint that zone-redundant HA requires General Purpose or Memory Optimized (Burstable cannot run with HA enabled at all, not even same-zone). Networking is the same choice between public access and VNet integration, fixed at creation. Authentication supports the same Entra-only configuration via --microsoft-entra-auth Enabled --password-auth Disabled (with --admin-object-id/--admin-display-name/--admin-type for the Entra administrator), backed by the same underlying data-plane token exchange as PostgreSQL. Extensions are called plugins and components on MySQL and are allowlisted through the azure.*_enable_json and component-specific server parameters rather than a single azure.extensions value; MySQL flexible server has no built-in PgBouncer equivalent — Bookshelf uses the client-side pooling already inherent in a JDBC connection pool (HikariCP, Spring Boot’s default) instead.

In the Azure portal

  1. Search for Azure Database for MySQL flexible servers → + Create.

  2. On Basics, choose the resource group, server name (mysql-bookshelf), region, MySQL version, and Compute + storage tier.

  3. On Networking, choose Public access or Private access (VNet integration) — fixed for the server’s lifetime, exactly as for PostgreSQL.

  4. On Authentication, choose Microsoft Entra authentication only and select the Entra administrator.

  5. On High availability, optionally enable Zone redundant HA or Same zone HA (General Purpose/Memory Optimized only).

  6. Select Review + create.

With the Azure CLI

export MYSQL_SERVER=mysql-bookshelf
export MYSQL_DATABASE=catalog

az mysql flexible-server create \
  --resource-group "$RG" \
  --name "$MYSQL_SERVER" \
  --location "$LOCATION" \
  --tier GeneralPurpose \
  --sku-name Standard_D2ds_v4 \
  --storage-size 128 \
  --version 8.0.21 \
  --high-availability ZoneRedundant \
  --public-access "$(curl -s ifconfig.me)" \
  --microsoft-entra-auth Enabled \
  --password-auth Disabled \
  --admin-object-id "$CATALOG_API_ADMIN_OBJECT_ID" \
  --admin-display-name "catalog-api-admins" \
  --admin-type Group \
  --database-name "$MYSQL_DATABASE" \
  --yes

az mysql flexible-server firewall-rule create \
  --resource-group "$RG" \
  --name "$MYSQL_SERVER" \
  --rule-name allow-catalog-api-outbound \
  --start-ip-address 20.10.20.0 \
  --end-ip-address 20.10.20.255

az mysql flexible-server replica create \
  --resource-group "$RG" \
  --replica-name mysql-bookshelf-replica-westus2 \
  --source-server "$MYSQL_SERVER"

az mysql flexible-server update \
  --resource-group "$RG" \
  --name "$MYSQL_SERVER" \
  --backup-retention 14

MySQL flexible server has no elastic-clusters equivalent; horizontal sharding across multiple MySQL servers is an application-level (or third-party proxy) concern, not a first-party Azure feature. MySQL’s own previous- generation deployment model, Single Server, follows the same retirement path as PostgreSQL’s and should likewise be treated as already gone rather than a current option.

References: az mysql flexible-server command reference, What is Azure Database for MySQL flexible server?, High availability concepts in Azure Database for MySQL flexible server, Networking concepts, Read replicas in Azure Database for MySQL flexible server.

Spring Boot: passwordless JDBC

The passwordless Spring Cloud Azure JDBC starter replaces a connection string carrying a database password with DefaultAzureCredential-backed token authentication — locally via the developer’s own Entra sign-in (Azure CLI, IntelliJ, VS Code), and in Azure via the compute resource’s managed identity, with no code change between the two:

<dependency>
    <groupId>com.azure.spring</groupId>
    <artifactId>spring-cloud-azure-starter-jdbc-postgresql</artifactId>
</dependency>
# application.yml
spring:
  datasource:
    url: "jdbc:postgresql://pg-bookshelf.postgres.database.azure.com:5432/catalog?sslmode=require"
    username: "catalog-api"          # the Entra principal name (or managed identity) granted access, not a password
    azure:
      passwordless-enabled: true

The catalog-api username above refers to a PostgreSQL role created from catalog-api’s managed identity (via `pgaadauth_create_principal_with_oid, run once as the Entra administrator) rather than a native PostgreSQL login role — creating that role is a one-time setup step, not something the starter does for the application. No spring.datasource.password property exists anywhere in configuration, environment variables, or code, which is the point: nothing to leak in a log, a committed application.yml, or a container image layer. The MySQL equivalent swaps in spring-cloud-azure-starter-jdbc-mysql and a jdbc:mysql:// URL, with the same azure.passwordless-enabled property and the same managed-identity role-creation step on the MySQL side.

References: Passwordless connections for Azure Database for PostgreSQL, Passwordless connections for Azure Database for MySQL, Passwordless authentication with Spring Cloud Azure.

Pricing model

Both services bill along the same independent axes, though the exact per-unit rates differ between them:

  • Compute — an hourly rate per vCPU/SKU that varies by tier (Burstable cheapest, Memory Optimized generally the most expensive per vCPU) and whether HA is enabled (a zone-redundant or same-zone standby roughly doubles the compute charge, since it is a second fully-provisioned server).

  • Storage — a per-GiB/month rate for the provisioned storage size, independent of how much of it is actually used, plus IOPS/throughput charges on Premium SSD v2-backed storage tiers that expose those as separate knobs.

  • Backup storage — backup storage up to the provisioned database size is included; storage beyond that (a long retention window, or geo-redundant backup effectively doubling stored backup data) bills separately.

  • Networking — a public-access server’s data egress to the internet bills like any other Azure egress; VNet-integrated traffic between resources in the same region is typically free.

  • Elastic clusters — billed per node (each node is its own compute + storage charge), so cost scales roughly linearly with node count rather than being a fixed uplift over a single server.

Exact current per-unit rates vary by region and currency and change over time; treat any specific number here as indicative, not contractual — current rates are published at the PostgreSQL flexible server pricing page and the MySQL flexible server pricing page, and the Azure pricing calculator models a specific tier/HA/storage combination end to end.

References: Azure Database for PostgreSQL flexible server pricing, Azure Database for MySQL flexible server pricing.

Troubleshooting

CREATE EXTENSION vector fails with "extension \"vector\" is not allow-listed for configuration". The extension was not added to the azure.extensions server parameter (Firewall rules, server parameters, extensions allowlist, and pgvector), or the change to that parameter has not yet been applied — a parameter-set call against azure.extensions (a server-level, not session-level, setting) can require a brief propagation delay; re-check the parameter’s current value with az postgres flexible-server parameter show --name azure.extensions before assuming the allowlist update failed outright.

Connection refused / timeout against a public-access server. No firewall rule (or an incorrect/expired IP range) covers the calling IP (Firewall rules, server parameters, extensions allowlist, and pgvector); a corporate NAT or a dynamic residential IP that has since changed are the most common culprits for a rule that "used to work." A private-access (VNet-integrated) server instead fails this way when the caller isn’t on the delegated subnet or a peered/connected network, or when the private DNS zone isn’t linked to the caller’s VNet, so the hostname doesn’t resolve to the private endpoint at all.

Entra token authentication fails with a role/login error even though the token itself is valid. The Entra principal (user, group member, service principal, or managed identity) authenticating has no corresponding PostgreSQL/MySQL role created for it yet — unlike Azure RBAC role assignments elsewhere in Azure, an Entra principal must still be provisioned as a database-level role (pgaadauth_create_principal/ pgaadauth_create_principal_with_oid on PostgreSQL, an analogous AAD_LOGIN-derived flow on MySQL) by the Entra administrator before it can connect, and --password-auth Disabled gives no fallback path around that step.

A read replica or elastic-cluster operation is rejected during a major-version upgrade. In-place major version upgrade does not support a server with an active read replica (Major-version upgrade); delete the replica first, upgrade the primary, then recreate the replica. Elastic clusters upgrade node-by-node through their own dedicated workflow rather than the single-server upgrade command.

PgBouncer connections succeed but behave differently than a direct engine connection. PgBouncer’s default pooling mode multiplexes many client connections onto fewer backend connections, which changes the visibility of session-level state (temporary tables, session variables, prepared statements outside a single transaction) between application requests that land on different backend connections. A workload that genuinely needs session affinity should either connect on port 5432 directly or adjust pooling mode/behavior rather than treating port 6432 as a drop-in, no-side-effects replacement.

References: Allow extensions in Azure Database for PostgreSQL flexible server, Networking overview, Use Microsoft Entra ID authentication, Major version upgrades, PgBouncer in Azure Database for PostgreSQL flexible server.

Clean up

az group delete --name "$RG" --yes --no-wait

This removes pg-bookshelf (and any replica, elastic cluster, or restored server created alongside it), mysql-bookshelf, and every other resource created in $RG on this page.

Related pages

References