Databases: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

Databases: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

by Iñigo Zendegi Urzelai -
Number of replies: 5
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Hi everybody,

We are actually running Moodle 2.2.4 under Oracle, and we want to migrate it to PostgreSQL.

We're trying to use the experimental dbtransfer tool (we don't know how to use it and haven't found any documentation about it; if there is one let us know about it please), but we had no success yet.

We think we should execute this tool in a Moodle under Postgres and the tool will import the data from the Moodle under Oracle, that's the way it works, doesn't it?

Anyway, if someone can tell us about how should we use this tool we'd be grateful, and if someone who has already done this kind of migration can tell us it would be great!

Thanks in advance.

Average of ratings: -
In reply to Iñigo Zendegi Urzelai

Re: Databases: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

by Dan Poltawski -

Hi Iñigo,

I think its a great move to move towards PostgreSQL from Oracle as it tends to get much more use (and therefore better testing, less bugs).

The dbtransfer tool uses the xmldb schema definitions in Moodle and takes data from one database to another. Its location at http://yourmoodle/admin/tool/dbtransfer and it simply takes destination database credentials. It uses your moodles currently configured credentials (in config.php) to connect to the current moodle database and transfer the data to the destination. If I remember rightly it will do some pre-checks before attempting to transfer, checking data matches the xmldb defintions.

The caveats I can think of are:

  • It depends on the XMLDB data definitions being defined and correct, anything outside of this definition will not be transfered. This is all good and correct for Moodle core, but you should pay special attention to third party plugins. We've required xmldb for 2.x plugins, but its possible you might have some data which is not covered by this.
  • [The 'experimental aspect'] I believe there is a risk that the ids in mdl_log entries are not matched up correctly. i.e. a forum view log entry might point to the wrong forum post after migration. The log data will still be in tact, but that additional metadata which allows you to click onto a log entry may be invalid.

Having said that, i've succesfully migrated 5 or 10 production sites from MySQL to Postgres using this tool and no have been encountered.

Average of ratings: Useful (1)
In reply to Dan Poltawski

Er: Re: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

by Iñigo Zendegi Urzelai -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Hi Dan,

Thanks for the explanation, it's been great help for us!

We've already made some steps forward using this tool:

- We've installed the php extension for Postgre in the current Moodle server in order to connect to the other server with the dbtransfer tool.

- We've run the dbtransfer tool and it goes fine with a recently installed Moodle (only has the data created on the installation).

- We've compared the DB objects of our Moodle instances (upgraded at least since 1.7.x) with a clean installation of Moodle and fix the differences between them.

- We're having some troubles with dbtransfer when we test it with real Moodle sites (there is some data it doesn't like), but it seems we could fix them.

We will test the dbtransfer migration of all our Moodle instances and if everything goes fine we'll jump into Postgre soon, so we'll tell you about the progress.

Thanks again for the help, Dan!



Average of ratings: -
In reply to Iñigo Zendegi Urzelai

Re: Er: Re: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

by Dan Poltawski -
You might be interested in this issue: MDL-34441
Average of ratings: -
In reply to Dan Poltawski

Er: Re: Er: Re: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

by Iñigo Zendegi Urzelai -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Hi again,

I'm proud to announce that we've already moved our Moodle instances from (Windows+Oracle) to (GNU/Linux+PostgreSQL) smile

Once we've standarized our DB schemas and fix some wrong-encoded data, the DBtransfer tool has made the database migration a really easy job to do.

I've seen in MDL-34441 that this tool will be visible soon, congratulations for a great work and thanks for making our lives a little bit easier!

Average of ratings: Useful (1)
In reply to Iñigo Zendegi Urzelai

Re: Er: Re: Er: Re: Moving Moodle from Oracle to PostgreSQL (dbtransfer tool)

by Dan Poltawski -
Great to hear Iñigo, i'm sure you'll do much better with Linux and PostgreSQL smile
Average of ratings: -