Mysql to Postgres conversion for olat

[Graham Conway]

HI There,

Given that postgres seems to be the recommended database. Is there a procedure to do a conversion of an exisiting olat installation from mysql to postgres for olat 10.x.x 

Thanks,

Graham Cownay

[Oliver Kant]

Hello Graham,

using MySQL is okay. We are not going to stop developing for MySQL since many
of our customers and the community use it. But if you really want to go that
way, here is a "recipe" to help you.

Probably the best way using opensource tools would be by using pgloader. We
additionally use apgdiff to make sure the scheme is clean.

Use the setupDatabase.sql found in webapp/WEB-INF/classes/database/postgresql/
to set up the database. Then dump the scheme using
$ pg_dump -s -f dest.scheme.sql DBNAME

After that, drop the database again.

Use pgloader to do the raw conversion
$ pgloader mysql://USER:PASSWORD@HOST/DBNAME postgresql://USER:PASSWORD@HOST/
DBNAME

Dump the scheme again
$ pg_dump -s -f source.scheme.sql DBNAME

Find out what has to be changed using apgdiff
$ apgdiff --ignore-start-with source.scheme.sql dest.scheme.sql >upgrade.sql

Finally, connect to the database and make sure the database uses the desired
scheme
$ psql -U USER
# \c DBNAME
# \i upgrade.sql

The most important part: We did this for some of our systems and it will show
some errors, but those were normal in our case, related to differences between
MySQL and Postgres BUT we do of course not guarantee that this method will
work in your case. It is worth a try though.

Cheers
Oliver


--------------------------------------------------------------------
professional services for the e-learning system OpenOLAT
hosting - operating - support - development - mobile - consulting
--------------------------------------------------------------------
http://www.frentix.com | http://www.openolat.org

frentix GmbH
Oliver Kant
Hardturmstrasse 76
CH-8005 Zürich, Switzerland

tel://+41-43-544-9000
fax://+41-43-544-9009

skype: live:oliver_1624
--------------------------------------------------------------------