Database for PostgreSQL Flexible Server

Introduction

Azure Database for PostgreSQL flexible server is a fully managed relational database service that provides granular control over database configuration and tuning. It supports PostgreSQL community versions and offers built-in high availability, intelligent performance monitoring, and flexible scaling across Burstable, General Purpose, and Memory Optimized compute tiers. Common use cases include web applications, microservices backends, and analytics workloads that benefit from PostgreSQL’s extensibility and standards compliance. For more information, see What is Azure Database for PostgreSQL - Flexible Server?.

LocalStack for Azure provides a local environment for building and testing applications that make use of Azure DB for PostgreSQL. The supported APIs are available on our API Coverage section, which provides information on the extent of DB for PostgreSQL integration with LocalStack.

Getting started

This guide is designed for users new to Azure DB for PostgreSQL and assumes basic knowledge of the Azure CLI and our lstk az proxy.

Launch LocalStack using your preferred method. For more information, see Introduction to LocalStack for Azure. Once the container is running, enable Azure CLI interception by running:

lstk az start-interception

This command points the az CLI away from the public Azure management REST API and toward the LocalStack for Azure emulator API. To revert this configuration, run:

lstk az stop-interception

This reconfigures the az CLI to send commands to the official Azure management REST API.

Create a resource group

Create a resource group to hold all resources created in this guide:

az group create \
  --name rg-postgres-demo \
  --location westeurope

{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo",
  "location": "westeurope",
  "name": "rg-postgres-demo",
  "properties": {
    "provisioningState": "Succeeded"
  },
  ...
}

Create and inspect a PostgreSQL flexible server

Create a Burstable-tier PostgreSQL 16 flexible server with 32 GB of storage:

az postgres flexible-server create \
  --name postgres-demo \
  --resource-group rg-postgres-demo \
  --location westeurope \
  --admin-user pgadmin \
  --admin-password 'P@ssw0rd2024!' \
  --sku-name Standard_B1ms \
  --tier Burstable \
  --version 16 \
  --storage-size 32 \
  --yes

{
  "connectionString": "postgresql://pgadmin:P@ssw0rd2024!@postgres-demo.postgresql.azure.localhost.localstack.cloud:4511/postgres?sslmode=require",
  "databaseName": "postgres",
  "host": "postgres-demo.postgresql.azure.localhost.localstack.cloud:4511",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-demo",
  "location": "West Europe",
  "password": "P@ssw0rd2024!",
  "resourceGroup": "rg-postgres-demo",
  "skuname": "Standard_B1ms",
  "username": "pgadmin",
  "version": "16",
  ...
}

The command waits for the server to reach the Ready state before returning. Azure requires the version property when it creates a server, and so does the emulator: a request without it is refused with MissingRequiredParameter.

Show and list flexible servers

Retrieve the details of the flexible server:

az postgres flexible-server show \
  --name postgres-demo \
  --resource-group rg-postgres-demo

{
  "administratorLogin": "pgadmin",
  "authConfig": {
    "activeDirectoryAuth": "Disabled",
    "passwordAuth": "Enabled",
    "tenantId": null
  },
  "backup": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    ...
  },
  "cluster": null,
  "fullyQualifiedDomainName": "postgres-demo.postgresql.azure.localhost.localstack.cloud:4511",
  "highAvailability": {
    "mode": "Disabled",
    "standbyAvailabilityZone": null,
    "state": "NotEnabled"
  },
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-demo",
  "location": "West Europe",
  "name": "postgres-demo",
  "network": {
    "delegatedSubnetResourceId": null,
    "privateDnsZoneArmResourceId": null,
    "publicNetworkAccess": "Enabled"
  },
  "resourceGroup": "rg-postgres-demo",
  "sku": {
    "name": "Standard_B1ms",
    "tier": "Burstable"
  },
  "state": "Ready",
  "storage": {
    "autoGrow": "Disabled",
    "iops": 120,
    "storageSizeGb": 32,
    "throughput": null,
    "tier": "P4",
    "type": "Premium_LRS"
  },
  "type": "Microsoft.DBforPostgreSQL/flexibleServers",
  "version": "16",
  ...
}

Then list all flexible servers in the resource group:

az postgres flexible-server list \
  --resource-group rg-postgres-demo

