PostgreSQL Upgrade from Version 12 to Version 17

Overview

This guide explains how to upgrade PostgreSQL from Version 12 to Version 17 on Ubuntu 20.04 LTS. We'll cover the complete upgrade process, including preparation, migration, verification, and post-upgrade cleanup.

Prerequisites

• Server running PostgreSQL 12
• Ubuntu 20.04 LTS
• Shell access with sudo privileges
• Recent database backup

Step 1: Prepare for PostgreSQL 17 Installation

Add PostgreSQL Repository Key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Add PostgreSQL Repository

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

Update Packages

sudo apt update

Install PostgreSQL 17

sudo apt install postgresql-17 postgresql-client-17 -y

Verify Installation

dpkg -l | grep postgresql

pg_lsclusters

Step 2: Upgrade PostgreSQL

Stop PostgreSQL Service

sudo service postgresql stop

Remove Empty PostgreSQL 17 Cluster

sudo service postgresql stop

Upgrade Cluster

sudo pg_upgradecluster 12 main

Step 3: Start PostgreSQL

sudo service postgresql start

pg_lsclusters

Step 4: Verify Upgrade

sudo -u postgres psql

SELECT version();

\q

Step 5: Remove Old PostgreSQL 12 Cluster (Optional)

sudo pg_dropcluster 12 main

Post-Upgrade Best Practices

sudo -u postgres vacuumdb --all --analyze-in-stages

sudo tail -f /var/log/postgresql/postgresql-17-main.log