FAQ Migrate my Mysql database to PostgreSQL - Revision history

Udo.tamm at 14:14, 26 February 2020

2020-02-26T14:14:25Z

← Older revision		Revision as of 14:14, 26 February 2020
Line 8:		Line 8:
	[[Category:FAQ EN]]		[[Category:FAQ EN]]

	= Prerequisite=		=Prerequisite=
	Your Dolibarr version must be at least version 3.2.0.		Your Dolibarr version must be at least version 3.2.0. If not, first upgrade your Dolibarr keeping MySQL.
	If not, first upgrade your Dolibarr keeping ~~MySq~~. Once upgrade is ok, you must clean database from old tables by running following request on your ~~Mysql~~ database:
			Once upgrade is ok, you must clean database from old tables by running following request on your MySQL database:

	<source lang="sql">		<source lang="sql">
	DROP TABLE llx_c_methode_commande_fournisseur;		DROP TABLE llx_c_methode_commande_fournisseur;
Line 22:		Line 24:
	=Process=		=Process=

	===Export ~~Mysql~~===		===Export MySQL===
	* Go onto page '''Setup - Tools - Backup'''.
	* Choose export method "'''MySql dump'''".		*Go onto page '''Setup - Tools - Backup'''
	* Choose export compatibility "'''POSTGRESQL'''".		*Choose export method "'''MySql dump'''"
	* Uncheck "'''Structure'''".		*Choose export compatibility "'''POSTGRESQL'''"
	* Check "'''Name columns'''".		*Uncheck "'''Structure'''"
	* Uncheck "'''Use extended INSERT'''".		*Check "'''Name columns'''"
	* Choose "'''No LOCK command around INSERT'''".		*Uncheck "'''Use extended INSERT'''"
	* Into '''Compression''' area, choose "'''Compress None'''"		*Choose "'''No LOCK command around INSERT'''"
	* Then click on "'''Build backup'''".		*Into '''Compression''' area, choose "'''Compress None'''"
	* 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.		*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.

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

	<source lang="ini">		<source lang="ini">
	escape_string_warning = off		escape_string_warning = off
	</source>		</source>
	Restart postgresql.		Restart postgresql.
	* Disable all constraint checks to allow import. For this, connect to database and run the procedure provided by Dolibarr installation:
			*Disable all constraint checks to allow import. For this, connect to database and run the procedure provided by Dolibarr installation:

	<source lang="sql">		<source lang="sql">
	psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql		psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
Line 56:		Line 66:
	CTRL+D		CTRL+D
	</source>		</source>
	* Modify the generated export dump file to set escape characters with correct value for postgres:
			*Modify the generated export dump file to set escape characters with correct value for postgres:

	<source lang="bash">		<source lang="bash">
	cd /tmp		cd /tmp
	sed -e "s/\\\'/\'\'/g" mysqldump_mydatabase_version_date.sql > mysqldump_readyforpgsql.sql		sed -e "s/\\\'/\'\'/g" mysqldump_mydatabase_version_date.sql > mysqldump_readyforpgsql.sql
	</source>		</source>
	* After conversion of file, import it into your empty postgresql database:
			*After conversion of file, import it into your empty postgresql database:

	<source lang="bash">		<source lang="bash">
	psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql -f mysqldump_readyforpgsql.sql >resimport.log 2>&1		psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql -f mysqldump_readyforpgsql.sql >resimport.log 2>&1
Line 68:		Line 82:
	If you have error, please report them onto dolibarr forum.		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.		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.
			*Once import is finished, you must enable constraint check and update all sequences to match imported data.

	<source lang="sql">		<source lang="sql">
	psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql		psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
Line 81:		Line 97:

	===Modify your Dolibarr setup file===		===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		*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

	<source lang="ini">		<source lang="ini">
	$dolibarr_main_db_host='name_or_ip_of_postgresql_server';		$dolibarr_main_db_host='name_or_ip_of_postgresql_server';
Line 93:		Line 111:

	===Test===		===Test===
	You should now have an application 100% working with all its data.		You should now have an application 100% working with all its data. Old MySQL database can be closed.
	Old ~~MySql~~ database can be closed.

PolyglotBot: Import interlang links (links to translated versions of this page in other languages) from Multi Language Manager table.

2019-07-23T13:20:28Z

Import interlang links (links to translated versions of this page in other languages) from Multi Language Manager table.

← Older revision		Revision as of 13:20, 23 July 2019
Line 1:		Line 1:
			<!-- BEGIN origin interlang links -->
			<!-- You can edit this section but do NOT remove these comments
			Links below will be automatically replicated on translated pages by PolyglotBot -->
			[[fr:FAQ_Migrer_ma_base_Mysql_vers_PostgreSQL]]
			[[es:FAQ_Migrar_mi_base_de_datos_MySQL_hacia_PostgreSQL]]
			<!-- END interlang links -->

	[[Category:FAQ EN]]		[[Category:FAQ EN]]

Delcroip at 10:41, 15 January 2019

2019-01-15T10:41:12Z

