Upgrade Postgres Database to 9.6 on Ubuntu 16.04

In this tutorial we will see a step by step guide for how to upgrade postgres database without losing data using the pg_upgrade tool provided with postgres. Although these instructions are specifically written for version 9.6 but you can customize it to work with any other version.

 

Step 1 : Install postgres 9.6

Install postgres 9.6 using these instructions

Link : https://tecadmin.net/install-postgresql-server-on-ubuntu/

 

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
$ wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

 

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

 

Step 2 : Upgrade cluster using pg_upgrade utility

Run pg_lsclusters your 9.5 and 9.6 main clusters should be “online”.

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.6-main.log

There already is a cluster “main” for 9.6 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.5/main when 9.6/main also exists. The recommended procedure is to remove the 9.6 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

 

Stop the 9.6 cluster and drop it.

sudo pg_dropcluster 9.6 main --stop

Upgrade the 9.5 cluster to the latest version.

sudo pg_upgradecluster 9.5 main

when you type the above command you might get an error stating you “Unable to stop postgres ” or “Postgres server did not shut-down”

So we will need to stop the postgres server before we type the above command. To stop the Postgres server please type the following command.

sudo systemctl stop postgresql@9.5-main

OR (You can also type the following command as an alternative)

sudo pg_ctlcluster 9.5 main stop –force

after stopping the server please type the upgrade command again

sudo pg_upgradecluster 9.5 main

Your 9.5 cluster should now be “down”.

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

Check that the upgraded cluster works, then remove the 9.5 cluster.

sudo pg_dropcluster 9.5 main

The above instructions are taken from this link

Link : https://gist.github.com/SumeetMoray/c8a0343822b4a07c72b90daf08fb125b

 

 

Other Useful Links :

https://www.safaribooksonline.com/library/view/postgresql-9-administration/9781849510288/ch04s03.html

https://www.postgresql.org/docs/9.6/static/pgupgrade.html

%d bloggers like this: