Thursday, December 15, 2016

A hairy adventure in upgrading Ubuntu and Postgres

(Detailing this more as a historical artifact than anything else, but it might help someone.)

I had an EC2 Ubuntu 14.04 Trusty machine running PostgreSQL 9.3 and PostGIS 2.1 that I had to upgrade. For whatever reason.
sudo do-release-upgrade

but PostGIS was holding it back. I don't remember how I found this out but I think it was a simple google. So I did:
sudo apt-get remove postgresql-9.3-postgis-2.1 # turns out this was a mistake
sudo do-release-upgrade

ok, now I'm on Ubuntu 16.04 Xenial. Now to upgrade postgres:
sudo pg_upgradecluster 9.3 main /data/db/postgresql/9.5/main

ugh but wait, can't do it without PostGIS. hold on --
psql tweet
tweet=# select * from tweet_pgh limit 1;
(some kind of error because I don't have PostGIS installed anymore.)

psql tweet
tweet=# ALTER EXTENSION postgis UPDATE TO "2.3.1";
(some kind of error about "no update path from 2.1.2 to 2.3.1")

Huh. I guess I have to 1. get PostGIS back, 2. give it an "update path" to 2.3.1.
Getting PostGIS back:
sudo apt-get install postgresql-9.3-postgis-2.1
(not found)

Huh. I flailed here for a while and then downloaded 2.1.2 from source, and did the old
make install
But of course it wasn't that easy! Got one compile error about json and using this old ticket, realized I had to go into liblwgeom/lwin_geojson.c and edit "#include " to "#include "
Got another compile error and had to like #IFDEF out references to AggState and WindowAggState in lwgeom_accum.c (as in this old ticket) and assume it'll work (sure did). Or rather, I was able to configure and make it, then I just symlinked it into the right place:
sudo ln -s /home/dantasse/postgis-2.1.2/postgis/ /usr/lib/postgresql/9.3/lib 

THEN, I could do this:
tweet=# select * from tweet_pgh limit 1;

but I couldn't do this:
tweet=# ALTER EXTENSION postgis UPDATE TO "2.3.1";
Still getting the "no update path" error, until I found this old post, and then:
cp /usr/share/postgresql/9.5/extension/postgis--2.1.2--2.3.1.sql /usr/share/postgresql/9.3/extension/

sudo pg_upgradecluster 9.3 main /data/db/postgresql/9.5/main # (took about 8 hours, b/c I think it has to dump and restore the whole DB?)
and we're all good. Ubuntu 16.04, Postgres server and client 9.5, PostGIS 2.3.

Can't believe that worked.

No comments:

Post a Comment