Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save Komzpa/994d5aaf340067ccec0e to your computer and use it in GitHub Desktop.
Save Komzpa/994d5aaf340067ccec0e to your computer and use it in GitHub Desktop.
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
Copy link
Author

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
Copy link
Author

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
Copy link

@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
Copy link
Author

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
Copy link

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
Copy link

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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