Azure

How to troubleshoot Azure Database for PostgreSQL connection and performance issues

Understanding Azure Database for PostgreSQL Connectivity

Azure Database for PostgreSQL connection and performance issues arise from firewall misconfiguration, SSL requirements, resource exhaustion, and query optimization problems. This guide covers diagnostics for both Flexible Server and Single Server tiers.

Why This Problem Matters in Production

In enterprise Azure environments, Azure Database for PostgreSQL connection and performance issues rarely occur in isolation. They typically surface during peak usage periods, complex deployment scenarios, or when multiple services interact under load. Understanding the underlying architecture helps you move beyond symptom-level fixes to root cause resolution.

Before diving into the diagnostic commands below, it is important to understand the service’s operational model. Azure distributes workloads across multiple fault domains and update domains. When problems arise, they often stem from configuration drift between what was deployed and what the service runtime expects. This mismatch can result from ARM template changes that were not propagated, manual portal modifications that bypassed your infrastructure-as-code pipeline, or service-side updates that changed default behaviors.

Production incidents involving Azure Database for PostgreSQL connection and performance typically follow a pattern: an initial trigger event causes a cascading failure that affects dependent services. The key to efficient troubleshooting is isolating the blast radius early. Start by confirming whether the issue is isolated to a single resource instance, affects an entire resource group, or spans the subscription. This scoping exercise determines whether you are dealing with a configuration error, a regional service degradation, or a platform-level incident.

The troubleshooting approach in this guide follows the industry-standard OODA loop: Observe the symptoms through metrics and logs, Orient by correlating findings with known failure patterns, Decide on the most likely root cause and remediation path, and Act by applying targeted fixes. This structured methodology prevents the common anti-pattern of random configuration changes that can make the situation worse.

Service Architecture Background

To troubleshoot Azure Database for PostgreSQL connection and performance effectively, you need a mental model of how the service operates internally. Azure services are built on a multi-tenant platform where your resources share physical infrastructure with other customers. Resource isolation is enforced through virtualization, network segmentation, and quota management. When you experience performance degradation or connectivity issues, understanding which layer is affected helps you target your diagnostics.

The control plane handles resource management operations such as creating, updating, and deleting resources. The data plane handles the runtime operations that your application performs, such as reading data, processing messages, or serving requests. Control plane and data plane often have separate endpoints, separate authentication requirements, and separate rate limits. A common troubleshooting mistake is diagnosing a data plane issue using control plane metrics, or vice versa.

Azure Resource Manager (ARM) orchestrates all control plane operations. When you create or modify a resource, the request flows through ARM to the resource provider, which then provisions or configures the underlying infrastructure. Each step in this chain has its own timeout, retry policy, and error reporting mechanism. Understanding this chain helps you interpret error messages and identify which component is failing.

Common Connection Errors

Error Message Cause Resolution
could not connect to server: Connection timed out Firewall blocking port 5432 Add client IP to firewall rules
FATAL: no pg_hba.conf entry for host Client IP not in allowed list Add firewall rule for IP range
FATAL: password authentication failed Wrong credentials Reset password in Azure portal
SSL connection is required SSL enforcement enabled Add sslmode=require to connection string
sslmode value invalid when SSL support is not compiled in Client libpq missing SSL Recompile libpq with –with-openssl
too many connections for role Connection pool exhausted Use connection pooling (PgBouncer)

Firewall Configuration

# List current firewall rules
az postgres flexible-server firewall-rule list \
  --resource-group "my-rg" \
  --name "my-postgres" \
  -o table

# Add a specific IP
az postgres flexible-server firewall-rule create \
  --resource-group "my-rg" \
  --name "my-postgres" \
  --rule-name "AllowMyIP" \
  --start-ip-address "203.0.113.50" \
  --end-ip-address "203.0.113.50"

# Allow all Azure services (for testing only)
az postgres flexible-server firewall-rule create \
  --resource-group "my-rg" \
  --name "my-postgres" \
  --rule-name "AllowAzureServices" \
  --start-ip-address "0.0.0.0" \
  --end-ip-address "0.0.0.0"

# Allow all IPs (testing only — NOT for production)
az postgres flexible-server firewall-rule create \
  --resource-group "my-rg" \
  --name "my-postgres" \
  --rule-name "AllowAll" \
  --start-ip-address "0.0.0.0" \
  --end-ip-address "255.255.255.255"

Connection Strings

# Standard connection string (Flexible Server)
psql "host=my-postgres.postgres.database.azure.com port=5432 dbname=mydb user=myadmin sslmode=require"

# Connection string for application
postgresql://myadmin@my-postgres:password@my-postgres.postgres.database.azure.com:5432/mydb?sslmode=require

SSL Configuration

# Python with psycopg2
import psycopg2

