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!