07 November 2025, 10:35

Hello

Is there a guide to convert a Postgres database to MySQL? Looking in the forums, it looks like most people are doing the other direction.

07 November 2025, 15:29

Hi Jawyei,

There is no code in Mahara to help with this currently.

If you are moving all the data of the Mahara site from Postgres to MySQL then I would suggest doing the following:

0) Make a backup of you database 

1) Export all the data out of the Postgres server into CSV files, using your database name for 'DB_NAME'

export SCHEMA=public
export DB_NAME=mahara
sudo -u postgres psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB_NAME | \
while read TBL; do \
  sudo -u postgres psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV HEADER" $DB_NAME > /tmp/postgres_data/$TBL.csv; \
done

2) Installing a fresh version of the same Mahara with MySQL as the database by editing the htdocs/config.php file and update the connection information to point to your MySQL database then visiting the site to kick off the installation process.

3) Clear out the data from the tables in MySQL server

Something like 

mysql -Nse 'show tables' $DB_NAME | while \
  read table; do mysql -e "truncate table $table" $DB_NAME; \
done

and then import the data in from the CSV files, eg to import the config data

LOAD DATA INFILE '/tmp/postgres_data/config.csv'
INTO TABLE config
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

You will need to import them in the order they appear in the various  */db/install.txt files starting with htdocs/lib/db/install.xml to get things that are foreign-keyed to work.

If you encounter errors on import then, hopefully, it will tell you what table file you need to do first.

Cheers

Robert