Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Upgrading PostgreSQL from 9.4 to 9.5 when upgrading Ubuntu 14.10 to 16.04

TL;DR

sudo pg_dropcluster 9.5 main --stop
sudo service postgresql stop
sudo pg_upgradecluster -m upgrade -k 9.4 main
sudo su postgres -c "/usr/lib/postgresql/9.5/bin/vacuumdb --all --analyze-in-stages"
sudo pg_dropcluster 9.4 main
Owner

Komzpa commented Dec 17, 2016

9.5 to 9.6 with postgis 2.2 to 2.3 at the same time:

sudo pg_dropcluster 9.6 main --stop
sudo service postgresql stop

sudo ln -sf /usr/lib/postgresql/9.6/lib/postgis-2.3.so /usr/lib/postgresql/9.6/lib/postgis-2.2.so
sudo ln -sf /usr/lib/postgresql/9.6/lib/rtpostgis-2.3.so /usr/lib/postgresql/9.6/lib/rtpostgis-2.2.so
sudo pg_upgradecluster -m upgrade -k 9.5 main

# do for all postgis-enabled databases
sudo su postgres -c "psql -d gis -c 'alter extension postgis update;'"

sudo rm /usr/lib/postgresql/9.6/lib/rtpostgis-2.2.so /usr/lib/postgresql/9.6/lib/postgis-2.2.so
sudo su postgres -c "/usr/lib/postgresql/9.6/bin/vacuumdb --all --analyze-in-stages"
sudo pg_dropcluster 9.5 main
sudo apt remove postgresql-9.5

hfs commented May 6, 2017

@Komzpa I also needed to softlink /usr/lib/postgresql/9.6/lib/postgis_topology-2.3.so to 2.2

helix84 commented May 24, 2017

Better than linking the old compiled extensions is to recompile them with the new pgxn. For your case, it would be something like:

sudo apt-get install postgresql-server-dev-9.6
sudo pgxn install postgis
sudo pgxn install rtpostgis
sudo pg_upgradecluster -m upgrade -k 9.5 main

Running "alter extension postgis update" didn't seem necessary; in my case it only told me I already have the latest version.

Owner

Komzpa commented Aug 29, 2017

9.6 to 10:

sudo pg_dropcluster 10 main --stop
sudo service postgresql stop

sudo ln -sf /usr/lib/postgresql/10/lib/postgis-2.4.so /usr/lib/postgresql/10/lib/postgis-2.3.so 
sudo ln -sf /usr/lib/postgresql/10/lib/rtpostgis-2.4.so /usr/lib/postgresql/10/lib/rtpostgis-2.3.so 
sudo ln -sf /usr/lib/postgresql/10/lib/libpgrouting-2.4.so /usr/lib/postgresql/10/lib/libpgrouting-2.2.so

sudo pg_upgradecluster -m upgrade -k 9.6 main

# do for all postgis-enabled databases
sudo su postgres -c "psql -d gis -c 'alter extension postgis update;'"

sudo su postgres -c "/usr/lib/postgresql/10/bin/vacuumdb --all --analyze-in-stages"
sudo pg_dropcluster 9.6 main
sudo apt remove postgresql-9.6
Owner

Komzpa commented Aug 29, 2017

@helix84 please pay attention: the extensions aren't symlinked from old postgres version, they're symlinked from newer version of the same extension, compiled for the same postgres. Thread on that in PostGIS mailing list: https://lists.osgeo.org/pipermail/postgis-devel/2017-August/026319.html

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment