Updrading Postgresql 9.3 to 9.5 with Postgis.

Upgrading Postgresql minor versions is quite straght forward. You can do so with the pg_upgrade tool that's part of the binaries. But there is always a but, this is true only when Postgis extensions are not being used. The errors that I ran into while upgrading and their fixes are shared here for the benefit of anyone else who may run into the same issues.

The first error in the upgrade process had nothing to do with postgis though. It was due to the wrong encoding. 

encodings for database "postgres" do not match:  old "UTF8", new "SQL_ASCII"

This was a problem that could be easily overcome by dropping the database and recreating it with the proper encoding.

/usr/pgsql-9.5/bin/initdb -E 'UTF-8' 9.5

But you will run into trouble once again with libraries

The contents of the loadable_libraries.txt file is as follows:

The requirement of tablefunc.so can be fullfilled by installing  postgresql95-contrib.x86_64 while repmgr_funcs.so is obviously from  repmgr95.x86_64

The postgis requirement isn't so easy.  What comes with postgresl-9.5 on centos is postgis-2.2 whereas 9.3 had 2.1. You have two choices; You can manually install 2.1 on the new server or you can do a hard upgrade. The first option is a little bit more work (you will invariably have to install the -devel versions of various libraries in order to be able to compile postgis from source) but it will minimize downtime. Hard upgrade involves dumping the entire database manually and importing it back.

So I decided to take the manual compile path only to find that it cannot be crossed.  When tying out the compile Postgis option it was discovered that the --prefix parameter to configure was broken. So the only choice for me is to do a hard upgrade. So it's a hard upgrade, and the hard upgrade is very hard because there are all kinds of inexplicable errors showing up. Here are a few examples:

ERROR:  invalid byte sequence for encoding "UTF8": 0xe0 0xaf 0x2e
ERROR:  value too long for type character varying(128)
ERROR:  insert or update on table "announcements_announcement" violates foreign key constraint "announcements_announcement_creator_id_fkey"
DETAIL:  Key (creator_id)=(1) is not present in table "auth_user". 

The data that doesn't fit into 128 chars now obviously fitted in the old version, this and the encoding errors indicate that some form of charset conversion is happening. More pressing is the foreign key errors. These users definitely exist on the roadlk database.

Update May 01: Finally solved this by not using postgis_restore and importing the database direct with pg_restore!