Skip to main content

Upgrade

PostgreSQL Upgrade​

Pre-upgrade Checklist​

Review PostgreSQL 17 release notes and compatibility

Test upgrade process in development environment

Backup all databases using pg_dumpall

Verify application compatibility with PostgreSQL 17

Plan downtime and rollback strategy

Upgrade Process using pg_upgrade

bash
# Stop PostgreSQL service
sudo systemctl stop postgresql
# Install PostgreSQL 17 alongside existing version
sudo apt-get install postgresql-17
# Run pg_upgrade
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check
# Perform actual upgrade
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin
# Start PostgreSQL 17
sudo systemctl start postgresql

Post-upgrade Tasks​

bash
# Update statistics
sudo -u postgres /usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages
# Remove old cluster (after verification)
sudo -u postgres ./delete_old_cluster.sh