Upgrade Postgresql(9.3 – 9.6) in Ubuntu retaining the databases

We can always install postgresql with fresh database, but the problem arise when we need to upgrade psql retaining all the databases and the data within it. For that to accomplish, following are the steps you need to follow :

  1. Stop the postgresql already running :

    $ sudo /etc/init.d/postgresql stop

  2. Setup the correct repository; add the keys for correct version to be installed.

    $ 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 -

  3. Update the system to implement addition of keys and install new packages

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

    Now when you run this command :

    $ pg_lsclusters

    you will see 2 clusters, 1 for the old version already installed and other is the new version we installed now. Fow me the old one was 9.3 and new is 9.6

  4. Now we will be droping our new version and upgrade the old version as we need to retain our old database cluster.

    $ sudo pg_dropcluster 9.6 main --stop
    $ sudo pg_upgradecluster 9.3 main

  5. Now since our old cluster is upgraded we can drop it.

    $ sudo pg_dropcluster 9.3 main

Confirm the cluster working with this :

$ pg_lsclusters

There should be only one cluster, the latest one, one we want to work with.
Also check if all your databases exist in upgraded postgres version. They should. 😉

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s