[
  {
    "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-demo",
    "location": "West Europe",
    "name": "postgres-demo",
    "sku": {
      "name": "Standard_B1ms",
      "tier": "Burstable"
    },
    "state": "Ready",
    "version": "16",
    ...
  }
]

Connect to the server

Read the endpoint from the server and export it:

export PG_FQDN=$(az postgres flexible-server show --name postgres-demo --resource-group rg-postgres-demo --query fullyQualifiedDomainName --output tsv)
echo "$PG_FQDN"

postgres-demo.postgresql.azure.localhost.localstack.cloud:4511

The endpoint carries the port the emulator listens on for this server, after a colon. Connect with psql over TLS, which the server requires as long as require_secure_transport is on, as on Azure. A connection URI takes the endpoint as it is, port included:

export PGPASSWORD='P@ssw0rd2024!'
psql "postgresql://pgadmin@${PG_FQDN}/postgres?sslmode=require" \
  -c "SELECT current_setting('server_version') AS engine, ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid()"

            engine             | ssl |   tls
-------------------------------+-----+---------
 18.3 (Debian 18.3-1.pgdg13+1) | t   | TLSv1.3
(1 row)

The server presents the LocalStack public certificate, whose *.postgresql.azure.localhost.localstack.cloud entry covers the host name, so a client can also verify the server against the public roots it trusts. By default, psql reads the roots it trusts from ~/.postgresql/root.crt, so sslmode=verify-full fails when that file does not exist. With libpq 16 or later, sslrootcert=system loads the trusted roots of the system instead, and the same URI works against Azure:

psql "postgresql://pgadmin@${PG_FQDN}/postgres?sslmode=verify-full&sslrootcert=system" \
  -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid()"

 ssl |   tls
-----+---------
 t   | TLSv1.3
(1 row)

When LocalStack cannot download that certificate, for example with SKIP_SSL_CERT_DOWNLOAD=1 or without outbound network access, the server presents a certificate issued by the LocalStack root certificate authority instead, and a client verifying the server needs that authority, in sslrootcert or PGSSLROOTCERT.

The engine reports PostgreSQL 18 although the server reports version 16: see Limitations.

Update a flexible server

Update the server SKU and storage size:

az postgres flexible-server update \
  --name postgres-demo \
  --resource-group rg-postgres-demo \
  --sku-name Standard_B2s \
  --tier Burstable \
  --storage-size 64 \
  --yes

{
  "administratorLogin": "pgadmin",
  ...
  "fullyQualifiedDomainName": "postgres-demo.postgresql.azure.localhost.localstack.cloud:4511",
  "location": "West Europe",
  "name": "postgres-demo",
  "sku": {
    "name": "Standard_B2s",
    "tier": "Burstable"
  },
  "state": "Ready",
  "storage": {
    "autoGrow": "Disabled",
    "iops": 240,
    "storageSizeGb": 64,
    "throughput": null,
    "tier": "P6",
    "type": "Premium_LRS"
  },
  "type": "Microsoft.DBforPostgreSQL/flexibleServers",
  "version": "16",
  ...
}

Create a database

Create a database on the flexible server:

az postgres flexible-server db create \
  --server-name postgres-demo \
  --resource-group rg-postgres-demo \
  --name sampledb \
  --charset UTF8 \
  --collation en_US.utf8

{
  "charset": "UTF8",
  "collation": "en_US.utf8",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-demo/databases/sampledb",
  "name": "sampledb",
  "resourceGroup": "rg-postgres-demo",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/databases"
}

Get and list databases:

az postgres flexible-server db show \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo \
  --name sampledb

az postgres flexible-server db list \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo

{
  "charset": "UTF8",
  "collation": "en_US.utf8",
  "name": "sampledb",
  ...
}
[
  {
    "name": "azure_maintenance",
    ...
  },
  {
    "name": "azure_sys",
    ...
  },
  {
    "name": "postgres",
    ...
  },
  {
    "name": "sampledb",
    ...
  }
]

Create and inspect a firewall rule

Create a firewall rule to allow connections from a specific IP address:

az postgres flexible-server firewall-rule create \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo \
  --name allow-myip \
  --start-ip-address 203.0.113.10 \
  --end-ip-address 203.0.113.10

{
  "endIpAddress": "203.0.113.10",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-demo/firewallRules/allow-myip",
  "name": "allow-myip",
  "resourceGroup": "rg-postgres-demo",
  "startIpAddress": "203.0.113.10",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/firewallRules"
}

