Tag: upgrade

Upgrade PostgreSQL from 9.1 to 9.3 on Kubuntu

This seven steps will perform the upgrade of PostgreSQL from version 9.1 to version 9.3.
This also works in Ubuntu and it can also be used to upgrade between any version numbers.

To upgrade between any versions, just changed the 9.1 for the legacy version number and the 9.3 for the new version number.

First install the necessary dependencies

sudo apt-get update
sudo apt-get -y install python-software-properties

Second, add the PostgreSQL repository

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

Third, setup the repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

Fourth, install PostgreSQL 9.3.

sudo apt-get install postgresql-9.3 postgresql-server-dev-9.3 postgresql-contrib-9.3

Fifth, perform the upgrade process.
The upgrade process is performed by having both servers running at the same time. Note that the new 9.3 version will run on a different port, 5433 as specified in the script bellow, and it will be set to the default port latter when the legacy version is uninstalled and the default port becomes available.

sudo su -l postgres
psql -d template1 -p 5433
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
\q #logout from database
service postgresql stop
/usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.3/main/ -O " -c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"
exit # logout postgresql back to previous user

Sixth, remove the 9.1 version.

sudo apt-get remove postgresql-9.1

Seventh, set the new version server port back to the default value and restart the service.

sudo vim /etc/postgresql/9.3/main/postgresql.conf # find old port of 5433 and change it to 5432
sudo service postgresql restart