Showing results for 
Search instead for 
Do you mean 

Migrating from old PostGIS to new PostGIS Read-Write data server

by Technical Evangelist on ‎12-07-2016 05:41 AM (1,071 Views)

A question often arises how to use the data stored in a PostGIS warehouse with the old open source version of PostGIS GDO. This article provides step-by-step instructions how to migrate from the old open source data server to the new one fully supported version.


First of all we need to emphasize two facts:

  1. It is not possible to access the old data with the new data server and vice versa. Or at least this is impossible if you require editing capabilities.
  2. It is possible to have both data servers installed and registered on the same machine.


So it is clear that in order to manage the old data with the new GDO dataserver, the data must be transferred to a new database. We will explain steps needed to achieve that using the command line utilities provided by PostgreSQL.


First create a new database:



>createdb -T template0 mynewdb


go to the psql utility connecting to that new database:



>psql -d mynewdb


create the PostGIS extension using the two following commands:



##create extension postgis;
##create extension postgis_topology;



escape the psql using \q command.


If you don't want to connect to that database as superuser (recommended), change the database owner with running psql without database specified:



and from psql run the following command:



##alter database mynewdb owner to mygdouser;



Then run Database Utilities (the official one delivered with GeoMedia 2016 or newer), connect to this mynewdb database as the mygdouser (or as the superuser if you omitted the last step) and Create the Metadata tables. Close Database Utilities.


Run GeoMedia, connect to the old database using the old "PostGIS" data server, and then make a connection to the mynewdb using the new "PostGIS Read-Write" data server. Run the "Output to Feature Classes" command, select the source feature classes you want to export, and select the mynewdb connection as the target database. Click OK and the data should be copied to your new database. After that, you can start using the new PostGIS Read-Write dataserver to maintain your data with your new database.



Prior migrating your data into new database, you should be aware of some differences between the old and new version. There are basically two of them:

  1. The new dataserver creates all tables and fields in lowercase and does not allow non-ASCII characters in table and field names. So if you create a geometry table manually using the "" notation, for example as create table "MyNewTable", then the table name will be case sensitive and the new data server will have problems accessing that table. Also if you are used to name your tables using Cyrillic, for example, it will not work in the new version.
  2. The old dataserver created all the geometry fields as dedicated geometry type field, such as MULTIPOLYGON, MULTILINESTRING etc. These geometry types cannot store circular arcs, thus whenever a geometry with arcs has been inserted from GeoMedia, the data server silently stroked the arcs. The advantage of this was better compatibility with other OSS such as QGIS. The new data server creates geometry columns with more general geometry type allowing arc segments, which is more convenient for GeoMedia users, on the other hand this may cause some troubles when accessing the data with 3rd party software.