DEV Community

Juraj Kostolanský
Juraj Kostolanský

Posted on • Originally published at kostolansky.sk on

How to upgrade PostgreSQL from 11 to 12

#postgres #linux #tutorial

The new PostgreSQL 12 has just been released. There are multiple ways to upgrade from the old version 11, 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 12.

sudo apt-get update sudo apt-get install postgresql-12 postgresql-server-dev-12
Enter fullscreen mode Exit fullscreen mode

Check if there are any differences in the config files.

diff /etc/postgresql/11/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf diff /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Stop the PostgreSQL service.

sudo systemctl stop postgresql.service
Enter fullscreen mode Exit fullscreen mode

Log in as the postgres user.

sudo su postgres
Enter fullscreen mode Exit fullscreen mode

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

/usr/lib/postgresql/12/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/11/main \ --new-datadir=/var/lib/postgresql/12/main \ --old-bindir=/usr/lib/postgresql/11/bin \ --new-bindir=/usr/lib/postgresql/12/bin \ --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \ --check
Enter fullscreen mode Exit fullscreen mode

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

/usr/lib/postgresql/12/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/11/main \ --new-datadir=/var/lib/postgresql/12/main \ --old-bindir=/usr/lib/postgresql/11/bin \ --new-bindir=/usr/lib/postgresql/12/bin \ --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'
Enter fullscreen mode Exit fullscreen mode

Go back to the regular user.

exit
Enter fullscreen mode Exit fullscreen mode

Swap the ports for the old and new PostgreSQL versions.

sudo vim /etc/postgresql/12/main/postgresql.conf # ...and change "port = 5433" to "port = 5432" sudo vim /etc/postgresql/11/main/postgresql.conf # ...and change "port = 5432" to "port = 5433"
Enter fullscreen mode Exit fullscreen mode

Start the PostgreSQL service.

sudo systemctl start postgresql.service
Enter fullscreen mode Exit fullscreen mode

Log in as the postgres user again.

sudo su postgres
Enter fullscreen mode Exit fullscreen mode

Check the new PostgreSQL version.

psql -c "SELECT version();"
Enter fullscreen mode Exit fullscreen mode

Run the generated analyze_new_cluster script.

./analyze_new_cluster.sh
Enter fullscreen mode Exit fullscreen mode

Back to normal user.

exit
Enter fullscreen mode Exit fullscreen mode

Check which old PostgreSQL packages are installed.

apt list --installed | grep postgresql
Enter fullscreen mode Exit fullscreen mode

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

sudo apt-get remove postgresql-11 postgresql-server-dev-11
Enter fullscreen mode Exit fullscreen mode

Remove the old configuration.

sudo rm -rf /etc/postgresql/11/
Enter fullscreen mode Exit fullscreen mode

Log in as the postgres user once more.

sudo su postgres
Enter fullscreen mode Exit fullscreen mode

Finally, drop the old cluster data.

./delete_old_cluster.sh
Enter fullscreen mode Exit fullscreen mode

Done!

Original article: How to upgrade PostgreSQL from 11 to 12

Top comments (5)

Collapse
 
raidus profile image
Willy Raider • • Edited

Thanks for the clear instructions! It's actually pretty straight forward.

Except in my case I had to

cd /tmp

before running

sudo su postgres

Just in case others run into the same issue :)

Collapse
 
abysso2 profile image
Andreas Mueller • • Edited

su - postgres also works :-)

Collapse
 
abysso2 profile image
Andreas Mueller • • Edited

What a great walkthrough - thank you very much! Works like a charm!

Collapse
 
joshukraine profile image
Joshua Steele •

Awesome walk-through, super helpful. Just did this on a production server — worked like a charm! Thanks! 😃 🎉

Some comments may only be visible to logged-in visitors. Sign in to view all comments.