Moving GeoNetwork from MySQL to Postgres ¶

Author : Heikki Doeleman

This page describes steps taken to move from a MySQL-based installation to aPostgres-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 a Postgres timestamp datatype. We may switch to that.

creating a database and user ¶

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

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>

Exporting existing data from MySQL and importing it into Postgres ¶

TODO describe the process

Links to related information ¶

​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