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 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.
Valid XHTML :: Valid CSS: :: Powered by WikkaWikiGet webERP Accounting & Business Management at SourceForge.net. Fast, secure and Free Open Source software downloads