Postgres 9.1 to 9.2 upgrade guide for Ubuntu 12.04

gistfile1.sh

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2 postgresql-server-dev-9.2 postgresql-contrib-9.2

sudo su -l postgres

# http://makandracards.com/makandra/18643-how-the-change-the-locale-of-a-postgresql-cluster
pg_lsclusters
pg_dropcluster --stop 9.2 main
pg_createcluster --start 9.2 main # --locale en_AU.UTF-8 <-- set this based on the current cluster's locale

# now edit the newly created cluster's config files so we can drop them in place of the existing
sudo vim /etc/postgresql/9.2/main/postgresql.conf # copy db config from old version, merging in updates for this version
sudo vim /etc/postgresql/9.2/main/pg_hba.conf # set permissions to access db

psql -p 5433 # port number from pg_lsclusters
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
# now exit psql - ctrl+d

# check that the upgrade will work, this works even if the clusters are running 
 PGPASSWORD=blah_blah_blah /usr/lib/postgresql/9.2/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.2/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.2/main/ -O "-c config_file=/etc/postgresql/9.2/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf" --check --link

service postgresql stop

# see http://blog.endpoint.com/2015/07/how-fast-is-pgupgrade-anyway.html for --link
 PGPASSWORD=blah_blah_blah /usr/lib/postgresql/9.2/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.2/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.2/main/ -O "-c config_file=/etc/postgresql/9.2/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf" --link

logout # logout postgresql back to previous user

sudo apt-get remove postgresql-9.1
sudo vim /etc/postgresql/9.2/main/postgresql.conf # change port to 5432
sudo service postgresql restart

sudo su -l postgres
./analyze_new_cluster.sh
logout

sudo ln -s /usr/lib/postgresql/9.2/bin/pg_dump /usr/bin/pg_dump --force
# do NOT delete the old cluster's data directory