Create a gist now

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.

Show comment
Hide comment
@Komzpa

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

Show comment
Hide comment
@hfs

hfs May 6, 2017

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

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.

Show comment
Hide comment
@helix84

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

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.

Show comment
Hide comment
@Komzpa

Komzpa 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

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.

Show comment
Hide comment
@Komzpa

Komzpa 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

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.

Show comment
Hide comment
@Drewster727

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

Show comment
Hide comment
@Komzpa

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

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.

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