PostGIS updating hints

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:
/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/ /tmp/<spatial_database>.dmp | psql <spatial_database>

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

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