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