conn = psycopg2.connect(
    host="my-postgres.postgres.database.azure.com",
    database="mydb",
    user="myadmin",
    password="password",
    sslmode="require",
    # Download CA cert from https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-networking-ssl-tls
    sslrootcert="/path/to/DigiCertGlobalRootCA.crt.pem"
)

Connection Pooling with PgBouncer

Flexible Server has built-in PgBouncer. Enable it to handle connection pooling at the server level.

# Enable built-in PgBouncer
az postgres flexible-server parameter set \
  --resource-group "my-rg" \
  --server-name "my-postgres" \
  --name "pgbouncer.enabled" \
  --value "true"

# Set pool mode
az postgres flexible-server parameter set \
  --resource-group "my-rg" \
  --server-name "my-postgres" \
  --name "pgbouncer.default_pool_size" \
  --value "50"

# Connect through PgBouncer (port 6432)
psql "host=my-postgres.postgres.database.azure.com port=6432 dbname=mydb user=myadmin sslmode=require"

Correlation and Cross-Service Diagnostics

Modern Azure architectures involve multiple services working together. A problem in Azure Database for PostgreSQL connection and performance may actually originate in a dependent service. For example, a database timeout might be caused by a network security group rule change, a DNS resolution failure, or a Key Vault access policy that prevents secret retrieval for the connection string.

Use Azure Resource Graph to query the current state of all related resources in a single query. This gives you a snapshot of the configuration across your entire environment without navigating between multiple portal blades. Combine this with Activity Log queries to build a timeline of changes that correlates with your incident window.

Application Insights and Azure Monitor provide distributed tracing capabilities that follow a request across service boundaries. When a user request touches multiple Azure services, each service adds its span to the trace. By examining the full trace, you can see exactly where latency spikes or errors occur. This visibility is essential for troubleshooting in microservices architectures where a single user action triggers operations across dozens of services.

For complex incidents, consider creating a war room dashboard in Azure Monitor Workbooks. This dashboard should display the key metrics for all services involved in the affected workflow, organized in the order that a request flows through them. Having this visual representation during an incident allows the team to quickly identify which service is the bottleneck or failure point.

Performance Diagnostics

Query Performance Insight

-- Enable pg_stat_statements (requires server restart first time)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    total_exec_time::numeric(10,2) AS total_ms,
    rows,
    left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Find queries with most total time
SELECT
    calls,
    total_exec_time::numeric(10,2) AS total_ms,
    (total_exec_time / calls)::numeric(10,2) AS avg_ms,
    left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Reset statistics
SELECT pg_stat_statements_reset();

Index Optimization

-- Find missing indexes (unused sequential scans on large tables)
SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 10000
  AND idx_scan < seq_scan
ORDER BY seq_tup_read DESC;

-- Find unused indexes (candidates for removal)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Connection Monitoring

-- Current connections by state
SELECT
    state,
    count(*) AS connection_count,
    max(now() - state_change) AS max_idle_time
FROM pg_stat_activity
WHERE datname = 'mydb'
GROUP BY state;

-- Long-running queries
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle'
ORDER BY duration DESC;

-- Kill a long-running query
SELECT pg_terminate_backend(pid);

Server Parameters

# List server parameters
az postgres flexible-server parameter list \
  --resource-group "my-rg" \
  --server-name "my-postgres" \
  --query "[?contains(name,'connection')]" -o table

# Increase max connections
az postgres flexible-server parameter set \
  --resource-group "my-rg" \
  --server-name "my-postgres" \
  --name "max_connections" \
  --value "200"

# Tune work_mem for complex queries
az postgres flexible-server parameter set \
  --resource-group "my-rg" \
  --server-name "my-postgres" \
  --name "work_mem" \
  --value "16384"

# Enable auto-explain for slow query logging
az postgres flexible-server parameter set \
  --resource-group "my-rg" \
  --server-name "my-postgres" \
  --name "auto_explain.log_min_duration" \
  --value "5000"

Performance Baseline and Anomaly Detection

Effective troubleshooting requires knowing what normal looks like. Establish performance baselines for Azure Database for PostgreSQL connection and performance that capture typical latency distributions, throughput rates, error rates, and resource utilization patterns across different times of day, days of the week, and seasonal periods. Without these baselines, you cannot distinguish between a genuine degradation and normal workload variation.

Azure Monitor supports dynamic alert thresholds that use machine learning to automatically learn your workload's patterns and alert only on statistically significant deviations. Configure dynamic thresholds for your key metrics to reduce false positive alerts while still catching genuine anomalies. The learning period requires at least three days of historical data, so deploy dynamic alerts well before you need them.

Create a weekly health report that summarizes the key metrics for Azure Database for PostgreSQL connection and performance and highlights any trends that warrant attention. Include the 50th, 95th, and 99th percentile latencies, the total error count and error rate, the peak utilization as a percentage of provisioned capacity, and any active alerts or incidents. Distribute this report to the team responsible for the service so they maintain awareness of the service's health trajectory.

