pg_upgrade - Upgrade PostgreSQL Without the Dump/Restore Hell

I've spent more weekends than I care to count sitting in a data center watching pg_dump crawl through 500GB databases. pg_upgrade saved my sanity.

The Traditional Upgrade Nightmare

Before pg_upgrade, upgrading PostgreSQL major versions meant:

• Dumping your entire database with pg_dump (6 hours for our main DB)
• Installing the new PostgreSQL version
• Restoring everything with pg_restore (another 8 hours if you're lucky)
• Praying nothing broke during the 14-hour maintenance window

I learned this the hard way during a PostgreSQL 9.6 to 11 upgrade that took 16 hours and required rolling back at 3 AM because we hit extension compatibility issues nobody tested.

How pg_upgrade Actually Works

pg_upgrade works at the file system level instead of dumping logical data. It literally moves or links your data files from the old PostgreSQL installation to the new one.

Three modes that matter:

• Copy mode: Copies all data files (safe but slow, 2x disk space needed)
• Link mode: Hard links files (fast but scary - if it fails halfway through, you're fucked)
• Clone mode: File system clones where supported (best of both worlds)

Real-World Performance

Our 500GB production database that used to take 14+ hours with dump/restore? pg_upgrade in copy mode: 45 minutes. Link mode: 8 minutes.

Small databases under 50GB typically upgrade in 10-20 minutes with copy mode. Medium databases (50-500GB) take 30 minutes to 2 hours. Large databases benefit massively from link mode but carry the risk.

Version Support Reality Check

pg_upgrade supports PostgreSQL 9.2+ to current versions. You can jump multiple major versions in one go - I've successfully upgraded 9.6 directly to 14 without intermediate steps.

PostgreSQL 18 GA was released September 25, 2025. The new --jobs flag in recent versions lets you parallelize the upgrade, cutting time in half for databases with multiple tablespaces.

Things That Will Bite You

• Extension hell: Third-party extensions are where upgrades go to die. PostGIS upgrades have ruined more of my weekends than I want to admit.
• Authentication failures: The upgrade process connects multiple times. Set up peer authentication or you'll be typing passwords for an hour.
• Disk space: Copy mode needs 2x your database size. I've seen upgrades fail at 90% completion because /var filled up.
• Replication: Standby servers need special handling. The docs make it sound simple - it's not.

Link mode is fast but unforgiving. If the upgrade fails after it starts linking files, your old cluster is toast. Always test in staging first and have backups. I've had to restore from backups twice when link mode upgrades failed spectacularly.

After fucking up my first pg_upgrade attempt in 2018, I've learned what actually matters vs. what the documentation glosses over.

Before You Even Think About Upgrading

Step 1: Test in staging or you're an idiot
Seriously. I can't stress this enough. That "simple" PostgreSQL 12 to 13 upgrade? Broke our entire authentication system because we had custom pg_hba.conf settings that weren't compatible.

Step 2: Check your extensions
Run this first:

SELECT name, default_version, installed_version 
FROM pg_available_extensions 
WHERE installed_version IS NOT NULL;

PostGIS, TimescaleDB, and other third-party extensions will ruin your day. I've had upgrades fail because of a single obscure extension that wasn't compatible with the target PostgreSQL version.

Step 3: Disk space reality check
Copy mode needs 2x your database size. Don't fuck around with this - I've seen upgrades fail at 90% completion and corrupt data because someone thought "close enough" on disk space was acceptable.

The Actual pg_upgrade Command That Works

Stop overthinking it. Here's what actually works:

## Always run the check first
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

## If check passes, run the real upgrade
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

Critical flags that matter:

• --check: Tests compatibility without touching anything. The docs say it's optional - it's not.
• --jobs 4: Parallel processing. Use your CPU core count or number of tablespaces, whichever is higher.
• --link: Fast but dangerous. Only use if you're confident and have backups.

Performance Tips From Hard Experience

For databases under 100GB: Copy mode, 15-30 minutes typical
For databases 100-500GB: Copy mode, 30 minutes to 2 hours depending on disk speed
For databases 500GB+: Link mode or plan for a long weekend

The --jobs flag is magic for large databases. Our 800GB database went from 3 hours to 45 minutes with --jobs 8.

What Actually Breaks During Upgrades

Authentication problems: pg_upgrade connects to both clusters multiple times. Either set up peer authentication in pg_hba.conf or create a .pgpass file:

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

Extension upgrade failures: The worst offenders:

• PostGIS (especially versions 2.x to 3.x)
• TimescaleDB with version compatibility issues
• Custom C extensions that aren't recompiled

Out of disk space: Happens at the worst possible time. Monitor with df -h during the upgrade.

After the Upgrade Completes

Don't celebrate yet. Run these immediately:

## Update statistics (this is not optional)
vacuumdb --all --analyze-only

## Check that everything actually works
psql -c \"SELECT version();\"

The generated scripts in your data directory aren't suggestions - run them. I've seen subtle performance issues because people skipped the post-upgrade maintenance.

When Everything Goes to Shit

If pg_upgrade fails:

1. Don't panic - your old cluster is intact (if you used copy mode)
2. Read the actual error message - don't just restart
3. Check logs in the pg_upgrade output directory
4. Most common fixes: extension issues, authentication problems, or disk space

I keep a rollback script ready:

#!/bin/bash
## Stop new cluster
systemctl stop postgresql
## Restore old cluster configuration  
systemctl start postgresql@14-main

Link mode failures are scarier because you might lose both clusters. This is why I only use link mode after successful copy mode tests in staging.