Azure PostgreSQL Lesson Learned #6: Major Upgrade Blocked by Password Auth (The One-Change Fix)
Co‑authored with angesalsaa Symptoms Portal Upgrade action fails or the precheck reports that upgrading with password authentication from 11 isn’t allowed. Users still authenticate with legacy MD5/password (or the server’s auth isn’t set to allow SCRAM‑SHA‑256). Error you can hit: - Fail at precheck. Root Cause PostgreSQL 11 on Flexible Server requires SCRAM to be enabled before attempting an in‑place major version upgrade to higher versions. The service precheck blocks the upgrade if the server is still on password/MD5‑only auth. Why SCRAM? It’s the modern, secure challenge‑response protocol (SCRAM‑SHA‑256) recommended by Postgres. Mentioned in our Public Documentation under Unsupported Configuration Parameter: Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn Contributing Factors No prior auth hardening: password_encryption not set to scram-sha-256. User passwords still stored as MD5 hashes, not re-issued under SCRAM. Clients/drivers unverified for SCRAM support (older libraries may fail). Specific Conditions We Observed Source server on PG 11 (Flexible Server). Upgrade target to a higher supported version via portal. Operational Checks Before you flip the switch, confirm you can safely move authentication: List server auth parameters (portal → Server parameters): Verify these 2 server parameters password_encryption & azure.accepted_password_auth_method = scram-sha-256 and authentication methods include SCRAM. Mitigation Goal: Enable SCRAM and re-issue all passwords → re-run the upgrade. 1) Enable SCRAM on the server Portal: Server parameters → set: password_encryption = scram-sha-256 azure.accepted_password_auth_method = scram-sha-256 Check Important Note: Connectivity with SCRAM - Azure Database for PostgreSQL | Microsoft Learn These are dynamic properties and don't require server restart. Verify client/driver compatibility Ensure your application drivers (JDBC, Npgsql, libpq, etc.) support SCRAM before enforcing it. Update client libraries if needed. Re‑run the Major Version Upgrade Portal: Overview → Upgrade → select target major version → Upgrade. Post‑Resolution Upgrade completed successfully. Authentication now uses SCRAM‑SHA‑256; users continue connecting with updated passwords. No further precheck blocks on auth. Prevention & Best Practices Standardize on SCRAM (password_encryption = scram-sha-256) across all environments. Inventory roles and rotate passwords under SCRAM before your upgrade window. Validate drivers in CI/CD for SCRAM support to avoid runtime surprises. Read the upgrade docs (concepts + how‑to) and version policy so you understand supported targets and timelines. Understand Unsupported Scenarios: Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn Why This Matters Skipping this step causes failed upgrades, longer downtime, and emergency rollbacks. Moving to SCRAM not only unblocks the upgrade but also improves security posture (MD5 is deprecated in the community). Key Takeaways Issue: PG11 → higher major upgrade blocked due to password/MD5 authentication. Fix: Enable SCRAM and reset all role passwords, then retry the upgrade. References SCRAM in Azure Database for PostgreSQL – Flexible Server (how to enable & verify) — https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/security-connect-scram Authentication parameters (incl. password_encryption) — https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/param-connections-authentication-authentication Major version upgrade: concepts & steps — Concepts · https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-perform-major-version-upgradeAzure PostgreSQL Lesson Learned #8: Post-Upgrade Performance Surprises (The One-Step Fix)
Co‑authored with angesalsaa Symptoms Upgrade from PostgreSQL 12 → higher version succeeds. After migration, workloads show: Queries running slower than before. Unexpected CPU spikes during normal operations. No obvious errors in logs or connectivity issues. Root Cause Missing or stale statistics can lead to bad query plans, which in turn might degrade performance and consume excessive memory. After a major version upgrade, the query planner relies on outdated or default estimates because the optimizer’s learned patterns are not refreshed. This often results in: Sequential scans instead of index scans. Inefficient join strategies. Increased CPU and memory usage. Contributing Factors Large tables with skewed data distributions. Complex queries with multiple joins. Workloads dependent on accurate cost estimates. Specific Conditions We Observed Any source server version can be impacted once you upgrade to higher version. No ANALYZE or VACUUM run post-upgrade. Operational Checks Before troubleshooting, confirm: Query plans differ significantly from pre-upgrade. pg_stats indicates outdated or missing statistics. Mitigation Goal: Refresh statistics so the planner can optimize queries. Run ANALYZE on all tables: ANALYZE; Important Notes: These commands are safe and online. For very large datasets, consider running during low-traffic windows. We recommend running the ANALYZE command in each database to refresh the pg_statistic table. Post-Resolution Queries return to expected performance. CPU utilization stabilizes. Execution plans align with indexes and cost-based optimization. Prevention & Best Practices Always schedule ANALYZE immediately after major upgrades. Automate stats refresh in your upgrade runbook. Validate plans for critical queries before going live. Why This Matters Skipping this step can lead to: Hours of degraded performance. Emergency escalations and customer dissatisfaction. Misdiagnosis as engine regression when it’s just missing stats. Key Takeaways Issue: Post-upgrade query slowness and CPU spikes due to stale/missing statistics. Fix: Run ANALYZE immediately after upgrade. Pro Tip: Automate this in CI/CD or maintenance scripts for zero surprises. References Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn
