Upgrading PostgreSQL from 9.4 to 9.5 on Ubuntu 15.10

Piotr Okoński – February 8th, 2016

PostgreSQL, Ubuntu, databases

Now that PostgreSQL 9.5 came out with even more awesome features, it’s time to upgrade!

Since upgrading from 9.4 might be a bit of a hassle if we used official Ubuntu repositories before, I decided to write a short guide to help with the process.

Backing up data

Before we proceed it is very important to back up your current cluster.

pg_dumpall > 9-4-backup

Installing new version

If you are using APT repositories from PostgreSQL just install the postgresql-9.5, otherwise follow the steps below.

  1. Add PostgreSQL apt repository to your sources

    echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgres.list
    

    this will create a new /etc/apt/sources.list.d/postgres.list file with a following line:

    deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main

    Where wily is your distribution’s codename.

  2. Add repository key for the newly added source

    sudo apt-get install wget ca-certificates
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    
  3. Update and install the new PostgreSQL 9.5 package

    sudo apt-get update
    sudo apt-get install postgresql-9.5
    

    Note: if you used any additional extensions like hstore from postgresql-*-9.4 packages, now is the time to install their version 9.5 equivalents. Without them the data migration will not work.

    Luckily, in Ubuntu 15.10 the contrib package is now installed automatically with the main package.

Upgrading

Before you proceed, make sure you are logged in as a Postgres user with super powers. In Ubuntu, switching to postgres user is enough:

sudo -iu postgres

During the installation of the new version, you’ll notice that a new directory for 9.5 version was created.

  1. Remove the newly created empty cluster

    pg_dropcluster 9.5 main
    

    During the installation of the new version, you’ll notice that a new directory for 9.5 version was created.

    We are removing it because our upgrade process from 9.4 will create it again and convert the old data format to work with 9.5.

  2. Upgrade your databases

    pg_upgradecluster 9.4 main
    

    Depending on the amount of data you had, this may take a long while. After it finishes sucessfully, you’ll see this message:

    Success. Please check that the upgraded cluster works.
    
  3. Remove the old cluster

    Postgres 9.5 should now be running, check your local apps which use the Postgres databases to confirm data is correct. If all seems to work, you can remove the old 9.4 cluster by running:

    pg_dropcluster 9.4 main
    

    If something goes wrong we always have the backup from the very first step.

That’s it for the migration process, enjoy yet another fantastic Postgres release!

We love to solve tough problems. Got one?   Hire us