PostGIS updating hints

From PostGIS 2.3 to PostGIS 2.5 (Debian Stretch to Buster with PostgreSQL 9.6 to PostgreSQL 11)

In theory, upgrading PostgreSQL with PostGIS should be straightforward with the ALTER EXTENSION postgis UPDATE TO and pg_upgradecluster commands by hand. Unfortunately, doing so in Debian usually fails, because each PostgreSQL version comes with its own postgis library, and library versions use to change between different Debian versions. Fortunately though, Debian Buster preserves the required libraries from postgresl-9.6-postgis when upgrading from Debian Stretch, making this task a lot easier than it was for the Jessie to Stretch upgrade (which is documented below).

Theoretically, for upgrading PostgreSQL together with PostGIS, you have to do a "hard" upgrade which means dumping the old database in Stretch, upgrading to Buster, and then restoring the database from the dump. As this may be OK for small databases, many systems run huge databases with terabytes of data and the whole dump/restore process can take a lot of time during which you have to keep your database in read-only for consistency. If you want to avoid this, you can follow this procedure for doing a soft-upgrade from Debian Stretch to Buster.

Backup your databases

To be safe, make a full dump and perhaps a file system level backup of all your databases before beginning with the upgrade. You may skip this step if feel safe to do so. Refer to https://www.postgresql.org/docs/9.6/static/backup.html for details on taking PostgreSQL backups. 

# Make a dump of all you databases
pg_dumpall -p <port> | gzip > full_dump.sql.gz

# usually the main PostgreSQL database runs on port 5432, so set <port> accordingly
# you may also use the --verbose option to make this a bit more entertaining

Upgrading Debian Stretch to Buster

See the information at https://www.debian.org/releases/buster/mips/release-notes/ch-upgrading.en.html for details on the upgrade procedure. You may also find more concise instructions using you favorite search machine.

Important: Due to a change in the glibc locale data in Buster, PostgreSQL indexes need to be reindexed immediately after upgrading the locales or locales-all packages, before putting back the database into production (info taken from [1]). For details see https://wiki.postgresql.org/wiki/Locale_data_changes

Suggested command: 

sudo -u postgres reindexdb --all

This info is taken from [2], see this link for further details. 

cp /usr/lib/postgresql/11/lib/postgis-2.5.so /usr/lib/postgresql/11/lib/postgis-2.3.so  
cp /usr/lib/postgresql/11/lib/rtpostgis-2.5.so /usr/lib/postgresql/11/lib/rtpostgis-2.3.so

Important: "Don't ever copy an extension lib from an older major PostgreSQL to a newer major PostgreSQL (for 9 series it's minor version, for 10 and on it's major version). NEVER. A PostGIS compiled against say PostgreSQL 9.6 will not work with any other PostgreSQL Minor version or Major version. An extension compiled for PostgreSQL 9.5 will not work for PostgreSQL 9.6. They may have the same name, but they are incompatible with each other. That goes for every other PostgreSQL extension in existence. e.g a PL/v8 compiled against PostgreSQL 9.6 will not work against PostgreSQL 11. It will never work." [2]

Use pg_upgradecluster to upgrade your databases

Use the Debian script pg_upgradecluster with method "--upgrade" to upgrade PostgreSQL without dumping and restoring (on filesystem level). 

# first drop the  cluster which was created when upgradeing to buster
pg_dropcluster 11 main --stop
# then upgrade the 9.6 cluster to 11
systemctl stop postgresql@9.6-<clustername>.service 
pg_upgradecluster -m link -v 11 9.6 <clustername>
# <clustername> is main by default

Upgrade the postgis extensions

psql <yourdb>
ALTER EXTENSION postgis UPDATE;

# if you have further postgis extensions installes, also update them:
ALTER EXTENSION postgis_sfcgal UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_tiger_geocoder UPDATE;
exit

After this, check the postgis version with 

psql -c "SELECT postgis_full_version();" <yourdb>

Then you can test the new cluster and remove the old one using pg_dropcluster. Also you can remove all postgresql-9.6 packages once you are ready.

[1] Apt listchanges info by Christoph Berg <myon@debian.org> Fri, 12 Apr 2019 14:32:52 +0200
[2] https://www.bostongis.com/blog/index.php?/archives/268-Using-pg_upgrade-to-upgrade-PostGIS-without-installing-an-older-version-of-PostGIS.html.

From PostGIS 2.1 to PostGIS 2.3 (Debian Jessie to Stretch with PostgreSQL 9.4 to PostgreSQL 9.6)

