upgrading from postgres 9.1 to 9.4 on ubuntu 12.04

Today I did an upgrade of postgres db from 9.1 to 9.4. And I didn’t find a page that would simply give me the steps to do so. I had to go to several pages and find the relevant commands to upgrade.
And here are steps listed that I followed to do the upgrade. And I am also putting in the references to the pages where i picked them from:

  1. Follow the steps listed on  https://wiki.postgresql.org/wiki/Apt#Quickstart
    1. sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’
    2. sudo apt-get install wget ca-certificates
    3. wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
    4. sudo apt-get update
    5. sudo apt-get install postgresql-9.4
  2. And then use pg_upgrade https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian
    1. sudo pg_lsclusters — check both the dbs are running
    2. sudo /etc/init.d/postgresql stop
    3. cd /tmp
    4. sudo -H -u postgres /usr/lib/postgresql/9.4/bin/pg_upgrade
      -b /usr/lib/postgresql/9.1/bin
      -B /usr/lib/postgresql/9.4/bin
      -d /var/lib/postgresql/9.1/main
      -D /var/lib/postgresql/9.4/main
      -o ‘ -c config_file=/etc/postgresql/9.1/main/postgresql.conf’
      -O ‘ -c config_file=/etc/postgresql/9.4/main/postgresql.conf’
    5. sudo pg_ctlcluster 9.4 main start
    6. sudo -u postgres psql –cluster 9.4/main

Have a look at this page for more options : http://www.postgresql.org/docs/9.4/static/pgupgrade.htm

If you want to change the port of the postgres 9.4 to the old one, go to  /etc/postgresql/9.4/main/postgresql.conf file and look for “port”, and change it to the value needed.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.