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.

Once upgrade is ok, you must clean database from old tables by running following request on your MySQL database: 

DROP TABLE llx_c_methode_commande_fournisseur;
DROP TABLE llx_c_source;
DROP TABLE llx_cond_reglement;
DROP TABLE llx_expedition_methode;
DROP TABLE llx_product_fournisseur;
ALTER TABLE llx_product_fournisseur_price DROP COLUMN fk_product_fournisseur;

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"
  • Into Compression area, 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.
  • Connect. You are now into an empty new instance of Dolibarr without old data.
  • Enable all modules that were enabled into old database (enable is enough to create modules specific tables, that's all what we want: having same structure than old database).
  • If you added "complementary fields" for some element, you must also defined them into this new instance (again, to have same fields into database than previous instance).
  • We will load old data at next step.

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 postgresql configuration to accept syntax from mysql file by setting the directive escape_string_warning to off into file postgresql.conf
escape_string_warning = off

Restart postgresql.

  • Disable all constraint checks to allow import. For this, connect to database and run the procedure provided by Dolibarr installation:
psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
> select dol_util_triggerall(false);
CTRL+D
  • Modify the generated export dump file to set escape characters with correct value for postgres:
cd /tmp
sed -e "s/\\\'/\'\'/g" mysqldump_mydatabase_version_date.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 >resimport.log 2>&1

Take a look at output log file resimport.log. If target structure was correctly matching mysql structure (no less tables nor fields), you should get no error. If you have error, please report them onto dolibarr forum. Note that you can run this import several time if you fixed error manually to your structure. It is safe because all dolibarr tables are protected by a "unique key". So if you do so, you will just get "duplicate record" errors, that you can ignore. Take care only on others.

  • Once import is finished, you must enable constraint check and update all sequences to match imported data.
psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
> select dol_util_triggerall(true);
> select dol_util_rebuild_sequences();
CTRL+D

You can also restore the postgresql.conf file to its initial value. 

escape_string_warning = on

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 to make program files to point to the new database
$dolibarr_main_db_host='name_or_ip_of_postgresql_server';
$dolibarr_main_db_port='port_of_postgresql_server';
$dolibarr_main_db_name='name_database_postgresql';
$dolibarr_main_db_user='login_database_postgresql';
$dolibarr_main_db_pass='password_database_postgresql';
$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=46368"