PostgreSQL pg_upgrade Guide

Upgrading PostgreSQL with pg_upgrade

Major version upgrades (e.g., PostgreSQL 15 → 16) require pg_upgrade. Unlike minor updates, major upgrades change the internal data storage format. pg_upgrade handles this conversion efficiently, especially with --link mode that creates hard links instead of copying data files.

Pre-Upgrade Checklist

1. Take a full backup (pgBackRest or pg_basebackup)
2. Check extension compatibility with the target version
3. Review the release notes for breaking changes
4. Test the upgrade on a staging environment first
5. Plan for downtime (typically 5-30 minutes with link mode)

Link Mode vs Copy Mode

Step-by-Step Upgrade

# 1. Install the new PostgreSQL version
sudo apt install postgresql-16

# 2. Stop both clusters
sudo systemctl stop postgresql

# 3. Run pg_upgrade with link mode
sudo -u postgres pg_upgrade \
  --old-datadir=/var/lib/postgresql/15/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/15/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --link --check  # Dry run first

# 4. If check passes, run for real (remove --check)
sudo -u postgres pg_upgrade \
  --old-datadir=/var/lib/postgresql/15/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/15/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --link

# 5. Start the new version
sudo systemctl start postgresql@16-main

# 6. Run post-upgrade analyze
sudo -u postgres /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages

Post-Upgrade Tasks

• Run ANALYZE on all databases to update statistics
• Test application queries against the new version
• Remove old cluster files once verified: ./delete_old_cluster.sh
• Update connection strings and monitoring to point to the new port/version

Eazy SaaS Tip: Always use --check mode first. It validates everything without making changes. For databases over 500 GB, link mode is essential — copy mode would take hours of downtime.