PostGIS updating hints
Outdated Information
The information on this page is no longer updated.
From PostGIS 2.1 to PostGIS 2.3 (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:
- Edit /etc/apt/sources.list
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
- Install postgresql-9.4-postgis-2.3
sudo apt update sudo aptitude install postgresql-9.4-postgis-2.3
- You have to tell aptitude to do the right thing here, meaning to skip the options which do not result in installing the packages postgresql-9.4-postgis-2.3 and postgresql-9.4-postgis-2.3-scripts.
- Upgrade postgis extension
psql <yourdb> ALTER EXTENSION postgis UPDATE TO "2.3.1";
Install PostgreSQL 9.6 with PostGIS 2.3 in Jessie
- Install postgresql-9.6
sudo aptitude install postgresql-9.6
- Choose the option to upgrade libpq, postgresql-client-common and postgresql-common.
- Install postgis-2.3.1 from source
# install required libraries aptitude install postgresql-server-dev-9.6 libxml2-dev libproj-dev libgdal-dev # download postgis cd /usr/local/src wget http://download.osgeo.org/postgis/source/postgis-2.3.1.tar.gz tar xvzf postgis-2.3.1.tar.gz # configure, build and install cd postgis-2.3.1 ./configure make make install
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:
- (A). A pg_dump_all that dumps only users/schemas/views/keys/etc and data, not the rest! But that will be hard considering that all kinds of keys/sequences/etc need to be preserved, but not the types/functions that are upgraded in the new setup. And some people might have very elaborate triggers/functions written that depend on a specific version of the libraries, so these can never be automatically upgraded.
- Perhaps an interactive upgrade (example: Do you whish to restore 600 postgis functions? No...).
- And maybe also the same setup for shp2pgsql as for instance createlang b--cluster (or callable with --pgis_version or something). But since that isn't available (yet) here is the manual way:
What I suggest is this:
- Use multicluster and first make sure the original install works again after installing postgresql-common,
- then upgrade into a separate cluster (running on a different port).
- When that works, change ports of the clusters.
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:
- Keep your original data at the original configuration. In my case that was pg7.4 and pgis0.9. I 'upgraded' that to pg7.5/pgis0.9 by installing the postgresql-common package and manually copying postgis to its new place (see earlier post).
- Make sure everything works including your mapserver installation, etc. and all userinterfaces for clients like a website. This install is not only your backup, all your users will not be aware of all the upgrade trouble you'll deal with in the separate cluster. For me everything worked (and there should not be a reason why not, since nothing really changes, except for the paths where pg and pgis are located).
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.
- (B) For this I installed pg8.0 and created a separate 'cluster' on port
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.
- (C) I used dump_all and dumped to plain/SQL-inserts and manually deleted
all the (postgis) function creation out of this file. You keep:
- table creation
- user creation
- data insertion
- triggers/keys/sequences etc.
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...
- 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)
- 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...
- 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).
- Case 1: without schemas
dump of: db (pg_dump -h [hostname] -U [username] -W -c -d -R -v [database] > database.sql) + geometry_columns data only (via phppgadmin)
- create new db in pg/postgis 8.0/1.0.4 (property of normal_user, with template_gis) create database new_db owner normal_user template=template_gis;
restore as normal_user: psql -h [hostname] -U [normal_user] -W [new_db] -e < database.sql
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:
dumping one schema at a time pg_dump -h [hostname] -U [username] -W -c -d -R -v [database] -n [schemaname]> database.sql clean up the dump, removing "set authorization to...., set search_path.... drop ....." (this helps cleaning up the errors, not to miss the important ones)
- dumping geometry_columns (only data, via phppgadmin).
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