Upgrading Postgresql to version 12

Development
1

Upgrade Postgresql from v10 to v12

BACKUP EXISTING DATA

Optional but highly recommended step

sudo -u postgres pg_dumpall > backup.sql

Upgrade the service

Install new packages

sudo apt-get update
sudo apt-get install postgresql-12 postgresql-server-dev-12

Stop the existing server

sudo systemctl stop postgresql.service

Convert configuration files

sudo -u postgres /usr/lib/postgresql/12/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/12/main \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/12/bin \
--old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'

Switch ports

#change port to 5432
sudo vim /etc/postgresql/12/main/postgresql.conf

#change port to 5433
sudo vim /etc/postgresql/10/main/postgresql.conf

Start the new server and check the version

sudo systemctl start postgresql.service
sudo -u postgres psql -c "SELECT version();"

Run the generated new cluster script

sudo -u postgres ./analyze_new_cluster.sh

Clean-up up the old version

#uninstalls postgres packages
sudo apt-get remove postgresql-10 postgresql-server-dev-10

#removes the old postgresql directory
sudo rm -rf /etc/postgresql/10/

#delete the old cluster data
sudo -u postgres ./delete_old_cluster.sh
1 Like