Created: Wednesday, 26 December 2012
The article isn't finished yet, so if anybody interesting in getting the final version of the article before I find time to complete it, feel free to let me know by using our contact us form. Thank you in advance and my apologizes for any inconveniences.
It is known pain when you decided to move into new version of OpenERP but realized that you cannot due to incompatibility of DB(s) between versions.
The simple approach in such a case is to sign a support contract with OpenERP company as they will take care of the migration script but if you don't want to do so, there is another way but it mighty worth the support contract in some cases.
Data transformation concept
It is clear that in some non trivial cases of data transformation you need to use ETL tools. You can easily find in Internet a batch of such tools, but in my case, I decided to look into open source and free of charge tools to try the whole idea before consider purchasing any commercial application for my needs.
After several attempts I choose Expressor Studio 3.0.1.
You choice might differ, so try to use the idea with the tool which suits you best.
To do plan:
- Select and install a ETL application;
- Setup database connection;
- Select several tables from the list of tables you are to migrate;
- Start the migration;
- Adjust sequences for your tables;
To do list in details
So the first step is to install Expressor Studio which is very simple task and I don't want to provide a lot of details with regard the activity.
The second step is to select what tables you are to migrate. IN general all tables from your old database are required your attention but you can be focused on a small subset as a try the concept. Once your algorithm works you can use it for the rest of data.
Let's start from migration of users. In such a case you are to migrate res_users table from your old database into the new one.
First of all, create a new clean database in your new OpenERp 6.x installation. When you are done, you can access the database by using PostgreSQL pgAdmin III - the native PostgreSQL administration tool.
You can setup a new connection by using a specific PostgreSQL user which has been created during your OpenERP installation.
If you don't know the password of such a user, you can change it in pgAdmin III or simply create a new one, you might use during the transformation activity.
Once you create a connection and test it, you can use the same data in Expressor Studio.
The final database looks like as it is shown in image on the right.
But in order to be able to work with your data in OpenERP we are to set sequence numbers in PostgreSQL DB.
We need to use pgAdmin III tool. Open your database and select the table you just migrated as follows: your_table_name\Schemas\public\Tables\res_users, and consider the name of the field which is automatically increases each time you add new record (primary key).
After that you are to open sequences for such a field as follows: your_database_name\Schemas\public\Sequences\res_users_id_seq, as you can notice, the name of the sequesnce is the table name + _field_name + _seq.
You got a dialog box in which you are to adjust current valus, which ought to be set as the last record id value in your table. In my table it is 10, so, just set the value in 10. If you don't correct such a value, in OpeneRP system you get a error message as follows:
duplicate key value violates unique constraint "res_partner_address_pkey".
So, you are about to get your OpenERP system up and running with youк old data but new face and functionality.