Ubuntu 12.04 - upgrading Postgres 9.2 / PostGIS 2.0 to Postgres 9.3 / PostGIS 2.1

upgrade_script_client.sh

# Add the repo for PG 9.3
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/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/pgdg.list"
sudo apt-get update -y

# Install the client libs
sudo apt-get install postgresql-client-9.3 -y

upgrade_script_server.sh

# NOTE: stop cron job for backups
# NOTE: put nginx in maintenance mode (cap <env> deploy:web:disable)
# NOTE: disable sidekiq workers (sudo stop workers)
# NOTE: disable rails cron jobs

# fix old bug from 9.1 packages
# see http://serverfault.com/questions/563073/postgresql-9-3-installation-fails
update-alternatives --remove postmaster.1.gz /usr/share/postgresql/9.1/man/man1/postmaster.1.gz

# may need to do these too?
# sudo apt-get install -f

# remove old postgis packages
sudo dpkg -r postgis gdal geos proj4

# add postgres apt repo
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/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/pgdg.list"

# Update apt repos
sudo apt-get update -y

# update Postgis from 2.0 to 2.1 on existing instance
sudo apt-get install postgresql-9.2-postgis-2.0 postgresql-9.2-postgis-2.1 -y

sudo service postgresql start

# do this for each database with PostGIS extensions
psql -d network360 -c "ALTER EXTENSION postgis UPDATE TO \"2.1.1\";"

# shut down existing service
sudo service postgresql stop

# install new version w/ latest postgis
sudo apt-get install postgresql-9.3-postgis postgresql-contrib-9.3 -y

# Copy over the rules files from the previous postgres installation
sudo cp /usr/share/postgresql/9.2/tsearch_data/* /usr/share/postgresql/9.3/tsearch_data

# shut down old cluster (it was started during upgrade)
sudo service postgresql stop

# switch to postgres user to perform upgrade
sudo su postgres
cd

# copy the existing config to the version-specific folder
cp /etc/postgresql/9.2/main/*.conf /var/lib/postgresql/9.2/main/
cp /etc/postgresql/9.3/main/*.conf /var/lib/postgresql/9.2/main/

# Tell postgres we are upgrading the existing postgres 
# database to the new postgres 9.3 location
/usr/lib/postgresql/9.3/bin/pg_upgrade \
    -d /var/lib/postgresql/9.2/main/ \
    -D /var/lib/postgresql/9.3/main/ \
    -b /usr/lib/postgresql/9.2/bin \
    -B /usr/lib/postgresql/9.3/bin \
    -v -k

# delete old postgres config stuff
exit
sudo rm -rf /etc/postgresql/9.2/

# fix port and other configuration in /etc/postgresql/9.3/main/postgresql.conf
sudo vimdiff /var/lib/postgresql/9.2/main/postgresql.conf /etc/postgresql/9.3/main/postgresql.conf

# copy old authentication rules to /etc/postgresql/9.3/main/pg_hba.conf
sudo cp /var/lib/postgresql/9.2/main/pg_hba.conf /etc/postgresql/9.3/main/pg_hba.conf

# restart postgres
sudo service postgresql start

# analyze new cluster
sudo -u postgres ~postgres/analyze_new_cluster.sh

# delete old cluster files
sudo -u postgres ~postgres/delete_old_cluster.sh

# remove old PostGIS
sudo dpkg --purge postgresql-9.2-postgis-2.0 postgresql-9.2-postgis-2.1

# NOTE: start cron job for backups
# NOTE: take nginx out of maintenance mode (cap <env> deploy:web:enable)
# NOTE: enable sidekiq workers (sudo start workers)
# NOTE: enable rails cron jobs