Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Upgrade Postgres 9.3 to 9.4 on Ubuntu
# 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 - | sudo apt-key add -
sudo sh -c 'echo "deb 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/": 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:
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
>\c your_database
Copy link

ahmedhashim commented Aug 12, 2015

Why apt-get upgrade? this is unnecessary step

Copy link

KDCinfo commented Sep 22, 2015

Which version of Ubuntu is this for?

Copy link

KDCinfo commented Sep 23, 2015

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.

Copy link

mikeghen commented Jan 25, 2016


Copy link

hdayacr commented Apr 14, 2016

👍 perfect with ubuntu 14.04

Copy link

patsrik commented May 26, 2016

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

Copy link

AKovtunov commented May 30, 2016

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

Copy link

zlwaterfield commented Jul 30, 2016


Copy link

codyjk commented Aug 14, 2016


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment