How to Upgrade Azure PostgreSQL from v11 to v18 Without Losing Your Mind

Upgrading a production database across seven major versions — jumping straight from PostgreSQL 11 to PostgreSQL 18 on Azure Database for PostgreSQL Flexible Server — sounds like the kind of weekend maintenance window that ages you five years.

PostgreSQL 11 reached End-of-Life (EOL), and staying on it meant missing out on years of query planner improvements, B-tree deduplication, parallelized VACUUM, asynchronous I/O advances, and critical security patches. Thankfully, Azure Flexible Server supports in-place major version upgrades that preserve your server name, private endpoints, firewall rules, and connection strings.

However, clicking the Upgrade button blindly will almost certainly fail on a long-lived v11 instance. Here is the exact step-by-step playbook we used to clear every pre-upgrade roadblock and complete the v11 to v18 upgrade cleanly.

flowchart LR
  A["1. Clear Pre-Upgrade Roadblocks\n(Extensions & SCRAM-SHA-256)"] --> B["2. Run Azure Pre-Check\n(Dry-Run Validation)"]
  B --> C["3. Verify PITR Backup\n(Safety Snapshot)"]
  C --> D["4. Execute In-Place Upgrade\n(v11 → v18)"]
  D --> E["5. Post-Upgrade ANALYZE\n& Re-enable Extensions"]

---

Step 1: Clear the Two Biggest Pre-Upgrade Roadblocks

Before Azure even lets pg_upgrade run under the hood, two legacy settings on older PostgreSQL 11 servers will block validation every time.

1. Incompatible Extensions in shared_preload_libraries

Over the life of a database, teams enable extensions in shared_preload_libraries that either don’t exist in newer major versions or require a different upgrade path. A classic culprit is pg_failover_slots or outdated third-party extensions.

1. Open the Azure Portal → your PostgreSQL Flexible Server → Server parameters.
2. Search for shared_preload_libraries.
3. Remove unsupported extensions (such as pg_failover_slots) and drop any unused legacy extensions inside your databases:

-- Check currently installed extensions across your database
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Drop legacy or unused extensions that block pg_upgrade
DROP EXTENSION IF EXISTS pg_failover_slots;

4. Click Save and restart the server so the updated preload library list takes effect.

2. Migrate Authentication from md5 to scram-sha-256

PostgreSQL 11 defaulted to md5 password hashing, whereas modern PostgreSQL versions (and Azure’s upgrade validator) enforce scram-sha-256. If any user role still has an md5-hashed password stored in pg_authid, the pre-upgrade check will fail, or worse, your application roles won’t be able to authenticate after the upgrade.

First, switch password_encryption to scram-sha-256 in Server parameters and save. Then inspect which roles still carry legacy md5 hashes:

-- Find roles still using legacy MD5 password hashes
SELECT
  rolname,
  rolcanlogin,
  CASE
    WHEN rolpassword LIKE 'md5%' THEN 'MD5 (Needs Re-hash)'
    WHEN rolpassword LIKE 'SCRAM-SHA-256%' THEN 'SCRAM-SHA-256 (Ready)'
    ELSE 'Other / No Password'
  END AS auth_status
FROM pg_authid
WHERE rolcanlogin = true
ORDER BY rolname;

For every role showing MD5 (Needs Re-hash), re-apply its password while password_encryption = 'scram-sha-256' is active:

SET password_encryption = 'scram-sha-256';
ALTER ROLE app_service_user WITH PASSWORD 'your-existing-or-rotated-secret';

---

Step 2: Run the Pre-Upgrade Validation Check

Azure Flexible Server includes a built-in pre-check engine that runs pg_upgrade --check against your metadata without modifying your live data.

1. In the Azure Portal, navigate to Settings → Upgrade on your Flexible Server blade.
2. Select PostgreSQL 18 as the target version.
3. Review the automated Pre-upgrade validation report, or trigger a check via Azure CLI:

az postgres flexible-server upgrade \
  --resource-group rg-production \
  --name pg-prod-flex \
  --version 18 \
  --yes

If validation flags any remaining objects — such as reg* data types (regproc, regclass) stored in user tables or views depending on deprecated system catalog columns — resolve those in a staging clone first.

---

Step 3: Verify Your Safety Backup & PITR Window

Before starting the maintenance window:

1. Open Backup and restore in the Azure Portal and confirm a fresh automated snapshot completed within the last few minutes.
2. Optionally spin up a quick Point-in-Time Restore test server to time how long your dataset takes to upgrade and verify application smoke tests against v18.

---

Step 4: Execute the In-Place Major Version Upgrade

Once pre-checks are green and your maintenance window begins:

1. Put your application in brief maintenance/read-only mode (or drain background job queues) so no long-running transactions hold locks.
2. Go to Settings → Upgrade, select 18, and click Upgrade.
3. Azure will:
   • Run a final pre-check pass
   • Take an automatic pre-upgrade snapshot
   • Execute pg_upgrade in link mode (which upgrades system catalogs in minutes rather than copying terabytes of table files)
   • Boot the server on PostgreSQL 18 with the same hostname, VNet integration, and firewall rules.

---

Step 5: Critical Post-Upgrade Verification & ANALYZE

Here is the step most guides skip: pg_upgrade does not transfer optimizer statistics (pg_statistic). Immediately after upgrading to PostgreSQL 18, the query planner has zero histogram or cardinality stats for your tables, which can cause sudden sequential scans until autovacuum catches up.

Run ANALYZE across all databases right after the server comes back online:

# Generate fresh planner statistics across all databases using parallel jobs
vacuumdb \
  --host=pg-prod-flex.postgres.database.azure.com \
  --username=pgadmin \
  --all \
  --analyze-in-stages \
  --jobs=4

Or directly inside psql for your primary database:

-- Update extensions to their PostgreSQL 18 compatible versions
ALTER EXTENSION pg_stat_statements UPDATE;
ALTER EXTENSION pgcrypto UPDATE;

-- Rebuild query planner statistics immediately
ANALYZE VERBOSE;

Final Checklist Before Closing the Maintenance Window

• Verify Server Version: SELECT version(); confirms PostgreSQL 18.x.
• Re-enable Preload Libraries: If you temporarily removed any extensions that have v18-compatible versions, add them back to shared_preload_libraries.
• Monitor Query Latency: Check pg_stat_statements and Azure Metrics (CPU, IOPS, active connections) for the first 15 minutes of live traffic.