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 ​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 (​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 ​ogr2ogr which is part of the ​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 ​FWTools, which includes GDAL, solved this.

Links to related information

​FWTools: Open Source GIS Binary Kit for Windows and Linux

​ogr2ogr

​Converting from other Databases to PostgreSQL

​Converting MySQL to PostgreSQL

​MIGRATING A DATABASE FROM MYSQL 5 TO POSTGRESQL 8

​Converting from MySQL to PostgreSQL

​Migrate your site from MySQL to PostgreSQL