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.

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

This comment has been minimized.

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.

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.

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

This comment has been minimized.

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

@Drewster727

This comment has been minimized.

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.

Owner

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.

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

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