Upgrading PostgreSQL 9.1 to 9.3 in Ubuntu

I went with the default PostgreSQL from the Linux Mint repositories when installing Petra. That was PostgreSQL 9.1. It's been perfectly fine, but earlier today while writing a PostgreSQL function I realized it was stupid to continue adhering to the pre-9.2 requirement of declaring local variables for function parameters. That was all the incentive I needed to do the upgrade, which I applied to two Mint PCs and an Ubuntu PC with everything going without a hitch.

We'll begin with the PostgreSQL Wiki and download the 9.3 Client and Server packages.

First create an /etc/apt/sources.list.d/pgdg.list file with the following contents. Notice “saucy” for Ubuntu 13.10/Linux Mint 16. You may need to change it depending on your OS.

deb http://apt.postgresql.org/pub/repos/apt/ saucy-pgdg main

Now the usual package install prep work, taken from another PostgreSQL Wiki Page.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get upgrade

We're developers so the following packages will get us most of what we'll need.

sudo apt-get install postgresql-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 pgadmin3

To confirm our install, we'll enter “sudo pg_lsclusters” and see our two versions of PostgreSQL running.

Now to an excellent guide from yznotes.com which we'll use to upgrade our PostgreSQL 9.1 environment to 9.3, complete with database upgrades, users and roles, all of it.

We stop the PostgreSQL Service

$ sudo service postgresql stop

and delete the default 9.3 cluster created by the 9.3 install.

$ sudo pg_dropcluster --stop 9.3 main

Next we create a new 9.3 cluster from the existing 9.1 cluster.

$ sudo pg_upgradecluster 9.1 main

The upgrade command will display what is being updated and look something like this. Don't worry if you get a fatal password authentication message for postgres like the one below. It don't mean nothin'.

Confirm that the new cluster loads and we're running PostgreSQL 9.3.

$ sudo service postgresql start 9.3

Then after 2 or 3 minutes when you're comfortable everything is Rock Steady, go ahead and delete the 9.1 Cluster.

$ pg_dropcluster --stop 9.1 main

You be upgraded! I don't know about you, but no more declaring function parameters as local variables for me!

Posted April 14, 2014 04:47 PM EDT

More Like This Post