Get and list firewall rules:

az postgres flexible-server firewall-rule show \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo \
  --name allow-myip

az postgres flexible-server firewall-rule list \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo

{
  "endIpAddress": "203.0.113.10",
  "name": "allow-myip",
  "startIpAddress": "203.0.113.10",
  ...
}
[
  ...
  {
    "endIpAddress": "203.0.113.10",
    "name": "allow-myip",
    "startIpAddress": "203.0.113.10",
    ...
  }
]

The list also holds the rule az postgres flexible-server create added for your client address.

View and update server configuration

View the current value of the max_connections configuration parameter:

az postgres flexible-server parameter show \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo \
  --name max_connections

{
  "allowedValues": "25-5000",
  "dataType": "Integer",
  "defaultValue": "50",
  "description": "Sets the maximum number of concurrent connections to the database server.",
  "documentationLink": "https://www.postgresql.org/docs/16/runtime-config-connection.html#GUC-MAX-CONNECTIONS",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-demo/configurations/max_connections",
  "isConfigPendingRestart": false,
  "isDynamicConfig": false,
  "isReadOnly": false,
  "name": "max_connections",
  "resourceGroup": "rg-postgres-demo",
  "source": "system-default",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": null,
  "value": "50"
}

Update max_connections to 200:

az postgres flexible-server parameter set \
  --resource-group rg-postgres-demo \
  --server-name postgres-demo \
  --name max_connections \
  --value 200 \
  --source user-override

{
  "allowedValues": "25-5000",
  "dataType": "Integer",
  "defaultValue": "50",
  ...
  "isConfigPendingRestart": true,
  "isDynamicConfig": false,
  "isReadOnly": false,
  "name": "max_connections",
  "source": "user-override",
  ...
  "value": "200"
}

Static parameters such as max_connections set isConfigPendingRestart to true after an update. A restart, or a start after a stop, applies the change to the engine and clears the flag. Dynamic parameters reach the running engine at once, and a setting of a library shared_preload_libraries does not load yet, such as auto_explain.log_min_duration, once a restart has loaded the library.

Stop, start, and restart a server

Stop the flexible server:

az postgres flexible-server stop \
  --resource-group rg-postgres-demo \
  --name postgres-demo

WARNING: Server will be automatically started after 7 days if you do not perform a manual start operation

Verify the server state is Stopped:

az postgres flexible-server show \
  --name postgres-demo \
  --resource-group rg-postgres-demo \
  --query state \
  --output tsv

Stopped

A stopped server keeps its data and refuses connections until it starts again. Start the server again:

az postgres flexible-server start \
  --resource-group rg-postgres-demo \
  --name postgres-demo

Restart the server to apply pending configuration changes:

az postgres flexible-server restart \
  --resource-group rg-postgres-demo \
  --name postgres-demo

Create and scale out an elastic cluster

An elastic cluster is a flexible server whose nodes run the Citus extension, so a table or a schema can be sharded across them. Create a cluster of two nodes with a database named appdb, and open it to every client address:

az postgres flexible-server create \
  --name postgres-cluster-demo \
  --resource-group rg-postgres-demo \
  --location westeurope \
  --admin-user pgadmin \
  --admin-password 'P@ssw0rd2024!' \
  --sku-name Standard_D2ds_v5 \
  --tier GeneralPurpose \
  --version 18 \
  --storage-size 32 \
  --node-count 2 \
  --database-name appdb \
  --public-access 0.0.0.0-255.255.255.255 \
  --yes

{
  "connectionString": "postgresql://pgadmin:P@ssw0rd2024!@postgres-cluster-demo.postgresql.azure.localhost.localstack.cloud:4512/postgres?sslmode=require",
  "host": "postgres-cluster-demo.postgresql.azure.localhost.localstack.cloud:4512",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-postgres-demo/providers/Microsoft.DBforPostgreSQL/flexibleServers/postgres-cluster-demo",
  "location": "West Europe",
  "skuname": "Standard_D2ds_v5",
  "username": "pgadmin",
  "version": "18",
  ...
}

The server reports its cluster:

az postgres flexible-server show \
  --name postgres-cluster-demo \
  --resource-group rg-postgres-demo \
  --query cluster

{
  "clusterSize": 2,
  "defaultDatabaseName": "appdb"
}

az postgres flexible-server list lists a cluster only with --show-cluster, as it does against Azure.

