Upgrading PostgreSQL from 9.4 to 9.5 to 9.6 to 10 when upgrading Ubuntu 14.10 to 16.04

upgrade-postgres-9.4-to-9.5-to-9.6-to-10.md

TL;DR

9.4 -> 9.5:

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

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

9.6 to 10 with postgis 2.3 to 2.4 at the same time:

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

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

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.

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

@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

@Komzpa thanks for the post -- I'm currently on postgres 9.1, trying to get to 10. Would you recommend taking the approach you outlined above (i.e. 9.1->9.4->9.5->9.6->10) or can I just go 9.1->9.6->10 ?

Any insight would be helpful. Thanks!

@Drewster727 you can probably go 9.1 -> 10 directly. The pg_upgrade tool is appended with migration code, not replaced completely for each version.

Hi @Kompza,

Thanks for making this available. I had been researching how to upgrade my 9.6.8 PostgreSQL with PostGIS and OpenStreetMap data to 10.5, but there is a lot of variations in examples, and few that also include the PostGIS update. Recognizing your name, I looked up this search result that proved really helpful. I have now successfully upgraded the instance.

Just one minor remark: maybe it would be good to include a minor note for those less familiar with OpenStreetMap, that the "libpgrouting" text line can be skipped in case you do not have the pgRouting extension for PostGreSQL installed (as I do).

Is it safe to upgrade 9.5 to 10 , skipping 9.6 ?
Or should I do 2 stage upgrade?

doing so in my development environment works fine, but I'm not sure if this is the correct way of doing it.

11+postgis3 to 12+postgis3:

sudo pg_dropcluster 12 main --stop

# do if your system was wal_level=minimal
psql -c "alter system set wal_level = 'replica'"
sudo service postgresql stop
sudo pg_upgradecluster -m upgrade -k 11 main
# do for all postgis-enabled databases
sudo su postgres -c "psql -d gis -c 'select postgis_extensions_upgrade();'"
sudo su postgres -c "alter extension postgis update TO \"3.0.0alpha2next\";"
sudo su postgres -c "alter extension postgis update TO \"3.0.0alpha2\";"
sudo su postgres -c "/usr/lib/postgresql/12/bin/vacuumdb --all --analyze-in-stages"
sudo pg_dropcluster 11 main
sudo apt remove postgresql-9.6

Now that everyone kills off plpython2, you may be unable to install it with new version of postgres. To hunt it down and kill:

drop language plpythonu;
SELECT * FROM pg_catalog.pg_proc where probin='$libdir/plpython2';

12+postgis3.1 to 13+postgis3.0, dev build oh h3_pg:

sudo pg_dropcluster 13 main --stop
sudo pgxn install h3
sudo apt install postgresql-plpython3-13
psql -c "alter system set wal_level = 'replica'"

alter extension postgis drop function st_hexagon;
drop function ST_Hexagon;
alter extension postgis drop function st_hexagongrid;
drop function st_hexagongrid;
alter extension postgis drop function st_square;
drop function st_square;
alter extension postgis drop function st_squaregrid;
drop function st_squaregrid;

alter extension h3 drop function h3_get_h3_unidirectional_edge_boundary;
drop function h3_get_h3_unidirectional_edge_boundary;
alter extension h3 drop function h3_set_to_multi_polygon;
drop function h3_set_to_multi_polygon;

alter extension postgis drop function st_tileenvelope( integer, integer, integer, geometry, float);
drop function st_tileenvelope( integer, integer, integer, geometry, float);

sudo service postgresql stop
sudo pg_upgradecluster -m upgrade -k 12 main
sudo service postgresql start
sudo su postgres -c "/usr/lib/postgresql/13/bin/vacuumdb --all --analyze-in-stages"
# psql -c "alter system set wal_level = 'minimal'"