How to upgrade PostgreSQL 12 to version 14
Create a data backup
Optional but strongly advised: backup all existing data.
sudo -u postgres pg_dumpall > backup.sql
Perform the upgrade
- Update and install packages
sudo apt-get update
sudo apt-get install postgresql-14 postgresql-server-dev-14
- Halt the old server
sudo systemctl stop postgresql.service
- Migrate configuration files
sudo -u postgres /usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
Note the output of this step as it may contain instructions that you need to do to finish the migration (see step 6).
- Reconfigure server ports
sudo vim /etc/postgresql/14/main/postgresql.conf # Set port to 5432
sudo vim /etc/postgresql/12/main/postgresql.conf # Set port to 5433
- Activate new server and verify version
sudo systemctl start postgresql.service
sudo -u postgres psql -c "SELECT version();"
- Execute new cluster script
On Postgres 13 and below do
sudo -u postgres ./analyze_new_cluster.sh
On Postgres 14 and above run the output from step 3 above.
Reset user passwords
PostgreSQL v14 defaults to scram-sha-256 for password hashing. Redefine all existing passwords.
sudo -u postgres psql
postgres=# \password $USER
Or, modify /etc/postgresql/14/main/pg_hba.conf to switch back to md5. But be cautious, as future versions may not support MD5.
Remove old version
- Remove old packages
sudo apt-get remove postgresql-12 postgresql-server-dev-12
- Delete configuration and data
sudo rm -rf /etc/postgresql/12/
sudo -u postgres ./delete_old_cluster.sh