Learn how to migrate Moodle from MySQL to Postgres. Our Moodle Support team is here to help you with your questions and concerns.

How to Migrate Moodle from MySQL to Postgres

Migrating a Moodle database from one type to another can be easily done via a database transfer tool.

This tool is an experimental feature that helps with the transfer of the Moodle site’s database seamlessly.

The database transfer tool can be found in our Moodle site under:

Settings > Site administration > Development > Experimental > Database migration

Alternatively, we can use the command line script located at `admin/tool/dbtransfer/cli/migrate.php`.

How the DBTransfer Tool Works

The dbtransfer tool uses the XMLDB schema definitions from Moodle and any installed plugins. It retrieves data from the current database and transfers it to the new database.

Potential Issues During Migration

• Schema Mismatches:

  If there are objects (columns, tables, etc.) in the current database that are not included in the XMLDB schema, or vice versa, the dbtransfer process will not execute.

  This can happen if Moodle has been upgraded from earlier versions or if some plugins haven’t been correctly uninstalled. Remove these objects before proceeding.
• Data Encoding Issues:

  Incorrectly encoded data in the current database can cause the transfer to crash. Identify and fix any problematic data before running the dbtransfer tool again.
• Timeouts:

  For large databases, the migration process may take a considerable amount of time, potentially causing timeouts in the database, PHP, or Apache. To avoid this, use the CLI variant of the tool.

How to Use the CLI Database Transfer Tool

1. Put the site into Maintenance Mode.
2. Then, disable cron to avoid any database writes during the migration process.
3. Next, run the migration script with the correct parameters for your new database:

   sudo -u www-data /usr/bin/php /path/to/moodle/admin/tool/dbtransfer/cli/migrate.php \
--dbtype='pgsql' \
--dbhost='x.x.x.x' \
--dbname='moodleDbName' \
--dbuser='moodleUser' \
--dbpass='***' \
--dbport=5432 \
--prefix='mdl_' \
--dbsocket='/var/run/postgresql'


   For more information, you can use the help command:

   sudo -u www-data /usr/bin/php /path/to/moodle/admin/tool/dbtransfer/cli/migrate.php --help

4. After migration, modify the Moodle configuration file to use the new database settings.
5. Next, verify that everything works correctly with the new database.
6. Then, disable Maintenance Mode and re-enable Cron. We can bring the site back online and resume regular cron jobs.

Example Execution

To migrate to a PostgreSQL database, ensure that the `php5-pgsql` package is installed:

$ sudo apt-get install php5-pgsql

Then run the migration script as shown above.

If you have a large database, the web interface may time out. In such cases, it’s better to use the command line tool to avoid interruptions.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

By following the above steps, we can replace MariaDB with MySQL in XAMPP, ensuring a smooth transition while preserving our data and configurations.

In brief, our Support Experts demonstrated how to migrate Moodle from MySQL to Postgres.