Upgrading from PostgreSQL 9.5 to 9.6 on Ubuntu 16.04

By Daniele, on Oct 2016

While updating my DigitalOcean droplet, I noticed there was a newer release of PostgreSQL 🐘 so I decided to migrate my Rails apps to use that.

Whereas I initially thought it would be a harrowing endeavor, it turned out to be relatively simple. Here are the steps I used to upgrade:

1. Backup your database

The first step is to backup your database. The way I do this is using a .pgpass file in my home folder, with the following:

#hostname:port:database:username:password
localhost:5432:myapp_prod:myuser:s3cuRep@ssword
You can then issue the following command:
$ pg_dump --no-password yourapp_prod > /home/your_user/yourapp_prod.pgsql
and PostgreSQL should automatically detect the .pgpass file.

2. Stop your running PostgreSQL instance

A simple

$ sudo /etc/init.d/postgresql stop
will do.

3. Install the latest PostgreSQL

Ensure you have the latest package and its dependencies:

$ sudo apt-get install postgresql-9.6

4. Upgrade the cluster

You need to drop the cluster on the new Postgres instance first, then run an upgrade command, as follows:

$ sudo pg_dropcluster --stop 9.6 main
$ sudo pg_upgradecluster -v 9.6 9.5 main
Notice how the second command takes the new version first and the old version as the second parameter.

5. Remove the old cluster and purge

If the above has worked (and you've backed up your database) you can now simply drop the old cluster and uninstall the obsolete version of Postgres:

$ sudo pg_dropcluster --stop 9.5 main
$ sudo apt-get purge postgresql-9.5*
$ sudo apt-get autoremove

6. Conclusion

You can ensure that you are using PostgreSQL 9.6 now with the following:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
If the above has all worked but your app complains for some reason, ensure that:
  1. You've stopped the postgres service (/etc/init.d/postgresql stop)
  2. You've restarted the postgres service (/etc/init.d/postgresql start)
  3. PostgreSQL permissions are correct
That should be all!

Back