How to upgrade PostgreSQL from 13 to 14

Juraj Kostolanský October 11, 2021

If you are upgrading to PostgreSQL 15, or older version 13, 12 or 11, please use the new tutorial. There are some important changes in the process.


The new PostgreSQL 14 has been released. There are multiple ways to upgrade from the old version 13, and the easiest one is by using the pg_upgrade tool. Here is a quick tutorial for Ubuntu (or Debian) systems. And, please, do not forget to back up your data!

Update packages and install the new PostgreSQL 14.

sudo apt-get update
sudo apt-get install postgresql-14 postgresql-server-dev-14

Check if there are any differences in the config files.

diff /etc/postgresql/13/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf
diff /etc/postgresql/13/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf

Stop the PostgreSQL service.

sudo systemctl stop postgresql.service

Log in as the postgres user.

sudo su - postgres

Check clusters (notice the --check argument, this will not change any data).

/usr/lib/postgresql/14/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/13/main \
  --new-datadir=/var/lib/postgresql/14/main \
  --old-bindir=/usr/lib/postgresql/13/bin \
  --new-bindir=/usr/lib/postgresql/14/bin \
  --old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf' \
  --check

Migrate the data (without the --check argument).

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

Go back to the regular user.

exit

Swap the ports for the old and new PostgreSQL versions.

sudo vim /etc/postgresql/14/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"

sudo vim /etc/postgresql/13/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"

Start the PostgreSQL service.

sudo systemctl start postgresql.service

Log in as the postgres user again.

sudo su - postgres

Check the new PostgreSQL version.

psql -c "SELECT version();"

Run the recommended vacuumdb command:

/usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages

There is one important change in the default settings in PostgreSQL 14. Actually, you may have noticed it while checking the differences in the config files: password encryption. Previously it was MD5, now it is SHA256. So, if you have used the default configuration, your clients will not be able to connect to the new database. And since PostgreSQL does not know the original clear text passwords, you have to set them again for all your database users.

To do so, connect to the new database:

psql

You can list all the existing database users:

\du

To change (or set again) the user’s password, you can use the following command (repeat for each user):

\password user_name

Now you can exit the psql.

\q

And back to normal user.

exit

Check which old PostgreSQL packages are installed.

apt list --installed | grep postgresql

Remove the old PostgreSQL packages (from the listing above).

sudo apt-get remove postgresql-13 postgresql-server-dev-13

Remove the old configuration.

sudo rm -rf /etc/postgresql/13/

Log in as the postgres user once more.

sudo su - postgres

Finally, drop the old cluster data.

./delete_old_cluster.sh

Done!

Let's stay in touch

Do you like what you read? Subscribe to get my content on web development, programming, system administration, side projects and more. No spam, unsubscribe at any time.