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.)

Huh.
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
./configure
make
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/postgis-2.1.so /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/

Finally:
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