How to: Upgrade PostgreSQL on Ubuntu
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:
- Back-up the existing PostreSQL data and configuration
- Install the new version of PostgreSQL
- Delete the default cluster created by the install
- Upgrade the existing cluster (which migrates the data and configuration)
- 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.dumpCreate the file /etc/apt/sources.list.d/pgdg.list:
touch /etc/apt/sources.list.d/pgdg.listEdit the new file to add a line for the repository:
nano /etc/apt/sources.list.d/pgdg.listThe 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 mainRefer 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 updatesudo apt-get upgradeThen we need to upgrade the existing cluster.
First, stop the PostgreSQL Service:
sudo service postgresql stopDelete the default 9.4 cluster created by the 9.4 install (or whatever version has been installed):
sudo pg_dropcluster --stop 9.4 mainNext, we create a new 9.4 cluster from the existing 9.1 cluster (or whatever the existing version is):
sudo pg_upgradecluster 9.1 mainOnce you’re happy everything has been upgraded successfully you can delete the old cluster:
pg_dropcluster --stop 9.1 main
