I always forget how easy it is to upgrade postgresql on Ubuntu (from 9.1 to 9.3 this time). It seems like a pain to have to manually upgrade the cluster, but when it comes down to it, it's self-documenting and quick.

My shell session basically went like this:

$ sudo apt-get install postgresql-9.3
The following extra packages will be installed:
$ sudo /etc/init.d/postgresql stop
 * Stopping PostgreSQL 9.1 database server  [ OK ] 
 * Stopping PostgreSQL 9.3 database server  [ OK ]

$ sudo su -l postgres

$ pg_upgradecluster 
Usage: /usr/bin/pg_upgradecluster [OPTIONS] <old version> <cluster name> [<new data directory>]

$ pg_upgradecluster 9.1 main
Error: target cluster 9.3/main already exists

$ pg_dropcluster 9.3 main
$ pg_upgradecluster 9.1 main
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Upgrading database some_db...

Ok, it wasn't that quick. The analyzing and upgrading took several minutes per database, but it was easy.

Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

  pg_dropcluster 9.1 main

$ logout
$ sudo /etc/init.d/postgresql start 9.3
 * Starting PostgreSQL 9.3 database server  [ OK ]

$ sudo netstat -tulpen | grep postg
tcp        0      0*               LISTEN      116        138692      10716/postgres  
$ test_that_it_works

$ sudo su -l postgres
$ pg_dropcluster 9.1 main
$ logout

$ find /etc/postgresql/9.1/
$ sudo apt-get remove --purge postgresql-9.1 postgresql-client-9.1

upgrade postgres ubuntu