Upgrade to the next major release

First it is important to make a backup. I have a test environment that I shut down and back up with Acronis. I usually use the time to update the system as well.

sudo apt update -y


sudo apt upgrade -y

After that I shut down the VM.

And the backup has been made.

I connect to the Postgres server via ssh.

In advance I collected a few directories.

The binary directory is located at /usr/lib/postgresql/11/bin.

The data directory is located at /var/lib/postgresql/11/main.

The config directory is located at /etc/postgresql/11/main.

The log file is located at /var/log/postgresql/postgresql-11-main.log.

Small control, we switch to the user context postgres

sudo su - postgres

and then

psql -U postgres

Now we display the data directory.

show data_directory;

Output is

(1 row)

and exit the postgres context with command exit.

Upgrade to Version 14

sudo apt install gnupg gnupg2 gnupg1 -y
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update -y
sudo apt-get install postgresql-14 -y

Check if there are any differences in the config files.

diff /etc/postgresql/11/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf
sudo diff /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf

Shutdown PostgreSQL server

sudo systemctl stop postgresql

Installation is already done and now the migration starts.

sudo su - postgres
cd /tmp
mkdir postgres
cd /tmp/postgres
/usr/lib/postgresql/14/bin/pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/14/bin -d /etc/postgresql/11/main -D /etc/postgresql/14/main --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' --new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Swap the ports for the old and new PostgreSQL versions.

sudo nano /etc/postgresql/11/main/postgresql.conf

…and change “port = 5432” to “port = 5433”

sudo nano /etc/postgresql/14/main/postgresql.conf

# …and change “port = 5433” to “port = 5432”
# listen_addresses = ‘localhost’ => listen_addresses = ‘*’ remove comment!!!

sudo nano /etc/postgresql/14/main/pg_hba.conf

# change =>

Start server

sudo systemctl start postgresql

Addition to Postgres 14

Password has changed and therefore each user must reset the password.

sudo su - postgres

Show tables with users


Change password of postgres

\password postgres

Unfortunately, this must be repeated for all users.


Clean up

apt list --installed | grep postgresql
sudo apt-get remove postgresql-11 postgresql-server-dev-11 -y
sudo apt-get remove postgresql-12 postgresql-server-dev-12 -y
sudo apt-get remove postgresql-13 postgresql-server-dev-13 -y
sudo rm -rf /etc/postgresql/11/
sudo rm -rf /etc/postgresql/12/
sudo rm -rf /etc/postgresql/13/
sudo su - postgres
cd /tmp/postgres