Upgrade PostgreSQL with pg_upgradecluster

Another post about upgrading PostgreSQL? Really? Yes, I had to upgrade from 9.3 to 9.5 on Ubuntu 16.04 after upgrading from 14.04 today.  And I couldn’t find a post that completely described my situation. So I’m writing my own.

 

 

The first thing you need to know is how to see which version of Postgres is currently running:

Yes, that’s 9.5 running. On Ubuntu 2 versions are installed at the same time:

To start or stop a specific version you can use:

After upgrading from Ubuntu 14.04 to 16.04 a version 9.5 was installed and a new cluster was created by the installer. You have to drop it if you want to upgrade your 9.3 cluster to 9.5. And you have to stop version 9.5 if you want to drop the cluster.

 

I got this cryptic error message, but it worked anyway. Just run the command again if you are not sure.

Well with a clean 9.5 it’s time to upgrade 9.3. The upgrade will not work properly if you have people using the database and you will get something like this:

So stop the application or nginx or wherever your users come from:

and try again:

Ouch! More scary and cryptic error messages. The solution to the locale warnings is to set them again before running pg_upgradecluster:

Use the version specific start/stop commands to see if both versions are working with the same set of data. If everything works well in the new version you can finally drop the 9.3 cluster.

I got no output but the website no longer works with 9.3 so it’s gone. Time to remove the packages as well:

Finally don’t forget to reboot to see if everything starts after a restart.