How to fix Azure: Database is read-only during maintenance in PostgreSQL

Verify that maintenance is actually running during the error occurrence:

1. Open your Azure Database for PostgreSQL resource in the Azure Portal
2. Go to "Maintenance + updates" section
3. Check if "Scheduled maintenance" shows a current or upcoming maintenance window
4. Check the "High Availability" section for failover status
5. Look for notifications about ongoing maintenance (blue information banners)

If maintenance is running, the database will return to read-write mode automatically when maintenance completes (typically 1-2 minutes for HA failover). No action is required except to retry your application.

Check if storage usage triggered read-only mode:

1. Go to the "Metrics" section in Azure Portal
2. Look for "Storage used" metric
3. If storage is above 95% or available capacity is below 5 GiB, this triggered read-only mode
4. You must increase storage to restore read-write access (not automatic)

# Check storage via psql if you can connect
SELECT pg_database_size('your_database') as size_bytes;

# Convert to GiB (divide by 1073741824)

If storage is the cause, proceed to Step 3. Otherwise, if maintenance is running, wait for it to complete.

If storage usage exceeded 95%, you must increase the allocated storage:

1. In Azure Portal, go to "Compute + storage" settings
2. Increase the "Storage" value (in GiB) to a higher amount
3. Click "Save" to apply the change
4. Storage increase is online and requires no downtime (unless crossing 4 TiB limit)
5. The server will automatically return to read-write mode

Storage scaling typically takes a few minutes to complete. Monitor the "Notifications" section to confirm the operation succeeded.

To calculate required storage:
- Add 20-30% buffer to current usage
- If currently at 95% of 100 GiB, increase to at least 150 GiB
- Enable "Storage autogrow" to prevent future occurrences

If the error persists after maintenance completes, DNS configuration may be stale. Update connections to use the fully qualified domain name instead of hardcoded IPs:

Bad (hardcoded IP):

const connectionString = "postgresql://user:[email protected]:5432/db";

Good (FQDN):

const connectionString = "postgresql://user:[email protected]:5432/db";

FQDN allows Azure to automatically route to the current primary instance after failover. Your server FQDN is shown in the Azure Portal under "Server name" (format: <servername>.postgres.database.azure.com).

Update connections in:
- Application environment variables
- Connection pooler configuration (PgBouncer, pgpool2)
- ORM connection strings (Prisma, SQLAlchemy, etc.)
- Direct database client code

Confirm that your application network correctly resolves the FQDN to the primary IP:

# From application container/machine
nslookup myserver.postgres.database.azure.com
host myserver.postgres.database.azure.com
dig myserver.postgres.database.azure.com

# For private DNS zones (if using)
nslookup myserver.postgres.database.azure.com   # from private network

Expected result: Should resolve to the primary server's current IP address. If using a private DNS zone, verify the A record points to the correct primary IP.

If DNS is cached or stale:
- Clear DNS cache on application server: ipconfig /flushdns (Windows) or restart networking (Linux)
- Update TTL in private DNS zone to 300 seconds or lower for faster failover recovery
- Check firewall/NSG rules to ensure outbound DNS queries succeed

Add resilience to your application to automatically retry failed transactions:

async function executeWithRetry(
  operation: () => Promise<void>,
  maxRetries = 3
): Promise<void> {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      await operation();
      return;
    } catch (error: any) {
      // Check for read-only transaction errors
      if (
        error.code === '25006' || // read-only SQL transaction
        error.message?.includes('read-only transaction')
      ) {
        if (attempt === maxRetries - 1) throw error;
        
        // Exponential backoff: 1s, 2s, 4s
        const delayMs = Math.pow(2, attempt) * 1000;
        await new Promise(r => setTimeout(r, delayMs));
        continue;
      }
      throw error; // Different error, fail immediately
    }
  }
}

// Usage
await executeWithRetry(async () => {
  await prisma.user.create({ data: { name: 'Alice' } });
});

This handles brief maintenance windows automatically without user intervention.

Configure Azure Alerts to notify you before storage reaches critical levels:

1. In Azure Portal, go to "Alerts" section
2. Create alert rule with conditions:
- Metric: "Storage used" or "Storage percent"
- Condition: Greater than 80% (for early warning)
- Action: Email notification
3. Create second alert for 90% to escalate urgency
4. Enable "Storage autogrow" in "Compute + storage" settings

PowerShell command:

New-AzMetricAlertRuleV2 -Name "PG-Storage-80pct" `
  -ResourceGroupName "mygroup" `
  -TargetResourceName "myserver" `
  -MetricName "storage_percent" `
  -Operator "GreaterThan" `
  -Threshold 80 `
  -WindowSize "PT5M" `
  -Frequency "PT1M"

With proper monitoring and autogrow enabled, you can prevent storage-triggered read-only mode from happening in production.