= 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 : {{{ }}} === config.xml === !GeoNetwork's config.xml needs to be changed to add Postgres JDBC connection settings : {{{ main-db jeeves.resources.dbms.DbmsPool username password org.postgresql.Driver jdbc:postgresql://127.0.0.1:5432/geonetwork 10 }}} 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://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]