DEV Community

loading...

How to upgrade PostgreSQL from 11 to 12

jkostolansky profile image Juraj Kostolanský Originally published at kostolansky.sk on ・2 min read

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

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

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/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

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'

Go back to the regular user.

exit

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"

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 generated analyze_new_cluster script.

./analyze_new_cluster.sh

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-11 postgresql-server-dev-11

Remove the old configuration.

sudo rm -rf /etc/postgresql/11/

Log in as the postgres user once more.

sudo su postgres

Finally, drop the old cluster data.

./delete_old_cluster.sh

Done!


Original article: How to upgrade PostgreSQL from 11 to 12

Discussion (5)

pic
Editor guide
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! 😃 🎉

Collapse
avtec profile image
av-tec

Thanks for the walk through with this.
I am having one issue with the check clusters step & hoping someone might be able to point me in the right direction.

When I try & run the check cluster step it fails and I get an error:
"Connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket"

Are there any changes or issues with the postgresql.conf or pg_hba.conf that may cause this?