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

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>

Database create scripts

TODO describe the changes

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