Connect to the cluster’s database and list its nodes. As on Azure, every node listens on one address, on ports from 7000, and every node holds shards, the coordinator included:

export PG_FQDN=$(az postgres flexible-server show --name postgres-cluster-demo --resource-group rg-postgres-demo --query fullyQualifiedDomainName --output tsv)
export PGPASSWORD='P@ssw0rd2024!'
psql "postgresql://pgadmin@${PG_FQDN}/appdb?sslmode=require" \
  -c "SELECT nodename, nodeport, shouldhaveshards FROM pg_dist_node ORDER BY nodeport"

 nodename  | nodeport | shouldhaveshards
-----------+----------+------------------
 127.0.0.1 |     7000 | t
 127.0.0.1 |     7001 | t
(2 rows)

Distribute a table across the nodes and count its shards on each:

psql "postgresql://pgadmin@${PG_FQDN}/appdb?sslmode=require" \
  -c "CREATE TABLE events (id bigint PRIMARY KEY, payload text)" \
  -c "SELECT create_distributed_table('events', 'id')" \
  -c "INSERT INTO events SELECT g, 'event ' || g FROM generate_series(1, 1000) AS g" \
  -c "SELECT nodeport, count(*) AS shards FROM citus_shards WHERE table_name = 'events'::regclass GROUP BY nodeport ORDER BY nodeport"

CREATE TABLE
 create_distributed_table
--------------------------

(1 row)

INSERT 0 1000
 nodeport | shards
----------+--------
     7000 |     16
     7001 |     16
(2 rows)

Scale the cluster out to three nodes:

az postgres flexible-server update \
  --name postgres-cluster-demo \
  --resource-group rg-postgres-demo \
  --node-count 3 \
  --yes \
  --query cluster

{
  "clusterSize": 3,
  "defaultDatabaseName": "appdb"
}

A scale-out adds nodes and leaves the shards where they are, as on Azure. Rebalance them to move shards to the new node:

psql "postgresql://pgadmin@${PG_FQDN}/appdb?sslmode=require" \
  -c "SELECT citus_rebalance_start()" \
  -c "SELECT citus_rebalance_wait()" \
  -c "SELECT nodeport, count(*) AS shards FROM citus_shards WHERE table_name = 'events'::regclass GROUP BY nodeport ORDER BY nodeport"

 citus_rebalance_start
-----------------------
                     1
(1 row)

 citus_rebalance_wait
----------------------

(1 row)

 nodeport | shards
----------+--------
     7000 |     11
     7001 |     11
     7002 |     10
(3 rows)

A cluster holds exactly one database: CREATE DATABASE fails with permission denied to create database, and a database create through the management API is refused.

Delete the cluster:

az postgres flexible-server delete \
  --resource-group rg-postgres-demo \
  --name postgres-cluster-demo \
  --yes

Delete and verify

Delete the flexible server:

az postgres flexible-server delete \
  --resource-group rg-postgres-demo \
  --name postgres-demo \
  --yes

Then list all flexible servers to confirm the resource group is now empty:

az postgres flexible-server list \
  --resource-group rg-postgres-demo

[]

Features

The PostgreSQL Flexible Server emulator supports the following features:

