PostgreSQL on Azure Flexible Server: Setup and Best Practices

Azure Database for PostgreSQL Flexible Server is the current recommended PostgreSQL offering on Azure. It replaced the older Single Server product and adds useful features: stop/start capability, flexible compute tiers, and better zone redundancy options. For a Payload CMS deployment, it's the most straightforward managed PostgreSQL option on Azure.

Creating the Server

az postgres flexible-server create \
  --resource-group blog-rg \
  --name shared-postgres-2025 \
  --location centralindia \
  --admin-user blogadmin \
  --admin-password "YourStr0ngP@ss!" \
  --sku-name Standard_B1ms \
  --tier Burstable \
  --storage-size 32 \
  --version 16 \
  --public-access 0.0.0.0-255.255.255.255

Standard_B1ms (Burstable tier) is the cheapest option — 1 vCore, 2GB RAM. For a blog with moderate traffic, it's sufficient. Upgrade to Standard_D2s_v3 (General Purpose) if you see CPU throttling under load.

--public-access 0.0.0.0-255.255.255.255 allows connections from any IP. Restrict this to your Container Apps' outbound IPs in production for better security.

Creating the Database

az postgres flexible-server db create \
  --resource-group blog-rg \
  --server-name shared-postgres-2025 \
  --database-name blogdb

SSL: Required and Non-Negotiable

Azure PostgreSQL Flexible Server enforces SSL connections by default. Your connection string must include sslmode=require:

postgresql://blogadmin:YourStr0ngP@ss!@shared-postgres-2025.postgres.database.azure.com:5432/blogdb?sslmode=require

Without sslmode=require, most PostgreSQL clients will still try to connect without SSL, and Azure will reject the connection. This is the most common cause of "connection refused" errors when migrating from a local PostgreSQL instance.

Verifying Your Connection String

Test the connection from your local machine:

psql "postgresql://blogadmin:YourStr0ngP@ss!@shared-postgres-2025.postgres.database.azure.com:5432/blogdb?sslmode=require"

If you get an error about the client certificate or SSL mode, add sslmode=require. If you get an access denied error, check your firewall rules.

Firewall Rules

Flexible Server uses firewall rules to control inbound connections:

# Allow your current IP (for local development)
az postgres flexible-server firewall-rule create \
  --resource-group blog-rg \
  --name shared-postgres-2025 \
  --rule-name my-dev-machine \
  --start-ip-address $(curl -s ifconfig.me) \
  --end-ip-address $(curl -s ifconfig.me)

# Allow Azure services (Container Apps, etc.)
az postgres flexible-server firewall-rule create \
  --resource-group blog-rg \
  --name shared-postgres-2025 \
  --rule-name AllowAzureServices \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

The 0.0.0.0 to 0.0.0.0 rule is Azure's magic value for "allow connections from any Azure service." It's not a wildcard — it specifically allows traffic originating from within Azure.

Connection Pooling with Payload

Payload CMS uses Drizzle ORM under the hood. The @payloadcms/db-postgres adapter connects to PostgreSQL directly. For production, configure a reasonable pool size:

// apps/cms/src/payload.config.ts
import { postgresAdapter } from '@payloadcms/db-postgres'

export default buildConfig({
  db: postgresAdapter({
    pool: {
      connectionString: process.env.DATABASE_URI,
      max: 10,           // maximum pool connections
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    },
  }),
})

max: 10 is conservative for a Burstable B1ms tier. Azure's Flexible Server has a connection limit tied to the compute tier — B1ms allows roughly 50 concurrent connections. With a pool of 10 per Container Apps replica and up to 3 replicas, you're safely under the limit.

Backing Up and Restoring

Flexible Server includes automatic backups with configurable retention (1–35 days):

# Check backup status
az postgres flexible-server show \
  --name shared-postgres-2025 \
  --resource-group blog-rg \
  --query backup

# Point-in-time restore (creates a new server)
az postgres flexible-server restore \
  --resource-group blog-rg \
  --name restored-postgres-2025 \
  --source-server shared-postgres-2025 \
  --restore-time "2025-03-30T10:00:00Z"

Restores create a new server — your original server remains unchanged. Test your restore procedure before you need it in production.

Monitoring Slow Queries

Enable slow query logging to identify performance problems:

az postgres flexible-server parameter set \
  --resource-group blog-rg \
  --server-name shared-postgres-2025 \
  --name log_min_duration_statement \
  --value 1000   # log queries taking more than 1 second

View logs in the Azure portal under Monitoring > Logs, or stream them with:

az postgres flexible-server logs list \
  --resource-group blog-rg \
  --server-name shared-postgres-2025

Key Takeaways

• Always include ?sslmode=require in your connection string — Azure enforces SSL and connections fail without it
• The 0.0.0.0 to 0.0.0.0 firewall rule allows Azure-internal traffic (Container Apps, etc.) — not all IPs
• Configure connection pool size based on your compute tier's connection limit; B1ms supports ~50 concurrent connections
• Point-in-time restore creates a new server; test your restore procedure before you need it
• Enable log_min_duration_statement to surface slow queries before they become user-facing latency