FAQ Migrate my Mysql database to PostgreSQL

Prerequisite

Your Dolibarr version must be at least version 3.1.1. 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 "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 sauvegarde

• 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 you 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.