Upgrading PostgreSQL and PostGIS from 9.3 to 9.4

In this post, we will walk through steps of upgrading from PostgreSQL 9.3 to 9.4. It all happened because my PostGIS version had got corrupted.

When I executed the command to check for my PostGIS version, I got an error saying liblwgeom library could not be loaded

su - postgres -c "psql -c 'SELECT PostGIS_full_version()'"

gave the following error

ERROR:  could not load library "/usr/lib/postgresql/9.3/lib/postgis-2.1.so": liblwgeom-2.1.2.so: cannot open shared object file: No such file or directory

After much research and browsing through forums, I realized it was better to upgrade postgreSQL and PostGIS. I checked the postgres version with the following command to make sure I needed to do that.

I found a great post on stackexchange from someone who had posted their script. With minor changes the script was good. Here is the modified script in its entirety

I executed the following step by step to make sure I could see what was happening...

su - postgres -c "psql -c 'SELECT PostGIS_full_version()'"
su - postgres -c "psql --version"


# Be sure to save your config files.
sudo cp /etc/postgresql/9.3/main/postgresql.conf ~
sudo cp /etc/postgresql/9.3/main/pg_hba.conf ~

# Package repo (for apt-get). I have 14.04 LTS, so I selected trusty-pgdg. Please change to match your version
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

# Also probably optional but I like to update sources and upgrade
sudo apt-get update
sudo apt-get upgrade

# Install postgres 9.4
sudo apt-get install postgresql-9.4 postgresql-server-dev-9.4 postgresql-contrib-9.4

# Get latest postgis for 9.4 (optional)
sudo apt-get install postgresql-9.4-postgis

# dump your data
sudo su postgres
cd ./data/dumps
/usr/lib/postgresql/9.4/bin/pg_dumpall > pre_upgrade_from_9.3_to_9.4.dump

# I got this error:
# pg_dump: [archiver (db)] query failed: ERROR:  could not load library "/usr/lib/postgresql/9.3/lib/postgis-2.1.so": 
# liblwgeom-2.1.2.so: cannot open shared object file: No such file or directory

#This is the elusive fix to the problem
sudo apt-get install liblwgeom-2.1.3

# Then repeated
sudo su postgres
cd ./data/dumps
/usr/lib/postgresql/9.4/bin/pg_dumpall > pre_upgrade_from_9.3_to_9.4.dump

# Make a data dir for Postgres 9.4
sudo mkdir -p /data/postgres/9.4/main
sudo chown -R postgres:postgres /data/postgres

# Change the 9.4 conf file's data dir to point to /data/postgres/9.4/main
sudo gedit /etc/postgresql/9.4/main/postgresql.conf

# Install 9.4 cluster
sudo /etc/init.d/postgresql stop

# May need to restart machine if could not stop both postgreSQL processes
sudo pg_dropcluster 9.4 main
sudo pg_createcluster -d /data/postgres/9.4/main 9.4 main

# start 9.4 explicitly
sudo /etc/init.d/postgresql start 9.4

# Restore: Make sure to use the 9.4 version of psql
psql -d postgres -p 5433 -f ~/data/dumps/pre_upgrade_from_9.3_to_9.4.dump

# Change port back to 5432 (optional) and the confs back to what they were! (reference previously copied files)
sudo gedit /etc/postgresql/9.4/main/postgresql.conf

# Changed local   all   all   peer -> local   all   all   md5
sudo gedit /etc/postgresql/9.4/main/pg_hba.conf

sudo service postgresql restart 9.4

# Verify your data was properly imported

# Drop old cluster
sudo pg_dropcluster --stop 9.3 main


# Analyze
sudo service postgresql start
psql
>\c your_database
> ANALYZE;

Now on starting I still got 2 postgres instances

We need to delete the old PostgreSQL version using commands below... 

#Delete previous postgres
sudo apt-get purge postgresql-9.3
sudo service postgresql restart 9.4

After the restart, it shows only one service starting..

Now, we can verify if PostGIS is correctly installed

su - postgres -c "psql -c 'SELECT PostGIS_full_version()'"

This shows the correct versions and everything should be fine. Finally to check the version of other extensions as well, we can use the following command

psql -c "SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%'"

Looks like everything has got upgraded correctly.