In theory, upgrading PostgreSQL with PostGIS should be straightforward with the ALTER EXTENSION postgis UPDATE TO and pg_upgradecluster commands by hand. Unfortunately, Debian Jessie although having postgresql-9.6 in the backports does not ship with postgresql-9.6-postgis and Debian Stretch does not support postgresql-9.4-postgis. As a result, an easy "soft" upgrade is not possible and you have to do a "hard" upgrade which means dumping the old database in Jessie, upgrading to Stretch and then restoring the database from the dump. As this may be OK for small databases, many systems run huge databases with terabytes of data and the whole dump/restore process can take a lot of time where you have to keep your database in read-only for consistency. If you want to avoid this, you can follow this procedure for doing a soft-upgrade to PostGIS 2.3.1, then installing PostGIS to PostgreSQL 9.6, upgrading and testing the database and finally upgrade to Stretch:

Backup your databases

To be safe, make a custom format dump and perhaps a file system level backup of all your databases before beginning with the upgrade. You may skip this step if feel safe to do so. Refer to https://www.postgresql.org/docs/9.4/static/backup.html for details on taking postgres backups. 

# Make a dump for each of your databases
pg_dump -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

# Additionally dump the global entries
pg_dumpall -g -p 5432 > dumpall_globals.sql

Upgrading PostGIS from 2.1 to 2.3 in Debian Jessie

Simply activate jessie-backports in the apt sources and install the package postgresql-9.4-postgis-2.3: 

deb http://ftp.at.debian.org/debian/ jessie-backports main contrib non-free
deb-src http://ftp.at.debian.org/debian/ jessie-backports main contrib non-free

sudo apt update
sudo aptitude install postgresql-9.4-postgis-2.3

psql <yourdb>
ALTER EXTENSION postgis UPDATE TO "2.3.1";

Install PostgreSQL 9.6 with PostGIS 2.3 in Jessie

sudo aptitude install postgresql-9.6

Use pg_upgradecluster to upgrade your databases

Use the Debian script pg_upgradecluster with method "--upgrade" to upgrade PostgreSQL without dumping and restoring (on filesystem level). 

# first drop the 9.6 cluster which was created when installing postgresql-9.6
pg_dropcluster 9.6 main --stop
# then upgrade the 9.4 cluster to 9.6
systemctl stop postgresql@9.4-<clustername>.service 
pg_upgradecluster -m upgrade 9.4 <clustername>
# <clustername> is main by default

After this, you can test the new cluster and remove the old one using pg_dropcluster. Also you can remove all postgresql-9.4 packages and upgrade to stretch once you are ready. After upgrading to stretch you might encounter some postgis errors due to missing libraries (especially if you do an apt autoremove). In this case simply repeat building and installing postgis-2.3.1 from source as described above.

From PostGIS 1.3 to PostGIS 1.4 (with PostgreSQL 8.3 to PostgreSQL 8.4)

This information deals with the upgrade process from PostGIS 1.3 to PostGIS 1.4 along with the upgrade of PostgreSQL from 8.3 to 8.4. If you have Debian Squeeze and the 8.3 version of PostgreSQL installed, this is a work you have to do in order to install PostGIS 1.4.

There are some scripts that help a lot in the spatially enabled databases upgrade process. 

# As root:
# Make a dump of the non spatially databases:
su postgres
pg_dump -c <database> > /tmp/alpha_database.sql
...

# Make a special dump for the spatially enabled databases:
pg_dump -Fc <spatial_database> > /tmp/<spatial_database>.dmp
...

# Now return to the root account to stop the 8.3 postgreSQL Server:
exit
/etc/init.d/postgresql stop 8.3

# Install postgresql 8.4 and Postgis 1.4
aptitude install postgresql-8.4 postgresql-8.4-postgis

# By default, the server will be listening on port 5433 and has default configs.
# You have to copy some configuration files:   
/etc/init.d/postgresql-8.4 stop
cp /etc/postgresql/8.3/main/pg_hba.conf /etc/postgresql/8.4/main/
cp /etc/postgresql/8.3/main/postgresql.conf /etc/postgresql/8.4/main/
/etc/init.d/postgresql start 8.4

# Now, the server is listening on the same port than the 8.3 server (generaly 5432).
# It is time to restore the non spatially-enabled databases:
su postgres
psql -f /tmp/alpha_database.sql
...

# Here is the process for any spatially-enabled database:
## First, create a new database:
createdb <spatial_database>
## Enable the plpgsql language:
createlang plpgsql <spatial_database>
## Then Install PostGIS 1.4 in it:
psql <spatial_database> -f /usr/share/postgresql-8.4/contrib/postgis.sql
## Then reload the data with a special Perl script:
/usr/share/postgresql-8.4-postgis/utils/new_postgis_restore.pl /tmp/<spatial_database>.dmp | psql <spatial_database>
...
exit

# After some tests, you can purge postgresql 8.3 and old postgis:
/etc/init.d/postgresql-8.4 stop
aptitude purge postgresql-8.3 postgresql-8.3-postgis postgresql-client-8.3
/etc/init.d/postgresql-8.4 start

