FAQ Migrate my Mysql database to PostgreSQL

From Dolibarr ERP CRM Wiki
Jump to navigation Jump to search


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".
  • Uncheck "Structure".
  • Check "Name columns".
  • Uncheck "Use extended INSERT".
  • Choose "No LOCK command around INSERT".
  • Choose "Compress None"
  • Then 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. Copy this file into /tmp directory.

Create a PostGreSQL database

  • Rename your conf.php file of Dolibarr into conf.php.mysql (file should be into /etc/dolibarr or htdocs/conf)
  • Create an empty file conf.php to replace the renamed one, with read and write permission for everybody.
  • Run Dolibarr. Because the conf.php file is empty, the install process will be suggested instead of logon page. Choose first installation and provide all informations of your postgresql database as data source. Run process until the end. About administrator account asked at end, choose same than the one existing into your MySql sessions.

Note: If you experience problem, you can go back to your Mysql database by restoring files renamed previsously into its original name conf.php.

Import dump file

  • Modify the generated file to set escape characters with correct characters:
cd /tmp
sed -e "s/\\\'/\'\'/g" mysqldump_mydatabase_version_date.sql > tmpfile.sql
sed -e 's/\\/\\\\/g' tmpfile.sql > mysqldump_readyforpgsql.sql
  • After conversion of file, import it into your empty postgresql database:
psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql -f mysqldump_readyforpgsql.sql > /tmp/resimport.log
  • Once import is finished, you must update all sequences to match imported data.
psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
# select rebuilt_sequences();
CTRL+D

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.

Retrieved from "https://wiki.dolibarr.org/index.php?title=FAQ_Migrate_my_Mysql_database_to_PostgreSQL&oldid=25837"