migrate postgres to mysql

migrate postgres to mysql

by David Shields -
Number of replies: 12

We're currently building a new server for a school (yaay, shiny toys!). Their current setup is moodle 1.7, on postgres, and the new box will be 1.8 on mysql.

I'm guessing I should set up 1.7 on new box first, then upgrade

Any docs / tips/ hints on converting the existing moodle to mysql ?

David

Average of ratings: -
In reply to David Shields

Re: migrate postgres to mysql

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Don't do it! Postgres is a much better database engine than MySQL.

What makes you want to switch?
Average of ratings: -
In reply to Tim Hunt

Re: migrate postgres to mysql

by Anthony Borrow -
Picture of Core developers Picture of Plugin developers Picture of Testers
Tim - From the minimal reading I have done, MySQL performance is considered slightly better (out of the box at least) than Postgres. Am I mistaken? In light of the performance issues after upgrading from 1.6 to 1.8 on our production server, I was recently considering asking which of the 2 databases would be considered better or which one folks would recommend.  Peace - Anthony
Average of ratings: -
In reply to Anthony Borrow

Re: migrate postgres to mysql

by Martín Langhoff -
Tuning PostgreSQL is a whole lot easier than migrating all your content to MySQL -- and you will learn something new and useful wink

And when your data grows (can you say "millions of rows in mdl_log"?) Pg starts to show its strength.
Average of ratings: -
In reply to Martín Langhoff

Re: migrate postgres to mysql

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
The OU chose Postgres for performance, or at least scalability, reasons, on the advice of Catalyst IT.

Of course, if you do go ahead with the switch, before and after performance observations would be a very useful thing for you to share with the community.

I expect that CatalystIT could sell you a bit of Postgres consulting if you have a budget for that sort of thing. Or the best route may be something like getting more memory for the server.

P.S. I know almost nothing about database and hardware tuning myself.
Average of ratings: -
In reply to Tim Hunt

Re: migrate postgres to mysql

by Martín Langhoff -
Sure! If someone wants our help to get DB tuning done with no hassles, we can help. Actually, we can help on either Pg or MySQL (but we are hardly the only ones wink ).

And on both cases, there's plenty of self help. I mean -- what we know we learn from the Pg hackers, and reading the same mailing lists that Google reads and perhaps bouncing ideas off people like we do here with Moodle smile No secret sauce, other than reading lots of open source.
Average of ratings: -
In reply to Martín Langhoff

Re: migrate postgres to mysql

by Anthony Borrow -
Picture of Core developers Picture of Plugin developers Picture of Testers
Martín - I will keep this in mind. Do you know of any Moodle specific comparison studies that may have done already? I may be in a position where I will be asked to setup and possibly administer a variety of Moodle sites and I would like to get things started on the right foot. If Postgres is really what admins are using and prefer then I will start doing some more reading on Postgres. Peace - Anthony
Average of ratings: -
In reply to Anthony Borrow

Re: migrate postgres to mysql

by Martín Langhoff -
I don't have anything Moodle-specific.

I'd google for "PostgreSQL tuning" -- I've even posted some good links last year in the perf forum. Pg 8.1 and 8.2 are noticeable faster than 7.4 and have more knowbs for tuning... etc.

And when reading the mailing lists Tom Lane is the man wink

(OTOH, if you have experienced DBAs on MySQL that know how to tune it, it may be easier)
Average of ratings: -
In reply to David Shields

Re: migrate postgres to mysql

by Derry Lyons -

Great discussion, guys, and thanks to David for bringing this point up.

Outside of the technical realm, my reason for moving to mysql is purely one of standards. I have a number of apps running in MS SQLServer and mysql, with Moodle being the only postgres app. I'd rather run two DB's rather than three, and migrating the others over to PG isn't an option.

An interesting observation that the majority of technical info for Moodle revolves around mysql instead of Postgres, which of course means more mental arithmetic when trying to apply the "logic" from examples.

So, assuming I've taken everyone's comments under advisement and still want to proceed migrating, what's the best route? I see my options as follows:

  1) Manually export each course from the Moodle-postgres and import into Moodle-mysql. Time-intensive, but relies on Moodle backup/restore to make sure life looks good.

  2) Use some manual PHP scripts to copy the tables and data over between the databases. Risky, since I'm not sure the table sequences and indexes will get set up properly, and since I'm not a DBA...

Thanks for sharing your opinions! smile Derry

Average of ratings: -
In reply to Derry Lyons

Re: migrate postgres to mysql

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I think I would try it at the database level. Find some format that Postgres exports that MySQL imports, possibly with a bit of massaging with regular expressions between the two.
Average of ratings: -
In reply to Tim Hunt

Re: migrate postgres to mysql

by Martín Langhoff -

Yes. I'd roughly

  • Install Moodle+MySQL
  • TRUNCATE all its tables
  • pg_dump --data-only --column-inserts ... > file.sql
  • mysql dbname < file.sql

And then fix up mdl_log_info which gets loaded with db-specific data -- the concats there will be wrong.

Average of ratings: -
In reply to Martín Langhoff

Re: migrate postgres to mysql

by Barbara Lawrence -

I've just tried this out and it has gone well. But I don't understand Martin's last comment about mdl_log_info.  Is this a table? There is no table with that name in my database (I'm on 1.9). If anyone could explain what I need to do here I'd be very grateful.

Average of ratings: -
In reply to Tim Hunt

Re: migrate postgres to mysql

by Derry Lyons -

Good thoughts!

Give my timeline, and the (relatively) few courses I had, it was easier to just migrate the courses. Starting with a fresh 1.8 install, too, made me feel a bit better.

I was struggling with the whole "sequence" concept between PG and msql... how to reset the new sequence/autoincrement numbers in msql once the import was done.

At any rate, we're migrated to 1.8 and ready to roll ~ thanks for the help! smile

Average of ratings: -