Skip to content

Instantly share code, notes, and snippets.

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

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

This comment has been minimized.

Copy link
Owner Author

@Komzpa 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

This comment has been minimized.

Copy link

@hfs 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

This comment has been minimized.

Copy link

@helix84 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.

@Komzpa

This comment has been minimized.

Copy link
Owner Author

@Komzpa 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
@Komzpa

This comment has been minimized.

Copy link
Owner Author

@Komzpa 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

@Drewster727

This comment has been minimized.

Copy link

@Drewster727 Drewster727 commented Apr 2, 2018

@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!

@Komzpa

This comment has been minimized.

Copy link
Owner Author

@Komzpa Komzpa commented Jul 29, 2018

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

@mboeringa

This comment has been minimized.

Copy link

@mboeringa mboeringa commented Nov 6, 2018

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

@Sonosar

This comment has been minimized.

Copy link

@Sonosar Sonosar commented Jan 17, 2019

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.

@Komzpa

This comment has been minimized.

Copy link
Owner Author

@Komzpa Komzpa commented Jun 22, 2019

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
@Komzpa

This comment has been minimized.

Copy link
Owner Author

@Komzpa Komzpa commented Feb 17, 2020

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';
@Komzpa

This comment has been minimized.

Copy link
Owner Author

@Komzpa Komzpa commented Oct 11, 2020

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'"


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.