Upgrading PostgreSQL from 9.6 to 10.0 on Ubuntu 18.04

How-To guide to upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu after upgrade it to 18.04 version.

TL;DR

After upgrade to Ubuntu 18.04:

$ sudo pg_dropcluster 10.0 main --stop
$ sudo pg_upgradecluster 9.6 main
$ sudo pg_dropcluster 9.6 main

Upgrade PostgreSQL

During Ubuntu updgrade to 18.04 you receive this message "Configuring postgresql-common":

Obsolete major version 9.6

The PostgreSQL version 9.6 is obsolete, but the server or client packages are still installed.
Please install the latest packages (postgresql-10 and postgresql-client-10) and upgrade the existing clusters with pg_upgradecluster (see manpage).

Please be aware that the installation of postgresql-10 will automatically create a default cluster 10.0/main.
If you want to upgrade the 9.6/main cluster, you need to remove the already existing 10 cluster (pg_dropcluster --stop 10 main, see manpage for details).

The old server and client packages are no longer supported.
After the existing clusters are upgraded, the postgresql-9.6 and postgresql-client-9.6 packages should be removed.

Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.

Use dpkg -l | grep postgresql to check which versions of postgres are installed:

ii  postgresql                                 10+190                                      all          object-relational SQL database (supported version)
ii  postgresql-10                              10.3-1                                      amd64        object-relational SQL database, version 10 server
ii  postgresql-10-postgis-2.4                  2.4.3+dfsg-4                                amd64        Geographic objects support for PostgreSQL 10
ii  postgresql-10-postgis-2.4-scripts          2.4.3+dfsg-4                                all          Geographic objects support for PostgreSQL 10 -- SQL scripts
ii  postgresql-9.6                             9.6.8-0ubuntu0.17.10                        amd64        object-relational SQL database, version 9.6 server
ii  postgresql-9.6-postgis-2.3                 2.3.3+dfsg-1                                amd64        Geographic objects support for PostgreSQL 9.6
ii  postgresql-client                          10+190                                      all          front-end programs for PostgreSQL (supported version)
ii  postgresql-client-10                       10.3-1                                      amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-9.6                      9.6.8-0ubuntu0.17.10                        amd64        front-end programs for PostgreSQL 9.6
ii  postgresql-client-common                   190                                         all          manager for multiple PostgreSQL client versions
ii  postgresql-common                          190                                         all          PostgreSQL database-cluster manager
ii  postgresql-contrib                         10+190                                      all          additional facilities for PostgreSQL (supported version)
ii  postgresql-contrib-9.6                     9.6.8-0ubuntu0.17.10                        amd64        additional facilities for PostgreSQL
ii  postgresql-plpython3-9.6                   9.6.8-0ubuntu0.17.10                        amd64        PL/Python 3 procedural language for PostgreSQL 9.6

Run pg_lsclusters, your 9.6 and 10 main clusters should be "online".

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 down   postgres /var/lib/postgresql/9.6/main pg_log/postgresql-%Y-%m-%d.log
10  main    5433 down   postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

There already is a cluster "main" for 10 (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 9.6/main when 10/main also exists. The recommended procedure is to remove the 10 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 10 cluster and drop it.

$ sudo pg_dropcluster 10 main --stop

Upgrade the 9.6 cluster to the latest version.

$ sudo pg_upgradecluster 9.6 main

Your 9.6 cluster should now be "down" and you can verifity running pg_lsclusters

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main ...
10  main    5432 online postgres /var/lib/postgresql/10/main  ...

Check that the upgraded cluster works, then remove the 9.6 cluster.

$ sudo pg_dropcluster 9.6 main

After all your data check you can remove your old packages.

$ sudo apt purge postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6

Disclaimer of Warranty.

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.

Reference

Originially published on www.paulox.net