= Moving !GeoNetwork from MySQL to Postgres =

Author : Heikki Doeleman

Date: 07/13/09

This page describes steps taken to move from a MySQL-based installation to a Postgres-based installation. The Postgres version used is 8.3.5.


=== Postgres configuration for command line and JDBC access ===

Depending on your Postgres installation, you may need to make some changes to allow for command line access and to allow for !GeoNetwork's JDBC connection.

I changed :

 {{{
   /var/lib/pgsql/data/pg_hba.conf
 }}}

such that now it contains :

 {{{
   local all all trust
   host  all 127.0.0.1/32 trust
   host  all all 127.0.0.1/32 ident sameuser
   host  all all 127.0.0.1 255.0.0.0 trust
   host  all all 127.0.0.1/32 md5
 }}}

(for an explanation of stuff in this file, see [http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html The pg_hba.conf file]).

And I changed 

 {{{
   /var/lib/pgsql/data/postgresql.conf
 }}}

uncommenting the lines 

 {{{
   listen_addresses = 'localhost' 
   port = 5432   
 }}}

These latter changes require a restart of Postgres. This you can do by issuing :

 {{{
   . /etc/init.d/postgresql stop
   . /etc/init.d/postgresql start
 }}}

=== Database create scripts ===

!GeoNetwork comes equipped with a create-database script for various DBMSes, also for Postgres. This one should do fine for a standard !GeoNetwork installation.

In our case however, the installation is a modified version of !GeoNetwork ([http://www.nationaalgeoregister.nl the Dutch géoportail]). It uses an alter-database script to create some changes idiosyncratic to this project. This file is developed against MySQL. In this file I had to make some changes :

 * change {{{int(10)}}} to {{{int}}}
 * change {{{longtext}}} to {{{text}}}
 * change {{{datetime}}} to {{{varchar(24)}}}
 * change {{{double}}} to {{{float8}}}

The change for {{{datetime}}} is debatable, as there also is an almost-equivalent Postgres {{{timestamp without time zone}}} datatype. We may switch to that.


=== creating a database and user ===

To create a database and user you can issue the following commands :

 {{{
   createuser -SRIP username
   createdb geonetwork
 }}}


=== config.xml ===

!GeoNetwork's config.xml needs to be changed to add Postgres JDBC connection settings :

 {{{
   <!-- - - - - - -->
   <!-- postgres -->
   <!-- - - - - - -->
   <resource enabled="true">
      <name>main-db</name>
      <provider>jeeves.resources.dbms.DbmsPool</provider>
      <config>
         <user>username</user>
         <password>password</password>
         <driver>org.postgresql.Driver</driver>
         <url>jdbc:postgresql://127.0.0.1:5432/geonetwork</url>
         <poolSize>10</poolSize>
      </config>
   </resource>
 }}}

You may do this using GAST, if you are comfortable using that tool.


=== Exporting existing data from MySQL and importing it into Postgres ===

There are many ways to achieve this, and all of them require manual fiddling with data dumps and things like that.

Many thanks to Thijs Brentjens who gave this excellent tip, I've found a relatively straightforward way of doing it using the tool [http://www.gdal.org/ogr2ogr.html ogr2ogr] which is part of the [http://www.gdal.org/index.html GDAL] tool suite. The ogr2ogr build I found on their web site does not include drivers for MySQL and Postgress though. Downloading the tool suite [http://fwtools.maptools.org/ FWTools], which includes GDAL, solved this.

Using ogr2ogr, you can simply replicate a MySQL database into a Postgres database. The end result needs some massaging, but not much.

Assuming you already created a Postgres database 'geonetwork' and a Postgres user 'postgressusername', you issue a command like

{{{
   ogr2ogr -overwrite -update -f "PostgreSQL" PG:"host=localhost user=postgressusername dbname=geonetwork password=postgressuserpassword" MYSQL:"geonetworkdbname,host=localhost,user=mysqlusername,password=mysqluserpassword,port=3306"
}}}

This creates the table structure in Postgres and copies the data into it. 

There are only 2 little tweaks necessary to make things work :

 * each table in Postgres gets a column {{{wkb_geometry}}} that was not there in your original, MySQL table
 * Primary key column {{{id}}} gets called {{{ogc_fid}}} in Postgres

The {{{wkb_geometry}}} columns can be fixed (i.e. removed) using this sql (thanks Thijs Brentjens) :

{{{
-- In pgsql:
-- create the SQL commands:

select 'ALTER TABLE ' || table_name || ' drop column wkb_geometry;' from information_schema.tables where table_schema='public';

-- save the results to file and/or execute it directly:
ALTER TABLE isolanguages drop column wkb_geometry;
ALTER TABLE categories drop column wkb_geometry;
ALTER TABLE categoriesdes drop column wkb_geometry;
ALTER TABLE groups drop column wkb_geometry;
ALTER TABLE groupsdes drop column wkb_geometry;
ALTER TABLE languages drop column wkb_geometry;
ALTER TABLE isolanguagesdes drop column wkb_geometry;
ALTER TABLE monitoringrequests drop column wkb_geometry;
ALTER TABLE monitoringservices drop column wkb_geometry;
ALTER TABLE metadata drop column wkb_geometry;
ALTER TABLE metadatacateg drop column wkb_geometry;
ALTER TABLE metadatarating drop column wkb_geometry;
ALTER TABLE operationallowed drop column wkb_geometry;
ALTER TABLE monitoringlog drop column wkb_geometry;
ALTER TABLE monitoringstatsgroups drop column wkb_geometry;
ALTER TABLE operations drop column wkb_geometry;
ALTER TABLE operationsdes drop column wkb_geometry;
ALTER TABLE regions drop column wkb_geometry;
ALTER TABLE usergroups drop column wkb_geometry;
ALTER TABLE regionsdes drop column wkb_geometry;
ALTER TABLE relations drop column wkb_geometry;
ALTER TABLE settings drop column wkb_geometry;
ALTER TABLE users drop column wkb_geometry;
ALTER TABLE sources drop column wkb_geometry;
}}}

TODO how to fix the IDs
TODO how to handle Postgres / MySQL on different machines 
TODO should we change the !GeoNetwork Postgress create-db script to match ogr2ogr's definition ?

In case the {{{ogr2ogr}}} command above gave you errors related to encodings, you might try this (Thijs Brentjens) :

{{{
alter database geonetworktest set client_encoding=latin1;
}}}




=== Links to related information ===

[http://fwtools.maptools.org/ FWTools: Open Source GIS Binary Kit for Windows and Linux]

[http://www.gdal.org/ogr2ogr.html ogr2ogr]

[http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL Converting from other Databases to PostgreSQL]

[http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL Converting MySQL to PostgreSQL]

[http://www.pageofguh.org/technicality/584 MIGRATING A DATABASE FROM MYSQL 5 TO POSTGRESQL 8]

[http://www.markslade.name/Articles.html?a=9 Converting from MySQL to PostgreSQL]

[http://www.sitepoint.com/article/site-mysql-postgresql-1/ Migrate your site from MySQL to PostgreSQL]