Upgrade PostgreSQL 12 to version 14 on Ubuntu 20.04

We will describe the process of upgrading PostgreSQL 12, which is available in the official repository in Ubuntu 20.04, to PostgreSQL 14 in a simple and convenient way, while preserving all databases. PostgreSQL 14 is a more secure and productive version that supports parallel nested loops (nested loop joins) and multirange types. A number of significant optimizations and improvements have been made: more efficient parallel data selection, improved parallel query planning, optimized sorting, increased performance of ARRAY, JSON, aggregate functions, and much more.

Preparation for upgrade

All commands listed in this article are executed on the server under the root user.
Let's make sure that PostgreSQL 12 is running and working using the command:
pg_lsclustersThe command will display a list of PostgreSQL clusters (servers) installed on your device.
It is quite normal that you only have one so far, as in the example:

We can see version (12) and the cluster name, port, and its status - online. This means we can proceed with creating a complete database dump.

Creating a complete PostgreSQL dump

To create a complete dump of the databases, users, permissions, and schemas of the current PostgreSQL DBMS, we need to use the pg_dumpall utility on behalf of the postgres user from the user's home directory:
cd /var/lib/postgresql
sudo -u postgres pg_dumpall > all_databases.sqlCreating a full dump may take some time, depending on the size of your databases. As a result, you will receive an all_databases.sql file, which will insure you in case of problems.

You can restore this dump using the command while in the same directory:
sudo -u postgres psql -f all_databases.sql

• -u postgres - the user on whose behalf the command is executed;
• -f all_databases.sql - path to the dump file (in the example, the file all_databases.sql is located in the directory where the command was run).

Installing PostgreSQL 14

By default, Ubuntu 20.04 does not have a PostgreSQL repository. Let's add it.
First, install the necessary software:
apt install -y wget gnupg2 lsb-releaseAdd the PostgreSQL repository itself:
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | tee /etc/apt/sources.list.d/pgdg.listAdd the repository's GPG key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -We receive a successful response:

Let's start updating the package list and installing PostgreSQL 14:
apt update
apt install postgresql-14 postgresql-server-dev-14After installation, we can check the list of clusters in our system and see no changes there, however, this is normal.

Upgrading PostgreSQL 12 to PostgreSQL 14

Let's use the pg_upgradecluster utility, which fully automates the PostgreSQL upgrade process. This utility copies data from the old version of PostgreSQL, updates configurations, starts a new PostgreSQL 14 cluster on the old port, and stops the old one. As a result of her work, you should receive a new version of the DBMS that will completely replace the old one without any loss of data.
Let's stop PostgreSQL:
systemctl stop postgresqlLaunch the automatic update utility:
pg_upgradecluster 12 mainWe receive a long output informing us about the utility's operation and, at the end, information about the successful launch of the new PostgreSQL 14 cluster:

However, this is not always the case. Very often on Ubuntu you can get a perl error: warning: Setting locale failed. Even if the locales are already configured:

This is due to the variable environment LC_CTYPE. Specify the correct value and restart pg_upgradecluster:
export LC_CTYPE="en_US.UTF-8The update process has been successfully started:

Checking the list of PostgreSQL clusters in our system:
pg_lsclustersAnd we see that the old cluster of version 12 has the status down and a new port, and in its place a new one, version 14, works:

You can check the current version of PostgreSQL with the command:
sudo -u postgres psql -c "SELECT version();"Get full information about the DBMS version:

After we have verified the performance of the new version of PostgreSQL, you can delete the cluster version 12, as pg_upgradecluster previously suggested, if necessary:
pg_dropcluster 12 mainAs a result, the current version of PostgreSQL 14 remains on the server with all databases and users:

Upgrading PostgreSQL 12 to version 14 in Ubuntu 20.04 is a straightforward task thanks to the pg_upgradecluster automatic upgrade tool. By following this guide, you can update the DBMS yourself in just a couple of commands. If you encounter any difficulties updating PostgreSQL on our VPS and dedicated servers, you can always contact our technical support team via online chat or the ticket system.