Upgrade PostgreSQL Database

  • Updated on Apr 17, 2025
  • Published on Oct 24, 2024

Upgrade to PostgreSQL 14 for Using New iRM Versions

To use newer versions of INSYS icom Router Management (iRM), the PostgreSQL database must be upgraded from version 12 to version 14.


This guide explains how to install PostgreSQL 12 and 14 in parallel and subsequently migrate the database.

Backup the Database

sudo -u postgres pg_dumpall > /tmp/backup.sql

Upgrade to PostgreSQL 14 on Ubuntu 20.04

Since PostgreSQL 14 is not natively available in Ubuntu 20.04, the PostgreSQL repository must be added manually.

Create Repository Configuration

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

Import the Repository Signing Key

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

Update Package Lists

sudo apt-get update

Install PostgreSQL 14

Note

If you want to install a specific version, use postgresql-XX instead of postgresql.

sudo apt-get -y install postgresql-14

Upgrade the Database from PostgreSQL 12 to 14

Stop the Old Database Service

sudo systemctl stop postgresql.service

Change to Working Directory

cd /tmp

Migrate Configuration and Data

sudo -u postgres /usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Reconfigure Server Ports

Adjust the ports in the configuration files as follows:

sudo vim /etc/postgresql/14/main/postgresql.conf  # Set port to 5432
sudo vim /etc/postgresql/12/main/postgresql.conf  # Set port to 5433

Start the New PostgreSQL Server and Verify the Version

sudo systemctl start postgresql.service
sudo -u postgres psql -c "SELECT version();"

Reset the Password for the Custom Database User

sudo -u postgres psql

In the PostgreSQL prompt:

\password u4insysicomroutermgmt

(Optional) Remove Old PostgreSQL 12 Installation

Remove Old Packages

sudo apt remove postgresql-12 postgresql-contrib-12

Delete Old Data and Configuration Files

sudo rm -rf /etc/postgresql/12
sudo -u postgres /tmp/delete_old_cluster.sh