← Older revision		Revision as of 10:41, 15 January 2019
Line 88:		Line 88:
	You should now have an application 100% working with all its data.		You should now have an application 100% working with all its data.
	Old MySql database can be closed.		Old MySql database can be closed.

	~~=Other solution: pgloader=~~
	~~In a single line of code the database can be migrated (tables are call dolibarr)~~
	~~https://github.com/dimitri/pgloader~~
	~~==Prerequisite==~~
	~~<source lang="bash">~~
	~~sudo apt-get install pgloader~~
	~~</source>~~

	~~==Process==~~
	~~<source lang="bash">~~
	~~pgloader mysql://USER:PASSWORD@IP_MYSQL:3306/dolibarr postgresql:///dolibarr~~
	~~</source>~~

Delcroip at 20:09, 14 January 2019

2019-01-14T20:09:42Z

← Older revision		Revision as of 20:09, 14 January 2019
Line 88:		Line 88:
	You should now have an application 100% working with all its data.		You should now have an application 100% working with all its data.
	Old MySql database can be closed.		Old MySql database can be closed.

			=Other solution: pgloader=
			In a single line of code the database can be migrated (tables are call dolibarr)
			https://github.com/dimitri/pgloader
			==Prerequisite==
			<source lang="bash">
			sudo apt-get install pgloader
			</source>

			==Process==
			<source lang="bash">
			pgloader mysql://USER:PASSWORD@IP_MYSQL:3306/dolibarr postgresql:///dolibarr
			</source>

Eldy: /* Modify your Dolibarr setup file */

2012-05-13T17:03:06Z

Modify your Dolibarr setup file

← Older revision		Revision as of 17:03, 13 May 2012
Line 80:		Line 80:
	$dolibarr_main_db_port='port_of_postgresql_server';		$dolibarr_main_db_port='port_of_postgresql_server';
	$dolibarr_main_db_name='name_database_postgresql';		$dolibarr_main_db_name='name_database_postgresql';
	$dolibarr_main_db_user='~~login_database_mysql~~';		$dolibarr_main_db_user='login_database_postgresql';
	$dolibarr_main_db_pass='~~password_database_mysql~~';		$dolibarr_main_db_pass='password_database_postgresql';
	$dolibarr_main_db_type='pgsql';		$dolibarr_main_db_type='pgsql';
	</source>		</source>

Eldy: /* Modify your Dolibarr setup file */

2012-05-13T17:02:50Z

Modify your Dolibarr setup file

← Older revision		Revision as of 17:02, 13 May 2012
Line 78:		Line 78:
	<source lang="ini">		<source lang="ini">
	$dolibarr_main_db_host='name_or_ip_of_postgresql_server';		$dolibarr_main_db_host='name_or_ip_of_postgresql_server';
	$dolibarr_main_db_port='~~port_server_postgresql~~';		$dolibarr_main_db_port='port_of_postgresql_server';
	$dolibarr_main_db_name='name_database_postgresql';		$dolibarr_main_db_name='name_database_postgresql';
	$dolibarr_main_db_user='login_database_mysql';		$dolibarr_main_db_user='login_database_mysql';

Eldy: /* Modify your Dolibarr setup file */

2012-05-13T17:02:30Z

Modify your Dolibarr setup file

← Older revision		Revision as of 17:02, 13 May 2012
Line 75:		Line 75:
	===Modify your Dolibarr setup file===		===Modify your Dolibarr setup file===
	* Open with a file editor, the file conf/conf.php that you can find into your Dolibarr root installation.		* 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		* Modify lines to make program files to point to the new database
	<source lang="ini">		<source lang="ini">
	$dolibarr_main_db_host='name_or_ip_of_postgresql_server';		$dolibarr_main_db_host='name_or_ip_of_postgresql_server';

Eldy: /* Import dump file */

2012-05-13T16:33:17Z

Import dump file

← Older revision		Revision as of 16:33, 13 May 2012
Line 62:		Line 62:
	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.		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.		* Once import is finished, you must enable constraint check and update all sequences to match imported data.
	<source lang="~~bash~~">		<source lang="sql">
	psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql		psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
	> select dol_util_triggerall(true);		> select dol_util_triggerall(true);

Eldy: /* Import dump file */

2012-05-13T16:33:07Z

Import dump file

← Older revision		Revision as of 16:33, 13 May 2012
Line 65:		Line 65:
	psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql		psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql
	> select dol_util_triggerall(true);		> select dol_util_triggerall(true);
	> select ~~rebuilt_sequences~~();		> select dol_util_rebuild_sequences();
	CTRL+D		CTRL+D
	</source>		</source>

Eldy: /* Import dump file */

2012-05-13T16:24:48Z

Import dump file

← Older revision		Revision as of 16:24, 13 May 2012
Line 58:		Line 58:
	psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql -f mysqldump_readyforpgsql.sql >resimport.log 2>&1		psql -h name_or_ip_of_postgresql_server -d name_database_postgresql -U login_database_mysql -f mysqldump_readyforpgsql.sql >resimport.log 2>&1
	</source>		</source>
			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.		* Once import is finished, you must enable constraint check and update all sequences to match imported data.
	<source lang="bash">		<source lang="bash">