How to: Upgrade PostgreSQL on Ubuntu

Make an enquiry

01245 64 64 64

A how to guide for upgrading PostgreSQL on Ubuntu.

Upgrading PostgreSQL is actually quite a straightforward operation compared to doing the same with MySQL or MSSQL.

Here's a quick summary of the process:

  1. Back-up the existing PostreSQL data and configuration
  2. Install the new version of PostgreSQL
  3. Delete the default cluster created by the install
  4. Upgrade the existing cluster (which migrates the data and configuration)
  5. Delete the old cluster

For this example I upgrade PostgreSQL from version 9.1 to 9.4.  But I assume the steps should carry over without any problems to other versions too.

Please note: This upgrade was performed on a development server so downtime and data loss was not a major concern, therefore this method may not be best for a production environment.

To start, back-up the data and configuration:

pg_dumpall > backup.dump

Create the file /etc/apt/sources.list.d/pgdg.list:

touch /etc/apt/sources.list.d/pgdg.list

Edit the new file to add a line for the repository:

nano /etc/apt/sources.list.d/pgdg.list

The line to add will depend on the version of Ubuntu you are running, the following line is for Ubuntu 12.04 (precise):

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

Refer to the PostgreSQL documentation for other Ubuntu versions.

Import the repository signing key:

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

Then run the following to update the package lists and install the latest version:

sudo apt-get update
sudo apt-get upgrade

Then we need to upgrade the existing cluster.

First, stop the PostgreSQL Service:

sudo service postgresql stop

Delete the default 9.4 cluster created by the 9.4 install (or whatever version has been installed):

sudo pg_dropcluster --stop 9.4 main

Next, we create a new 9.4 cluster from the existing 9.1 cluster (or whatever the existing version is):

sudo pg_upgradecluster 9.1 main

Once you're happy everything has been upgraded successfully you can delete the old cluster:

pg_dropcluster --stop 9.1 main

Tagged with:

You might also like...

Vim: Demystifying the Beast

by