Most recent edit on 2008-02-22 13:44:12 by PhilDaintree

Additions:

Converting a postgres database to mysql

Deletions:
Converting a postgres database to mysql

---

Oldest known version of this page was edited on 2007-06-29 21:53:21 by LindsayHarris []
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

1. Run the data through the format converter program pg2my.php, available from 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

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

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

1. Connect to the new database using

1. Create the tables required for webERP using the script modified in step 1 when setting up mysql thusly

1. Disable foreign key constraints, as they will be violated while loading in the database contents:

1. Load in the contents from the converted postgres output:

1. Enable foreign key constraints as they must now be:

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.