Upgrade PostgreSQL 9.3 to 9.6 on Ubuntu 16.04

upgrade_pg.sh

sudo apt-get install postgresql-9.6 postgresql-server-dev-9.6 postgresql-contrib-9.6 -y
 
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS hstore;'"
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";'"
sudo systemctl stop postgresql
sudo su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.6/bin \
-d /var/lib/postgresql/9.3/main/ -D /var/lib/postgresql/9.6/main/ \
-O "-c config_file=/etc/postgresql/9.6/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" --link'
 
sudo apt-get remove postgresql-9.3 -y
sudo sed -i "s:5433:5432:g" /etc/postgresql/9.6/main/postgresql.conf
sudo systemctl enable [email protected]
sudo systemctl start [email protected]

In place upgrade is applied. All upgrade takes 10 seconds for 1.5 GB database.

Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |
sudo apt-key add -
sudo apt-get update

FOR Ubuntu 14.04 Trusty version:::
Keep Postgresql 9.3 on port 5431
Keep Postgresql 9.6 on port 5432
Keep Postgresql 10.0 on port 5434

Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

then :
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |
sudo apt-key add -
sudo apt-get update

sudo apt-get install postgresql-9.6 postgresql-server-dev-9.6 postgresql-contrib-9.6 -y

sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS hstore;'"
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";'"
sudo service postgresql stop
sudo su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.6/bin \ -d /var/lib/postgresql/9.3/main/ -D /var/lib/postgresql/9.6/main/ \ -O "-c config_file=/etc/postgresql/9.6/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" --link'

sudo apt-get remove postgresql-9.3 -y
sudo sed -i "s:5432:5431:g" /etc/postgresql/9.3/main/postgresql.conf
sudo sed -i "s:5433:5432:g" /etc/postgresql/9.6/main/postgresql.conf
sudo service postgresql reload
sudo service postgresql start

Tried this on Ubuntu 16.04 and found that after postgres was installed via apt, the data directory /var/lib/postgresql/9.6/main was not created. I could initialize everything with pg_ctl but aren't sure how that would affect the pg_upgrade script. Any ideas?

Edit: After a bit of digging, I believe the issue was that the 9.6 install setup a cluster for the new instance which was interrupting the pg_upgrade. I ended up getting it working by dropping the new cluster and upgrading the 9.3 cluster using pg_upgradecluster from the following gist: https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44

Install packages

sudo apt-get install postgresql-9.6 postgresql-server-dev-9.6 postgresql-contrib-9.6 -y

List clusters. Should show existing (9.3) and newly installed (9.6)

pg_lsclusters

Output:

Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.6 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.6-main.log

Stop new cluster

sudo pg_dropcluster 9.6 main --stop

Upgrade existing 9.3

sudo pg_upgradecluster 9.3 main

Drop old 9.3 cluster

sudo pg_dropcluster 9.3 main