PostgreSQL

Upgrade PostgreSQL to a new version

PostgreSQL
PostgreSQL

In this guide I will explain how to upgrade postgresql to a new version. For example on the new Ubuntu 22.04 LTS version you get a message that PostgreSQL version 12 is obsolete. Before upgrading to the new Ubuntu version you should upgrade your PostgreSQL.

Checking the current PostgreSQL version

First we need to check which current versions of PostgreSQL are installed. This example is based on Debian or Ubuntu based systems. To check the installed versions we will use the dpkg command. To see the supported PostgreSQL versions check the offical website.

sudo dpkg -l | grep postgresql

postgresql-12             12.6-0ubuntu0.20.10.1  amd64  object-relational SQL database, version 12 server
postgresql-client-12      12.6-0ubuntu0.20.10.1  amd64  front-end programs for PostgreSQL 12
postgresql-client-common  225ubuntu1             all    manager for multiple PostgreSQL client versions
postgresql-common         225ubuntu1             all    PostgreSQL database-cluster manager

As you can see only PostgreSQL version 12 is installed on this system.

Installing a new PostgreSQL version

PostgreSQL allows for multiple versions to coexist on the same system. Before we can upgrade the existing clusters we need ton install a new version of PostgreSQL. Read through the offical PostgreSQL documentation here if the postgresql-13 version is not available.

# Always start with updating your system.
sudo apt update && sudo apt upgrade

# Install PostgreSQL server and client version 13
sudo apt install postgresql-13 postgresql-client-13

Running the same dpkg command we used earlier it will now show both PostgreSQL version 12 and 13.

sudo dpkg -l | grep postgresql

postgresql                13+225ubuntu1          all    object-relational SQL database (supported version)
postgresql-12             12.6-0ubuntu0.20.10.1  amd64  object-relational SQL database, version 12 server
postgresql-13             13.2-1                 amd64  The World`s Most Advanced Open Source Relational Database
postgresql-client         13+225ubuntu1          all    front-end programs for PostgreSQL (supported version)
postgresql-client-12      12.6-0ubuntu0.20.10.1  amd64  front-end programs for PostgreSQL 12
postgresql-client-13      13.2-1                 amd64  front-end programs for PostgreSQL 13
postgresql-client-common  225ubuntu1             all    manager for multiple PostgreSQL client versions
postgresql-common         225ubuntu1             all    PostgreSQL database-cluster manager

Upgrading PostgreSQL

Now that have both version 12 and 13 installed we can begin with updating the existing PostgreSQL cluster.

First we check the status of the clusters with the pg_clusters command. This should show both clusters online

sudo pg_lsclusters

Ver  Cluster  Port  Status  Owner     Data directory               Log file
12   main     5432  online  postgres  /var/lib/postgresql/12/main  /var/log/postgresql/postgresql-12-main.log
13   main     5433  online  postgres  /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log

Out of the box the PostgreSQL version 13 cluster “main” is created at installation. When trying to upgrade our “main” version 12 it will clash with the existing version 13 “main”. We will need to drop this cluster with the pg_dropcluster command.

sudo pg_dropcluster 13 main --stop

Now we can upgrade the “main” cluster from version 12 to version 13 with the pg_upgradecluster command.

sudo pg_upgradecluster 12 main

With the pg_lsclusters command we can verify the status of the clusters. Version 12 should be down now.

sudo pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5433 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

When everything is running fine we can delete the version 12 “main” cluster and remove the PostgreSQL version 12 packages

sudo pg_dropcluster 12 main

sudo apt-get purge postgresql-12 postgresql-client-12

Conclusion

By following these steps you should be able to upgrade your existing PostgreSQL version 12 cluster to version 13. These steps for will also work for example upgrading version 13 to version 14. If you have any questions post them in the comments!

Leave a Reply

Your email address will not be published.