Changes between Initial Version and Version 1 of HowToMoveFromMySQLToPostgres

Timestamp:

Jul 10, 2009, 3:51:11 AM (15 years ago)

Author:

heikki

Comment:

--

HowToMoveFromMySQLToPostgres

@@ +1 @@
+= 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 [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
+ }}}
+
+
+=== 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 ===
+
+[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]
+