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 ​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.

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

​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