PostgreSQL pg_upgrade: AI-Optimized Technical Reference

Core Technology Overview

Purpose: In-place PostgreSQL major version upgrades without dump/restore operations
Supported Versions: PostgreSQL 9.2+ to current versions
File System Operation: Works at data file level, not logical data level

Operational Modes and Performance

Three Critical Modes

Mode	Speed	Safety	Disk Requirements	Failure Recovery
Copy	Moderate	High	2x database size	Original cluster intact
Link	Fast	Low	Minimal	Both clusters at risk
Clone	Fast	High	Minimal	Filesystem-dependent

Real-World Performance Benchmarks

• Small databases (<50GB): 15-30 minutes (copy), 5-10 minutes (link)
• Medium databases (50-500GB): 30 minutes-2 hours (copy), 10-20 minutes (link)
• Large databases (500GB+): 2-6 hours (copy), 20-60 minutes (link)
• Performance multiplier: --jobs flag cuts time in half for multi-tablespace databases

Critical Performance Note: 500GB database that previously took 14+ hours with dump/restore completed in 45 minutes (copy mode), 8 minutes (link mode)

Configuration Requirements

Essential Pre-Upgrade Commands

-- Check extension compatibility (CRITICAL)
SELECT name, default_version, installed_version 
FROM pg_available_extensions 
WHERE installed_version IS NOT NULL;

Production-Ready pg_upgrade Command

# Mandatory compatibility check
pg_upgrade \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir /usr/lib/postgresql/14/bin \
  --new-bindir /usr/lib/postgresql/17/bin \
  --check

# Actual upgrade with parallel processing
pg_upgrade \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir /usr/lib/postgresql/14/bin \
  --new-bindir /usr/lib/postgresql/17/bin \
  --jobs 4

Authentication Configuration (Prevents Password Hell)

Method 1: pg_hba.conf

local   all             postgres                                peer

Method 2: .pgpass file

localhost:5432:*:postgres:yourpassword
localhost:5433:*:postgres:yourpassword

Critical Failure Modes and Solutions

High-Risk Extension Conflicts

• PostGIS: Especially 2.x to 3.x transitions - career-limiting failures
• TimescaleDB: Version compatibility issues cause upgrade failures
• Custom C extensions: Must be recompiled for target PostgreSQL version

Disk Space Failures

• Failure point: 90% completion when /var fills up
• Requirement: Exactly 2x database size for copy mode
• Monitoring: Use df -h during upgrade process

Authentication Failures

• Symptom: Multiple password prompts during upgrade
• Root cause: pg_upgrade connects to both clusters repeatedly
• Impact: Hour-long manual intervention requirement

Resource Requirements and Costs

Disk Space Reality

• Copy mode: 2x database size (non-negotiable)
• Link mode: Minimal additional space
• Clone mode: Filesystem-dependent, typically minimal

Time Investment

• Planning and testing: 1-2 days minimum for production environments
• Actual upgrade execution: See performance benchmarks above
• Post-upgrade verification: 2-4 hours including statistics updates

Human Expertise Requirements

• Minimum skill level: Database administrator with staging environment access
• Critical knowledge: Extension management, authentication configuration
• Failure recovery skills: Essential for link mode operations

Decision Criteria Matrix

Database Size	Recommended Mode	Downtime Window	Risk Level
<50GB	Copy or dump/restore	30 minutes	Low
50-500GB	Copy (test link in staging)	1-2 hours	Medium
500GB+	Link (after extensive testing)	20-60 minutes	High

Critical Warnings and Gotchas

What Official Documentation Doesn't Emphasize

1. Extension hell is real: Third-party extensions destroy more upgrades than version incompatibilities
2. Link mode point of no return: File modification begins immediately, no rollback possible
3. Replication complexity: Standby server handling requires specialized procedures beyond basic documentation
4. PostgreSQL 18 changes: New --jobs flag behavior affects parallel processing assumptions

Breaking Points That Cause Total Failure

• Disk space exhaustion at 90% completion: Corrupts both old and new clusters
• Extension version mismatches: Upgrade fails after hours of processing
• Authentication configuration errors: Requires manual intervention throughout process
• Link mode failures: Can destroy both source and target clusters simultaneously

Post-Upgrade Mandatory Steps

# Update statistics (NOT OPTIONAL)
vacuumdb --all --analyze-only

# Verify upgrade success
psql -c "SELECT version();"

Critical Note: Generated post-upgrade scripts must be executed, not skipped

Rollback Procedures

Copy Mode (Safe)

• Original cluster remains intact
• Simple service restart to old version
• Zero data loss risk

Link Mode (High Risk)

#!/bin/bash
# Emergency rollback script
systemctl stop postgresql
# Restore old cluster configuration  
systemctl start postgresql@14-main

Warning: May not work if link mode fails mid-process

When NOT to Use pg_upgrade

• Databases under 50GB where dump/restore downtime is acceptable
• First-time PostgreSQL upgrades without staging environment
• Environments with critical custom extensions of unknown compatibility
• Systems without proper backup and recovery procedures
• Teams without database administration expertise

Success Indicators

• --check flag passes without errors
• Extension compatibility verified in staging
• 2x disk space confirmed available
• Authentication configured to avoid password prompts
• Rollback procedure tested and documented
• Post-upgrade statistics update completes successfully