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