Howto guide for upgrading PostgreSQL from version 11 to 12 on Ubuntu, after its upgrade from version 19.10 to 20.04.
This article is aimed at those like me who use Ubuntu and PostgreSQL to develop locally on their computer and after the last update to Ubuntu 20.04 they have two versions of PostgreSQL installed.
After upgrade to Ubuntu 20.04:
$ sudo pg_dropcluster 12 main --stop $ sudo pg_upgradecluster 11 main $ sudo pg_dropcluster 11 main
During Ubuntu updgrade to 20.04 you receive this message "Configuring postgresql-common":
Obsolete major version 11
The PostgreSQL version 11 is obsolete, but the server or client packages are still installed.
Please install the latest packages (postgresql-12 and postgresql-client-12) and upgrade the existing clusters with pg_upgradecluster (see manpage).
Please be aware that the installation of postgresql-12 will automatically create a default cluster 12/main.
If you want to upgrade the 11/main cluster, you need to remove the already existing 12 cluster (pg_dropcluster --stop 12 main, see manpage for details).
The old server and client packages are no longer supported.
After the existing clusters are upgraded, the postgresql-11 and postgresql-client-11 packages should be removed.
Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.
dpkg -l | grep postgresql to check which versions of postgres are installed:
ii postgresql 12+214 all object-relational SQL database (supported version) ii postgresql-11 11.7-0ubuntu0.19.10.1 amd64 object-relational SQL database, version 11 server ii postgresql-12 12.2-4 amd64 object-relational SQL database, version 12 server ii postgresql-client 12+214 all front-end programs for PostgreSQL (supported version) ii postgresql-client-11 11.7-0ubuntu0.19.10.1 amd64 front-end programs for PostgreSQL 11 ii postgresql-client-12 12.2-4 amd64 front-end programs for PostgreSQL 12 ii postgresql-client-common 214 all manager for multiple PostgreSQL client versions ii postgresql-common 214 all PostgreSQL database-cluster manager
pg_lsclusters, your 11 and 12 main clusters should be "online".
Ver Cluster Port Status Owner Data directory Log file 11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log 12 main 5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
There already is a cluster "main" for 12 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 11/main when 12/main also exists. The recommended procedure is to remove the 12 cluster with
pg_dropcluster and then upgrade with
Stop the 12 cluster and drop it.
$ sudo pg_dropcluster 12 main --stop
Upgrade the 11 cluster to the latest version.
$ sudo pg_upgradecluster 11 main
Your 11 cluster should now be "down" and you can verifity running
Ver Cluster Port Status Owner Data directory Log file 11 main 5433 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log 12 main 5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
Check that the upgraded cluster works, then remove the 11 cluster.
$ sudo pg_dropcluster 11 main
After all your data check you can remove your old packages.
$ sudo apt-get purge postgresql-11 postgresql-client-11
THERE IS NO WARRANTY FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.
Originially published on www.paulox.net