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
and
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
data_directory ----------------------------- /var/lib/postgresql/11/main (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'
exit
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 127.0.0.1/32 => 0.0.0.0/0
Start server
sudo systemctl start postgresql
Addition to Postgres 14
Password has changed and therefore each user must reset the password.
sudo su - postgres
psql
Show tables with users
\du
Change password of postgres
\password postgres
Unfortunately, this must be repeated for all users.
\q
exit
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
./delete_old_cluster.sh
exit