= Moving !GeoNetwork from MySQL to Postgres =

Author : Heikki Doeleman

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

TODO describe the process

=== 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]