How to upgrade PostgreSQL to the latest version on Ubuntu

Upgrading PostgreSQL on Ubuntu can be a seamless process. This guide will take you through the various steps required for a successful upgrade, ensuring your databases are migrated safely.

Introduction

PostgreSQL, commonly known as Postgres, is an open-source relational database management system. Upgrading your Postgres server to the latest version brings performance improvements, security patches, and new features. However, database upgrades should be undertaken with care to avoid data loss. This guide will provide a detailed walkthrough for upgrading PostgreSQL on Ubuntu systems, using various methods suitable for different scenarios.

Prerequisites:

• Access to an Ubuntu server
• An existing PostgreSQL installation
• Adequate system backups in place
• Sudo or root privileges

Understanding PostgreSQL Versioning

Before starting the upgrade, you should understand the PostgreSQL versioning system. Major versions are indicated by the first two numbers (e.g., 13.2 to 14.0), while minor releases are indicated by the last number (e.g., 14.1 to 14.2). Upgrading to a new major version involves more steps than a minor version bump.

Upgrading to a Minor Version

sudo apt update
sudo apt upgrade postgresql

This procedure updates PostgreSQL to the latest minor version without needing to migrate data between servers. Once the process is complete, simply restart the service.

sudo systemctl restart postgresql

Upgrading to a Major Version

Major version upgrades are more involved as they require a database migration. Ubuntu’s ‘pg_upgrade’ utility can assist with this process.

Step 1: Install the New Version

sudo apt update
sudo apt install postgresql-XX

Replace ‘XX’ with the number of the new version of PostgreSQL you wish to install.

Step 2: Prepare for Migration

Ensure that both the old and new versions are installed side by side.

sudo systemctl stop postgresql
sudo pg_dropcluster XX main --stop

Here, ‘XX’ should match the new PostgreSQL version number. This command does not delete any data, but it avoids confusion with multiple running clusters.

Step 3: Upgrade the Database

/usr/lib/postgresql/XX/bin/pg_upgrade -b /usr/lib/postgresql/YY/bin/ -B /usr/lib/postgresql/XX/bin/ -d /var/lib/postgresql/YY/main/ -D /var/lib/postgresql/XX/main/ -O "-c config_file=/etc/postgresql/XX/main/postgresql.conf" -o "-c config_file=/etc/postgresql/YY/main/postgresql.conf"

Replace ‘XX’ with the new version and ‘YY’ with the old version. After successful execution, the data will be migrated to the new version.

Step 4: Tidy Up

Remove the old version’s cluster and restart the new PostgreSQL service:

sudo pg_dropcluster YY main
sudo systemctl start postgresql

Step 5: Test and Verify

As a safety measure, it’s crucial to verify that your applications still operate correctly with the upgraded database server. Check log files for any unusual messages:

tail -f /var/log/postgresql/postgresql-XX-main.log

Custom and Advanced Upgrades

If your upgrade path involves custom configurations or large databases, you might need more sophisticated strategies such as using ‘pg_dumpall’ for backup and restore processes, or setting up replication with logical or streaming replication to minimize downtime.

sudo pg_dumpall > all_databases.sql
# After installation of the new version, restore
psql -f all_databases.sql postgres

Conclusion

Upgrading PostgreSQL is a process that can be tailored to fit any environment, from simple minor updates to complex major upgrades on production servers. With the right preparation and by following these steps, you can ensure a smooth transition to the latest PostgreSQL version on your Ubuntu server, reaping the benefits of the latest features and improvements.