=====Converting a postgres database to mysql=====

webERP version 3.06 drops support for postgres.   The following steps should convert your database from one to the other without pain.  I **strongly** suggest that you convert the database and run with the mysql server for a few days **before** upgrading to a newer version of webERP. This allows an easier rollback if problems show up.    Note this is not a theoretical discussion - I have followed these steps several times.

The recipe has three basic steps.   First is to dump the postgres database contents into an ASCII file,  second is to install the mysql server, and the third is to create the database tables and then insert the data from the postgres server.

Convert postgres data to mysql format
  1. Disable access to webERP until this process is completed to ensure no database changes are lost during the conversion.
  1. Dump the contents of your postgres database to a file //pg_data// using the command %%pg_dump -a -D fred > pg_data%%
  1. Run the data through the format converter program  pg2my.php,  available from [[http://www.lightbox.org/pg2mysql.php Lightbox Technologies]]. If there is no download available from there yet, you may collect it from [[http://www.bluegum.com/temp/pg2mysql-cmdline.tar.bz2]]  This program takes data from standard input and writes it to standard output,  so use it as %%pg2my.php < pg_data > my_input%%
  to leave the data in a file called //my_input//.

Setup mysql
  1. Edit the database initialisation script supplied with your current version of weberp,  namely  //.../sql/mysql/weberp-new.sql// and remove all the INSERT statements. The purpose here is to create the database tables without any content,  as it will all come from the postgres database.
  1.  Install the mysql server,   and set the //root// password.
  1. Use the //mysql// program to login as //root// and create the weberp user (the name which goes in the config.php file), using the command %%GRANT ALL PRIVILEGES ON *.* TO weberp_db_user@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;%%
  where //some_pass// is replaced with the password used in the config.php file.

Add data to mysql
  1. Create the new database,  for example //company// using the command %%CREATE DATABASE company;%%
  1. Connect to the new database using %%\u company%%
  1. Create the tables required for webERP using the script modified in step 1 when setting up mysql thusly %%\. weberp-new.sql%%
  1. Disable foreign key constraints, as they will be violated while loading in the database contents: %%SET FOREIGN_KEY_CHECKS = 0;%%
  1. Load in the contents from the converted postgres output:  %%\. my_input%%
  1. Enable foreign key constraints as they must now be: %%SET FOREIGN_KEY_CHECKS = 1;%%
  1. All done,  so quit from the //mysql// program

Now switch over your //config.php// file to use mysql rather than postgres,  and away you go.

Obviously you should do this on a test system before updating the live server.