FAQ Migrate my Mysql database to PostgreSQL
Prerequisite
Your Dolibarr version must be at least version 3.2.0. If not, first upgrade your Dolibarr keeping MySql.
Process
Export Mysql
- Go onto page Setup - Tools - Backup.
- Choose export method "MySql dump".
- Choose export compatibility "POSTGRESQL".
- Choose No LOCK command around INSERT.
- Choose "Compress None" and click on "Build backup".
- Go into directory of your Dolibarr data files (directory is defined into "Setup - System information - Dolibarr - All parameters" on line "Root directory for data files").
- Go into subdirectory admin/backup. You will find the dump of database you just build with previous step.
Create a PostGreSQL database
- Create an empty PostGreSQL database. This step is no describe into this page. Take a look at PostgreSql for this.
Import dump file
- Modify the generated file to set escape characters with correct characters:
sed "s/\\\'/\'\'/g" mysqldump_mydatabase_version_date.sql > mysqldump_readyforpgsql.sql
- Après avoir convertit le fichier, importer le sur votre base postgresql vierge:
psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql -f mysqldump_readyforpgsql.sql
Modify your Dolibarr setup file
- Open with a file editor, the file conf/conf.php that you can find into your Dolibarr root installation.
- Modify lines make programs to point to new database
$dolibarr_main_db_host='name_or_ip_of_postgresql_server';
$dolibarr_main_db_port='port_server_postgresql';
$dolibarr_main_db_name='name_database_postgresql';
$dolibarr_main_db_user='login_database_mysql';
$dolibarr_main_db_pass='password_database_mysql';
$dolibarr_main_db_type='pgsql';
Test
You should now have an application 100% working with all its data. Old MySql database can be closed.