How to upgrade PostgreSQL from 11 to 12
Juraj Kostolanský • 03 October 2019
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!
