Upgrade PostgreSQL Database
In this article we will learn how to upgrade PostgreSQL Database on ubuntu
In this tutorial we will see a step by step guide for how to upgrade postgres database without losing data using the pg_upgrade utility tool provided. This guide is written for upgrading to postgres 9.6 but it can be generalized to upgrade to any version.
Prerequisite Knowledge
To understand this upgrade guide you dont need to be an expert but having some basic knowledge of linux terminal commands and postgres Database will help you.
Steps for Upgrade
Step 1 : Install postgres newer Version Step 2 : Upgrade cluster using pg_upgrade utility
Step 1 : Install postgres newer version
Install postgres 9.6 using these instructions Link : https://tecadmin.net/install-postgresql-server-on-ubuntu/
Code
$ 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 -
Code
$ 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”.
Code
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.
Code
sudo pg_dropcluster 9.6 main --stop
Upgrade the 9.5 cluster to the latest version.
Code
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
Code
sudo pg_upgradecluster 9.5 main
Your 9.5 cluster should now be “down”.
Code
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.
Code
sudo pg_dropcluster 9.5 main
The above instructions are taken from this link
Link : https://gist.github.com/SumeetMoray/c8a0343822b4a07c72b90daf08fb125b