Upgrading Postgres

Problem : This took me quite a while to figure. I had Postgresql version 9.2 on my machine & I wanted to upgrade it to latest 9.3.  Tried most of the links here and there on internet but was getting wierd errors - mostly because of the configuration issues. Most of the Postgres documentation is good and revolves around using pg_upgrade and can be used actually as is

Solution :

Step 1 - sudo apt-get install postgresql-9.2 postgresql-contrib-9.2 postgresql-server-dev-9.2 \
              postgresql-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3

Note - I skipped this one and got few weird errors. So install the contribs for both the old and new version we are upgrading from 9.2 to 9.3

Step 2 - sudo /etc/init.d/postgresql stop 

Note - This will stop the Postgres instances running in the cluster

Step 3 - cd /tmp

sudo -H -u postgres /usr/lib/postgresql/9.3/bin/pg_upgrade \  -b /usr/lib/postgresql/9.2/bin \

   -B /usr/lib/postgresql/9.3/bin \   -d /var/lib/postgresql/9.2/main \    -D /var/lib/postgresql/9.3/main \

   -o ' -c config_file=/etc/postgresql/9.2/main/postgresql.conf' \    -O ' -c config_file=/etc/postgresql/9.3/main/postgresql.conf'

Note - Do not forget $ cd /tmp here otherwise the system gives error as "cannot write to log file pg_upgrade_internal.log"    

Step 4 - Now after step 3 one might get error as

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

To overcome this one stop the postmaster service that get started via init.d  try the following :

sudo /etc/init.d/postgresql stop

Final Note - The upgraded server will be running on port 5433 and not the default 5432. When connecting via pgadmin, make sure we put in the correct port

After this the upgrade should go smoothly as it happened in my case.


Reference : http://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian