How to Upgrade PostgreSQL from 12 to 14 on Ubuntu

PostgreSQL is one of the most popular relational database management systems, known for its robustness, flexibility, and performance. As with any major software, it’s crucial to keep your PostgreSQL version up-to-date to take advantage of new features, optimizations, and security improvements. With the release of PostgreSQL 14, users can benefit from significant performance improvements, better query optimizations, and enhanced security features. In this comprehensive guide, we’ll walk you through the process of how to upgrade PostgreSQL 12 to 14 on an Ubuntu server. Whether you’re a system administrator or a developer, this tutorial will help ensure that your upgrade is smooth and seamless. So, let’s dive in!

Prerequisites

Before we begin, ensure that you meet the following prerequisites:

You have sudo or root access to the server.

PostgreSQL 12 is already installed and in use.

You have a stable backup of your databases (always important before performing any major update).

Backup Your Database

The most critical step before any upgrade is to create a full backup of your PostgreSQL cluster. These backups will ensure you can restore your data if anything goes wrong during the upgrade process.

To create a backup of all your databases, run:

sudo -u postgres pg_dumpall > ~/pg_backup.sql

This command will dump all databases, roles, and configurations into a single SQL file. Store it in a safe location as a backup.

Update System Packages

It’s always a good practice to update your system before proceeding with any new installations. Ensure that all existing packages are up-to-date:

sudo apt update && sudo apt upgrade -y

Add PostgreSQL APT Repository

To install PostgreSQL 14, we need to add the official PostgreSQL APT repository to your system’s sources. This repository provides the latest stable versions of PostgreSQL.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -

sudo apt update

The above procedure will add the PostgreSQL repository to your list of package sources and update your package index.

Install PostgreSQL 14

With the repository added, we can now install PostgreSQL 14 alongside your existing PostgreSQL 12 installation.

Run the following command:

sudo apt install postgresql-14 postgresql-client-14-y

This will install PostgreSQL 14 while keeping the existing PostgreSQL 12 intact. You can now safely upgrade without worrying about losing any data.

Check Existing Clusters

Before upgrading, let’s check the currently active PostgreSQL clusters:

pg_lsclusters

This will show you a list of all PostgreSQL clusters running on your system. You should see both version 12 and version 14 listed.

Upgrade Using pg_upgrade

If you’re wondering how to upgrade PostgreSQL 12 to 14, PostgreSQL offers a tool called pg_upgradecluster that makes the process much easier. It enables you to upgrade your data from version 12 to 14 safely and efficiently, without any data loss.

Run the following command to begin the upgrade:

sudo pg_upgradecluster 12 main

Once the upgrade is complete, verify it by checking your clusters again:

pg_lsclusters

You should now see that the PostgreSQL 14 cluster is running and the PostgreSQL 12 cluster is still listed as well.

Clean Up Old Cluster

After confirming that everything is working correctly (and after testing your application and queries), you can remove the old PostgreSQL 12 cluster:

sudo pg_dropcluster 12 main

This will safely remove the old PostgreSQL 12 data cluster from your server, leaving only PostgreSQL 14.

Verify Upgrade

It’s essential to verify that the upgrade was successful and that everything is functioning correctly. Log into the new PostgreSQL 14 instance:

sudo -u postgres psql

Once inside the PostgreSQL prompt, check the PostgreSQL version:

SELECT version();

You should see output showing PostgreSQL 14.x.

If you’re wondering how to upgrade PostgreSQL 12 to 14, the process is relatively straightforward. It involves key steps such as backing up your data, updating your system, and installing the new version through the PostgreSQL APT repository. To ensure a smooth transition, it’s important to upgrade dependencies, adjust configuration settings, and verify compatibility with your existing applications. By carefully following these steps, you can take full advantage of the improved performance, advanced security features, and new functionalities that PostgreSQL 14 offers. Additionally, testing the upgrade in a staging environment is essential to minimize any potential disruption to your production systems.

If you need expert help on how to upgrade PostgreSQL 12 to 14 or face challenges during the process, our team is here to assist. We specialize in Linux server management services and can ensure a smooth, secure upgrade tailored to your needs. Contact us today for reliable support and hassle-free database management!