Old versions

This is the first draft of a HOWTO page for migrating from postgresql 7.4 with postgis 0.9 to postgresql 8.0 with postgis 1.0.

Below I added a different approach to document it.

Hi Paolo & friends,

> We're probably in the worst of situations, having to upgrade both from > 0.9.2 > to 1.0 and from 7.4 to 8.0. Yes we are all in that same situation I think... But it is not that bad, really! Imagine not being able to use the multicluster/multiversion environment...

> 7.4 to 8.0 (I imagine the automated pg_upgradecluster will not work?) The upgrade_cluster will not work, because the postgis versions 0.9 and 1.0 are not compatible, that script basically calls pg_dump_all and restores that again in the new env. The postgis upgrade script did not work for me so I followed their manual steps.

Whishlist: What would greatly help is this:

What I suggest is this:

Downtime can be meassured in minutes ;-). And have extra respectfor Martin Pitt for writing the postgresql-common to make this possible and 'relatively' pain free: thanks Martin!

General: Any shapefile that you loaded as a table, you are probably better of loading again with shp2psql in the new cluster. Note that you should take extra care to be sure that you are using the correct binary of shp2pgsql and that you connect to the correct database! (This is not something you want to do with pgadmin from a MSwindows machine...) 

/usr/lib/postgresql/8.0/bin/shp2pgsql -s 28992 -I -D my.shp
myschema.mytable > myshp.sql
psql -d mynewdb -f myshp.sql --cluster 8.0/main

(Little bit more detailed steps) In a production environment (which is what I deal with) I now did this:

Now the real upgrading: I personally do not see a reason to upgrade to pg8.0/pgis0.9 first so I upgrade straight to pg8/pgis1.0.

5434 (or one of your own favorates). Now I manually installed pgis 1.0 but Alex is working hard on the packages so hopefully that is ok to do with the package. The tricky part is that a simple database dump/restore will not work. The postgis API and the way the objects are stored, changed. There is a script that will attempt this for you, but it doesn't work if you use more elaborate setups (with different languages loaded and other goodies from contrib, I use for instance dblink) and I haven't tested it at all with an upgrade between postgresql versions.

all the (postgis) function creation out of this file. You keep:

the rest you delete: In the file the first set of lines create your databse schema (recognizable by CREATE TABLE etc.). The schema is something you want to keep... Then you get all the functions (recognizable with CREATE FUNCTION) and types (CREATE TYPE), those are things you want to delete, loading the new lwpostgis.sql will have created them for you. Any users that you probably want to keep are created by CREATE USER. I moved this to a separate SQL script. All the inserts in the table you want to keep.

Do not be afraid to delete too much, if it fails you still have the original database up and running so nobody is going to notice it except you...

  1. I manually created my template database in pg8.0 with pgis1.0 and dblink etc. Make sure you follow the correct path to the NEW lwpostgis.sql!(probably /usr/share/postgresql/8.0/contrib/lwpostgis.sql)
  2. Now I create a brandnew database (createdb -T mygistemplate --cluster 8.0/main mynewdb) and load the sanitized dump into it (psql -d mynewdb -f theSanitizedDump.sql --cluster 8.0/main). It SHOULD work error/warning free...
  3. I restored views manually (I did not have that many) by copy/pasting them in PGadmin.

Good luck!

Floris Sluiter

Forwarded from Lia Lia Venturato <venturato AT faunalia.it>

Hi all. We completed successfully our transition from pgis09+pg74 to pgis10+pg80, using a different approach from Floris. Here our report, in the hope it can be useful to someone. Lia

Caution: this method changes the owners of tables, schemas, etc. All grants are kept, but ownership goes to the user (not admin) who restores the db. In addition, user-created functions may not be restored (if they need admin privileges).

you get some errors, because dumped functions (old pgis) cannot be restored, but that's ok: new_db will have new pgis10 functions+ old data.

Geometry_columns and spatyal_ref_sys are property of postgres (from template_gis), so restore gives an error. Spatial_ref_sys is to be kept like this, granting select privileges to the user of the db. Geometry_columns table must be restored from dump of data, so first change its privileges then restore it: 

grant select on spatial_ref_sys to normal_user;
grant all on geometry_columns to normal_user;

then, as normal_user: 

\i /path/dump/data/geometry_columns.sql

* Case 2: with schemas of different owners In the dump you have the instructions to change owner and set search_path during restore (set authorization to nomeutente, set search_path to schemaname). Given that we have to restore as normal_user, this is not possible. We solved the problem by:

The rest is as above, with the following cautions: restore schema public as normal_user, not admin log into the db as normal_user create schemas set search_path to nomeschema and restore the dumps \i /path/to/clean/dump/schema.sql

At the end, grant the right privileges to geographic tables (see above).

Lia Venturato

CategoryTeams