Upgrade Postgres 9.3 to 9.4 on Ubuntu

gistfile1.sh

# Be sure to save your config files. Optional but I do:
sudo cp /etc/postgresql/9.3/main/postgresql.conf ~
sudo cp /etc/postgresql/9.3/main/pg_hba.conf ~

# Package repo (for apt-get)
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

# Also probably optional but I like to update sources and upgrade
sudo apt-get update
sudo apt-get upgrade

# Install postgres 9.4
sudo apt-get install postgresql-9.4 postgresql-server-dev-9.4 postgresql-contrib-9.4

# Get latest postgis for 9.4 (optional)
sudo apt-get install postgresql-9.4-postgis

# dump your data
sudo su postgres
cd /mnt/dumps
/usr/lib/postgresql/9.4/bin/pg_dumpall > pre_upgrade_from_9.3_to_9.4.dump
# I got this error:
# pg_dump: [archiver (db)] query failed: ERROR:  could not load library "/usr/lib/postgresql/9.3/lib/postgis-2.1.so": liblwgeom-2.1.2.so: cannot open shared object file: No such file or directory
# pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
#     ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '17868'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attrelid, a.attnum
# pg_dumpall: pg_dump failed on database "sidewalk", exiting

# Took advice here: http://gis.stackexchange.com/questions/97871/postgis-2-1-error-after-update
sudo apt-get install liblwgeom-2.1.3

# Then repeated
sudo su postgres
cd /mnt/dumps
/usr/lib/postgresql/9.4/bin/pg_dumpall > pre_upgrade_from_9.3_to_9.4.dump

# Make a data dir for Postgres 9.4 (on your EBS if this is EC2)
sudo mkdir -p /data/postgres/9.4/main
sudo chown -R postgres:postgres /data/postgres
# Change the 9.4 conf file's data dir to point to /data/postgres/9.4/main
sudo nano /etc/postgresql/9.4/main/postgresql.conf

# Install 9.4 cluster
sudo /etc/init.d/postgresql stop
sudo pg_dropcluster 9.4 main
sudo pg_createcluster -d /data/postgres/9.4/main 9.4 main

# start 9.4 explicitly
sudo /etc/init.d/postgresql start 9.4

# Restore: Make sure to use the 9.4 version of psql
psql -d postgres -p 5433 -f /mnt/dumps/pre_upgrade_from_9.3_to_9.4.dump
# Or nohup version:
sudo -u postgres nohup psql -d postgres -p 5433 -f /mnt/dumps/pre_upgrade_from_9.3_to_9.4.dump > upgrade-restore-nohup.out 2>&1 &5

# Change port back to 5432 (optional) and the confs back to what they were! (reference previously copied files)
sudo nano /etc/postgresql/9.4/main/postgresql.conf
sudo nano /etc/postgresql/9.4/main/pg_hba.conf

sudo service postgresql restart 9.4

# Verify your data was properly imported

# Drop old cluster
sudo pg_dropcluster --stop 9.3 main

# Analyze
sudo service postgresql start
psql
>\c your_database
> ANALYZE;

Why apt-get upgrade? this is unnecessary step

Which version of Ubuntu is this for?

Never mind. I see by your reference to "precise-pgdg" that this refers to 12.04. I just changed 'precise' to 'trusty' for 14.04. I used your steps in conjunction with another step-by-step upgrade (which used 'utopic'), and between the two, got it to work without too many issues.

Thank you very much for posting this -- It was very helpful.

👍

👍 perfect with ubuntu 14.04

all fine, but will this restore data in 9.3 ? What should i do to get data in 9.3 to 9.4

I would recommend to make sudo su postgres on line 50
Without it I was receiving errors

👍

👍