Server lifecycle management: Create, get, update, list, and delete flexible servers with full ARM resource model support.
A real engine behind every server: Each server runs its own PostgreSQL container, reached through the host name and port in fullyQualifiedDomainName.
TLS: Every server serves TLS with a certificate that covers its host name, and refuses a client without TLS while require_secure_transport is on, with the engine’s own no pg_hba.conf entry error, as on Azure.
Stop, start, and restart: A stopped server keeps its data and refuses connections. A start or a restart applies the static parameters changed since the engine started and clears isConfigPendingRestart.
Database management: Create, get, list, and delete user databases, each a real database in the server’s engine.
Firewall rules: Create, get, update, list, and delete IP-based firewall rules with address range validation.
Server configuration: Get, list, and update the server parameters Azure lists. Dynamic parameters reach the running engine at once, and static ones at the next restart.
Elastic clusters: A server created with --node-count (properties.cluster) runs real Citus nodes, lists them in pg_dist_node on one address and ports from 7000, distributes tables and schemas, and scales out with az postgres flexible-server update --node-count or with a redeployed template that asks for more nodes in clusterSize. While a scale-out runs, the cluster reports the state UpdatingCluster and refuses parameter changes as busy, and a scale-in is accepted and changes nothing, as on Azure.
Virtual endpoints: Create, get, update, list, and delete a pair of virtual endpoints. The writer reaches the primary server, and the reader reaches the read replica the members name, or the primary while none does. Each host name keeps its address for the pair’s life while the server it leads to changes, so a client reconnecting with a saved address reaches the current target. A server holds one pair.
Virtual network subnet usage: Count the flexible servers in each subnet of a virtual network.
SKU tiers: Burstable, General Purpose, and Memory Optimized tiers with multiple SKU sizes per tier.
PostgreSQL versions: Versions 11 to 18, with the version required on create, as on Azure.
Storage management: Configurable storage from 32 GB to 16 TB with automatic tier mapping and storage auto-grow support.
Name availability check: Validate server name uniqueness across subscriptions before creation.
Location capabilities: Query available SKUs, storage sizes, and supported PostgreSQL versions per region.
Long-running operations: Server create, update, delete, start, stop, and restart return 202 Accepted with async operation tracking headers.
Bicep and Terraform support: Deploy flexible servers, elastic clusters, databases, and firewall rules using infrastructure-as-code templates.

Limitations

Every server runs PostgreSQL 18: The engine is PostgreSQL 18 whatever version the server reports, and a major version upgrade changes only the reported version.
The endpoint is not an Azure host name: fullyQualifiedDomainName is {server}.postgresql.azure.localhost.localstack.cloud:{port}, not {server}.postgres.database.azure.com, and it embeds the port. Use the value the API returns rather than building a host name yourself.
Elastic clusters need an amd64 Docker host or amd64 emulation: A cluster runs the citusdata/citus image, which is published for linux/amd64 only. On an arm64 host, Docker runs it only with amd64 emulation, such as Rosetta in Docker Desktop or QEMU through binfmt_misc, and a cluster create fails with an error that says so otherwise.
Clients reach a cluster’s coordinator only: The port in fullyQualifiedDomainName leads to the coordinator. The PgBouncer port 6432 and the load-balanced ports 7432 and 8432 are not emulated.
Cluster replicas and restores are not emulated: A read replica or a restore of an elastic cluster is created as a single server.
Cluster settings Azure does not support are accepted: Microsoft Learn lists virtual network injection and storage auto-grow as unsupported for an elastic cluster; the emulator stores both, as it does for a server.
Virtual endpoints do not follow a switchover: A promotion makes the replica an independent server, and a switchover that swaps the roles of a primary and its replica is not emulated, so the writer keeps reaching the server the pair belongs to. Each host name carries a port of its own, like fullyQualifiedDomainName. The LocalStack public certificate covers these host names; the certificate issued by the LocalStack root certificate authority, served when the public one is unavailable, names the server’s own host only, so sslmode=verify-full through a virtual endpoint fails then.
Size-dependent parameter defaults: Azure derives the default of some parameters, such as max_connections, from the compute size, and reports it in defaultValue. The emulator reports one default for every size.
pg_cron is not available: On Azure, the default shared_preload_libraries loads pg_cron as well as pg_stat_statements, on every version but 17. The official PostgreSQL image the emulator runs does not ship pg_cron, so a server preloads pg_stat_statements only, and the nodes of a cluster citus and pg_stat_statements.
Backup and restore: Backup retention days and geo-redundant backup settings are stored but no actual backups are performed. Point-in-time restore is not supported.
Read replicas replicate no data: A read replica is created as its own empty server with its source’s configuration.
Microsoft Entra authentication: The authConfig.activeDirectoryAuth property is stored but Active Directory authentication is not enforced.
Virtual network integration: Delegated subnet and private DNS zone properties are accepted but VNet injection is not performed.
Private endpoint connections: The property is returned in the response but private endpoint connectivity is not implemented.
High availability failover: High availability mode and standby availability zone are stored but no failover mechanism is active.
Firewall rule enforcement: Firewall rules are stored and queryable but are not enforced on actual database connections.
Storage auto-grow enforcement: The auto-grow setting is stored but automatic storage expansion does not occur.

Samples

The following samples demonstrate how to use Azure Database for PostgreSQL flexible server with LocalStack for Azure:

API Coverage

54 of 74 operations implemented

Operation ▲	Implemented ▼

Page 1 of 0

Was this page helpful?