How to upgrade PostgreSQL from 11 to 12

Juraj Kostolanský•October 03, 2019

The new PostgreSQL 12 has just been released. There are multiple ways to upgrade from the old version 11, and the easiest one is by using the pg_upgrade tool. Here is a quick tutorial for Ubuntu (or Debian) systems. And, please, do not forget to back up your data!

Update packages and install the new PostgreSQL 12.

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

Check if there are any differences in the config files.

diff /etc/postgresql/11/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf
diff /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf

Stop the PostgreSQL service.

sudo systemctl stop postgresql.service

sudo su postgres

Check clusters (notice the --check argument, this will not change any data).

/usr/lib/postgresql/12/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/11/main \
  --new-datadir=/var/lib/postgresql/12/main \
  --old-bindir=/usr/lib/postgresql/11/bin \
  --new-bindir=/usr/lib/postgresql/12/bin \
  --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
  --check

Migrate the data (without the --check argument).

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

Go back to the regular user.

exit

Swap the ports for the old and new PostgreSQL versions.

sudo vim /etc/postgresql/12/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"

sudo vim /etc/postgresql/11/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"

Start the PostgreSQL service.

sudo systemctl start postgresql.service

sudo su postgres

Check the new PostgreSQL version.

psql -c "SELECT version();"

Run the generated analyze_new_cluster script.

./analyze_new_cluster.sh

Back to normal user.

exit

Check which old PostgreSQL packages are installed.

apt list --installed | grep postgresql

Remove the old PostgreSQL packages (from the listing above).

sudo apt-get remove postgresql-11 postgresql-server-dev-11

Remove the old configuration.

sudo rm -rf /etc/postgresql/11/

sudo su postgres

Finally, drop the old cluster data.

./delete_old_cluster.sh

Done!

Juraj Kostolanský

I'm a software engineer from Slovakia working as a full-stack Ruby on Rails web developer and system administrator.

Home•All posts