When a troubleshooting investigation reveals a previously unknown failure mode, add it to your team's knowledge base along with the diagnostic steps and resolution. Over time, this knowledge base becomes an invaluable resource that accelerates future troubleshooting efforts and reduces dependency on individual experts. Structure the entries using a consistent format: symptoms, diagnostic commands, root cause analysis, resolution steps, and preventive measures.

Scaling

# Scale up compute
az postgres flexible-server update \
  --resource-group "my-rg" \
  --name "my-postgres" \
  --sku-name "Standard_D4ds_v5" \
  --tier "GeneralPurpose"

# Scale storage (can only increase, not decrease)
az postgres flexible-server update \
  --resource-group "my-rg" \
  --name "my-postgres" \
  --storage-size 256

Retry Logic

import psycopg2
import time

def connect_with_retry(max_retries=5, base_delay=1):
    for attempt in range(max_retries):
        try:
            conn = psycopg2.connect(
                host="my-postgres.postgres.database.azure.com",
                database="mydb",
                user="myadmin",
                password="password",
                sslmode="require",
                connect_timeout=10
            )
            return conn
        except psycopg2.OperationalError as e:
            if attempt == max_retries - 1:
                raise
            delay = base_delay * (2 ** attempt)
            print(f"Connection attempt {attempt + 1} failed: {e}. Retrying in {delay}s...")
            time.sleep(delay)
    return None

Monitoring and Alerting Strategy

Reactive troubleshooting is expensive. For every hour spent diagnosing a production issue, organizations lose revenue, customer trust, and engineering productivity. A proactive monitoring strategy for Azure Database for PostgreSQL connection and performance should include three layers of observability.

The first layer is metric-based alerting. Configure Azure Monitor alerts on the key performance indicators specific to this service. Set warning thresholds at 70 percent of your limits and critical thresholds at 90 percent. Use dynamic thresholds when baseline patterns are predictable, and static thresholds when you need hard ceilings. Dynamic thresholds use machine learning to adapt to your workload's natural patterns, reducing false positives from expected daily or weekly traffic variations.

The second layer is log-based diagnostics. Enable diagnostic settings to route resource logs to a Log Analytics workspace. Write KQL queries that surface anomalies in error rates, latency percentiles, and connection patterns. Schedule these queries as alert rules so they fire before customers report problems. Consider implementing a log retention strategy that balances diagnostic capability with storage costs, keeping hot data for 30 days and archiving to cold storage for compliance.

The third layer is distributed tracing. When Azure Database for PostgreSQL connection and performance participates in a multi-service transaction chain, distributed tracing via Application Insights or OpenTelemetry provides end-to-end visibility. Correlate trace IDs across services to pinpoint exactly where latency or errors originate. Without this correlation, troubleshooting multi-service failures becomes a manual, time-consuming process of comparing timestamps across different log streams.

Beyond alerting, implement synthetic monitoring that continuously tests critical user journeys even when no real users are active. Azure Application Insights availability tests can probe your endpoints from multiple global locations, detecting outages before your users do. For Azure Database for PostgreSQL connection and performance, create synthetic tests that exercise the most business-critical operations and set alerts with a response time threshold appropriate for your SLA.

Operational Runbook Recommendations

Document the troubleshooting steps from this guide into your team's operational runbook. Include the specific diagnostic commands, expected output patterns for healthy versus degraded states, and escalation criteria for each severity level. When an on-call engineer receives a page at 2 AM, they should be able to follow a structured decision tree rather than improvising under pressure.

Consider automating the initial diagnostic steps using Azure Automation runbooks or Logic Apps. When an alert fires, an automated workflow can gather the relevant metrics, logs, and configuration state, package them into a structured incident report, and post it to your incident management channel. This reduces mean time to diagnosis (MTTD) by eliminating the manual data-gathering phase that often consumes the first 15 to 30 minutes of an incident response.

Implement a post-incident review process that captures lessons learned and feeds them back into your monitoring and runbook systems. Each incident should result in at least one improvement to your alerting rules, runbook procedures, or service configuration. Over time, this continuous improvement cycle transforms your operations from reactive fire-fighting to proactive incident prevention.

Finally, schedule regular game day exercises where the team practices responding to simulated incidents. Azure Chaos Studio can inject controlled faults into your environment to test your monitoring, alerting, and runbook effectiveness under realistic conditions. These exercises build muscle memory and identify gaps in your incident response process before real incidents expose them.

Summary

PostgreSQL connectivity issues resolve by checking firewall rules (az postgres flexible-server firewall-rule list), verifying SSL configuration (sslmode=require), enabling built-in PgBouncer for connection pooling (port 6432), and implementing retry logic with exponential backoff for transient errors. For performance, enable pg_stat_statements to find slow queries, check for missing indexes, and tune server parameters like work_mem and max_connections.

Leave a Reply