Upgrade PostgreSQL from 9.1 to 9.3 on Ubuntu 12.04

postgresql_9.1_to_9.3_on_ubuntu_12.04

# Probably excessive, but it makes these instructions simpler
sudo -i

# Add postgresql repo and update apt listing
echo "deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main" > /etc/apt/sources.list.d/pgdg.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update

# For some reason this is necessary with PostgreSQL on Ubuntu 12.04
update-alternatives --remove postmaster.1.gz /usr/share/postgresql/9.1/man/man1/postmaster.1.gz
apt-get install postgresql-9.3 pgadmin3 postgresql-contrib-9.3 # contrib is needed for hstore

# Before stopping the clusters, check the LC Collation of your running 9.1 instance and export this while recreating 9.3 main
# More Infos: https://www.sysorchestra.com/2014/07/28/installchangecheck-locale-for-new-or-productive-postgresql-cluster/
su - postgres
psql -p 5432 -c "\l" # check the collation column and note that (for ex. de_DE.UTF8)
psql -p 5433 -c "\l" # If the collation is the same, continue. If not, recreate the 9.3 cluster with the following commands

# Recreate 9.3 cluster with collation from old 9.1 cluster (NOT NEEDED IF BOTH ALREADY HAVE THE SAME!)
pg_ctlcluster 9.3 main stop
pg_dropcluster 9.3 main
export LC_ALL="<old-clusters-collation>" # For ex. LC_ALL="de_DE.UTF8"
pg_createcluster 9.3 main

# Stop all running postgresql servers -- needed for migration of 9.1 data to 9.3 (pg_upgrade execution)
/etc/init.d/postgresql stop

# Must link conf file into data directory since it's expected there by pg_upgrade.
# Unfortunately, Ubuntu places it in /etc default (which complicates the upgrade)
ln -s /etc/postgresql/9.1/main/postgresql.conf /var/lib/postgresql/9.1/main/postgresql.conf
ln -s /etc/postgresql/9.3/main/postgresql.conf /var/lib/postgresql/9.3/main/postgresql.conf

# Run the pg_upgrade, but as the postgres user instead of root.
su postgres
cd ~postgres
/usr/lib/postgresql/9.3/bin/pg_upgrade -d /var/lib/postgresql/9.1/main -D /var/lib/postgresql/9.3/main -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -k -v

# Hopefully upgrade finished without error! In which case, we can start up PostgreSQL...
/etc/init.d/postgresql start

looks like missing "list" from the end of this line; correction below:
echo "deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Thank you for share. It solved my upgrade.

It solved my upgrade too. Thank you

Thanks

This is exactly what I need to do, but not getting very far with these instructions.

response to line 7 (apt-get update).....

N: Ignoring file 'pgdg.' in directory '/etc/apt/sources.list.d/' as it has an invalid filename extension
W: GPG error: http://cran.stat.ucla.edu precise/ Release: The following signatures were invalid: KEYEXPIRED 1445181253 KEYEXPIRED 1445181253 KEYEXPIRED 1445181253

response to line 11 (apt-get install postgresql-9.3 pgadmin3 postgresql-contrib-9.3)......

Reading package lists... Done
Building dependency tree
Reading state information... Done
pgadmin3 is already the newest version.
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
postgresql-contrib-9.1 : Depends: postgresql-9.1 (= 9.1.24-1.pgdg12.4+1) but 9.1.23-0ubuntu0.12.04 is to be installed
N: Ignoring file 'pgdg.' in directory '/etc/apt/sources.list.d/' as it has an invalid filename extension

response to line 17 (psql -p 5433 -c "\l")...

$ psql -p 5433 "\l"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5433"?

result...

postgresql is downloaded to:
/usr/share/postgresql/9.3
/usr/include/postgresql/9.3

any ideas?