Skip to content

Instantly share code, notes, and snippets.

@tamoyal tamoyal/gist:10303138
Last active Aug 29, 2015

What would you like to do?
Update Postgres 9.1 to 9.3 on Ubuntu
# Note this uses a dump and restore method. You can try using pg_upgrade instead
# Make a copy of postgresql.conf and hba.conf since you'll want to use those to edit the 9.3 conf later
$ sudo cp /etc/postgresql/9.1/main/postgresql.conf ~
$ sudo cp /etc/postgresql/9.1/main/pg_hba.conf ~
$ wget --quiet -O - | sudo apt-key add -
$ sudo sh -c 'echo "deb precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
$ sudo apt-get update
$ sudo apt-get upgrade
$ sudo apt-get install postgresql-9.3 pgadmin3 postgresql-server-dev-9.3 postgresql-contrib-9.3
# Make sure to dump using the 9.3 version of dump
$ sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_dumpall > /mnt/dumps/pre-upgrade.dump
# Get latest postgis for 9.3 (optional)
$ sudo apt-get install python-software-properties
$ sudo apt-add-repository ppa:ubuntugis/ppa
$ sudo apt-get update
$ sudo apt-get install postgresql-9.3-postgis
# Make a data dir for Postgres 9.3 (on your EBS if this is EC2)
$ sudo mkdir -p /data/postgres/9.3/main
# Change the 9.3 conf file's data dir to point to /data/postgres/9.3/main
$ sudo nano /etc/postgresql/9.3/main/postgresql.conf
# Create cluster
$ sudo pg_dropcluster 9.3 main
$ sudo pg_createcluster -d /data/postgres/9.3/main 9.3 main
# Restore: Make sure to use the 9.3 version of psql
$ psql -d postgres -p 5433 -f /mnt/dumps/pre-upgrade.dump
# Or nohup version:
$ sudo -u postgres nohup psql -d postgres -p 5433 -f /mnt/dumps/pre-upgrade.dump > upgrade-restore-nohup.out 2>&1 &
# Verify your data was properly imported
# Drop old cluster
$ sudo pg_drop cluster --stop 9.1 main
# Change back port (optional)
$ sudo service postgresql stop
$ sudo nano /etc/postgresql/9.3/main/postgresql.conf
# change to 5432
# Change the postgres conf and hba conf back to what it was! (reference previously copied files)
$ sudo nano /etc/postgresql/9.3/main/postgresql.conf
$ sudo nano /etc/postgresql/9.3/main/pg_hba.conf
# Analyze
$ sudo service postgresql start
$ psql
>\c your_database
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.