DEV Community

Cover image for Upgrading PostgreSQL from 9.5 to 9.6 on Ubuntu 17.04
Paolo Melchiorre
Paolo Melchiorre

Posted on • Edited on • Originally published at paulox.net

Upgrading PostgreSQL from 9.5 to 9.6 on Ubuntu 17.04

Howto upgrade PostgreSQL from 9.5 to 9.6 after upgrade to Ubuntu 17.04

TL;DR

After upgrade to Ubuntu 17.04:

sudo pg_dropcluster 9.6 main --stop
sudo pg_upgradecluster 9.5 main
sudo pg_dropcluster 9.5 main
Enter fullscreen mode Exit fullscreen mode

Upgrade PostgreSQL

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

Obsolete major version 9.5

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

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

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

Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.
Enter fullscreen mode Exit fullscreen mode

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

ii  postgresql                 9.6+179                all     object-relational SQL database (supported version)
ii  postgresql-9.5             9.5.6-0ubuntu0.16.10   amd64   object-relational SQL database, version 9.5 server
ii  postgresql-9.6             9.6.2-1                amd64   object-relational SQL database, version 9.6 server
ii  postgresql-client          9.6+179                all     front-end programs for PostgreSQL (supported version)
ii  postgresql-client-9.5      9.5.6-0ubuntu0.16.10   amd64   front-end programs for PostgreSQL 9.5
ii  postgresql-client-9.6      9.6.2-1                amd64   front-end programs for PostgreSQL 9.6
ii  postgresql-client-common   179                    all     manager for multiple PostgreSQL client versions
ii  postgresql-common          179                    all     PostgreSQL database-cluster manager
ii  postgresql-contrib-9.5     9.5.6-0ubuntu0.16.10   amd64   additional facilities for PostgreSQL
ii  postgresql-contrib-9.6     9.6.2-1                amd64   additional facilities for PostgreSQL
Enter fullscreen mode Exit fullscreen mode

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

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Enter fullscreen mode Exit fullscreen mode

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

Stop the 9.6 cluster and drop it.

sudo pg_dropcluster 9.6 main --stop
Enter fullscreen mode Exit fullscreen mode

Upgrade the 9.5 cluster to the latest version.

sudo pg_upgradecluster 9.5 main
Enter fullscreen mode Exit fullscreen mode

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

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Enter fullscreen mode Exit fullscreen mode

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

sudo pg_dropcluster 9.5 main
Enter fullscreen mode Exit fullscreen mode

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

sudo apt-get purge postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5
Enter fullscreen mode Exit fullscreen mode

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 gist.github.com/pauloxnet and www.paulox.net

Top comments (6)

Collapse
 
navinks04 profile image
navinks04

Very Nice Article. I had previously gone through your article and upgraded the database from 9.5 to 10. But now I need to downgrade it to 9.5 or 9.6, is it possible to do?
If yes then can you please explain the steps.
Thanks

Collapse
 
mbroek2004 profile image
Martin van den Broek

Very helpful, thanks !

Collapse
 
pauloxnet profile image
Paolo Melchiorre

You are welcome!

Collapse
 
mohitchauhan8 profile image
Mohit Chauhan

i have no words for u bro. thank u thank u thank u thank u thank u ,thanks a lotttttttttttttttttttttttttt :)

Collapse
 
pauloxnet profile image
Paolo Melchiorre

You're welcome

Collapse
 
preston profile image
Preston Lee

Awesome. Thank you!