Migration from ADempiere 3.60LTS to iDempiere, best path?

[nilskm]

ADempiere 3.60LTS on Postgresql 9.2. 

Should I start with upgrading the DB from 9.2 to a more modern version or should I start with applying the migration scripts? 

And where can I find the migration scripts? They links on this page are broken: 

https://wiki.idempiere.org/en/Migration_Note

Best regards

Nils

[nilskm]

I've found the integration scripts. But has anyone written a guide on how to use them? 

idempiere/migration-historic at master · idempiere/idempiere · GitHub 

[Aziz Kayoueche]

Hi 

Having worked with Compiere and ADempiere in the past, and now working on iDempiere, I've noticed some key differences. While I am not a migration expert between these two ecosystems, I want to point out that the database structures are not identical.

For example, in ADempiere, storage is handled by a single table: M_Storage. On the other hand, in iDempiere, this has been split into two separate tables: M_StorageOnHand and M_StorageReservation.

I hope you find this information helpful.

[edwin_ang]

You are trying to do a very huge migration effort. 

Migrating from Adempiere to iDempiere then you have to upgrade multiple versions, with critical checkpoints at version 6, 8 and 11. 

If you have minor customization on your current Adempiere system then this will be easier.

But it will still be a major effort. If you don't have enough resources (time and money), then i would suggest you to just skip migration. Just start over in iDempiere version 11 or 12 and create a data warehouse to archive your existing Adempiere data. 

[Nils Kohlström]

Thank you for your replies! It'll probably end up with starting over completely with the latest version of iDempiere. This was mostly just a test to see how it would work out. 

I'm still interested in what's the best path to run the db scripts. To upgrade Postgresql first and then run the db scripts? Or maybe it's more complicated than that

--
You received this message because you are subscribed to a topic in the Google Groups "iDempiere" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/idempiere/R30tD1Tan6g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to idempiere+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/idempiere/0da927a5-80a3-4f8c-b80e-dae8d5f67dden%40googlegroups.com.

[Edwin Ang]

The tricky part is Adempiere and the old version of iDempiere are using an old version of PostgreSQL JDBC driver. We've done an upgrade project two years ago from version 2 to 11 and it was a massive effort. We've done it in three phases: 2 to 6.2, 6.2 to 8.2 and then 8.2 to 11. We need to upgrade PostgreSQL in several phases too. 

The older version of iDempiere has an ant script that can automate running the migration scripts. 

I would say if it is not a huge and critical installation, then just don't even try it. Very expensive and risky project.

To view this discussion visit https://groups.google.com/d/msgid/idempiere/CAEd1mAX7PFBtAMK_Wzjtx%2Bru7RsNMGWxnYXZoTUzNLX66BwwFQ%40mail.gmail.com.

[Nils Kohlström]

Thanks! I was kind of afraid of the necessity to upgrade PostgreSQL in steps too, and that it would be kind of hard to know where and how. But I have tested 3.60LTS with PostgreSQL v16. Just using the latest jdbc driver did the trick. I haven't tested everything but still a lot and I haven't experienced any trouble at all. Therefore I thought it could be easiest to upgrade to PostgreSQL v16 first and after that start testing the db scripts. I think I'll give it a try anyway :) 

To view this discussion visit https://groups.google.com/d/msgid/idempiere/CAP4vBhh79g1Nf6HEqY4rNMt1LvZNi%3DKieuBKw8cV-_QAeVL_-g%40mail.gmail.com.

[edwin_ang]

Give it a go then. That's why we are on open source right? We like the freedom to do it our way ;)

[nilskm]

A quick feedback on this matter if anyone's interested. 

I started of with the database, since if upgrading the db wouldn't work, then nothing would work. I took a copy of current db, upgraded it to postgres 16. Then I started with all the scripts and, well, it was  a real nightmare, there were errors just about everywhere. Most kind of logical but I realized that it would be a h*ll to go through it all and probably take me weeks. 

So I decided to "cheat". I've recently realized how extremely competent later versions of Claude AI is. I basically gave it direct access to a local copy of the database (ran it on a docker desktop), access to iDempiere 12 and ADempiere 360LTS code to give it context. Then I gave it detailed instructions to what to do and asked if it was doable. It gave the response that it was doable but a pretty massiv task. It worked through the scripts, made fixes, started over a couple of times, I restored new copies of db for it a couple of times. I had to upgrade from pro to max plan since it used up my limits pretty fast. Finally, after we worked "together" close to two days, it ended up with a result it was content with. I directly found a few errors, but it fixed them right away. 

I haven't tested everything but I'm cautiously optimistic that this actually worked! I'm pretty enthusiastic anyway! 

Now there's just this "small" task of going through 15 years of specific customizations for my company. I don't think it's possible to give that task directly to Claude since there's so much context around it. But I'm counting on a lot of help from Claude AI to do it. 

About the errors that came up. Actually they weren't that complicated, just a lot of them to handle. Missing translations, uuid problems with custom tables, renaming of columns, migrations I had already done manually (i.e. scripts tried to add things that already existed), a couple of functions I've made that needed fixing, references from m_pricelistversion to client that didn't exist, missing translation rows (_trl), views that needed to be dropped and recreated. It doesn't sound so bad when I'm going through it afterwards but one small error caused a bunch of errors, and finding and testing that would've been really tough to do manually. 

I'll keep you posted for 

[Heng Sin Low]

Perhaps you can share the instructions you give to Claude here ? Could be useful for many.

You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/idempiere/229ad72c-8012-447e-9dc0-de6022ddd619n%40googlegroups.com.

[nilskm]

Yes of course. I tried to create a github repository for the result. But I realized that it's to specific for my setup to be applied in general. It's likely better that anyone else who wants to do this will start from scratch with Claude AI. My first initial instruction to Claude AI is below. The instructions were modified during the process a bit, e.g in the beginning I'm asking it to only upgrade to 2.1 but I skipped that later and asked it to continue all the way to version 12 of iDempiere. 

I think that the prerequisites might be the most important thing so I list these first: 

1. Point Claude AI to the code base for 360LTS and also to codebase for v12. This gives it context for the SQL scripts. 

2. Give it its own DB to play with. I simply ran postgresql16 on docker and imported a backup. 

3. Give it access to the database, i.e. login. You need to have psql command installed also. 

These instructions are the ones I gave, some details redacted. It's actually pretty simple, just tell it what you want but start with giving it as much info you can. 

If I'd start all over I'd give it instruction to recreate dropped views in the end. I did that later in the process though. I'd also have given it access to a clean database for version 12 which it could compare with when needed. I did that later in the process when I ran into problems with some data that was missing. It could then compare data and see what was missing. 

--------------------- the instructions to Claude AI ------------------

The codebase contains release 12 of iDempiere from github. There's specifically one folder that's of interest right now, this one:
…migration-historic\360lts-i1.0a\postgresql And maybe this script.
…migration-historic\migrate_postgresql.sh The script is for linux so it can't be run since we're on windows, but that doesn't really matter. What it does is just run each sql script in a folder one by one.

Today I have a database in postgresql that's based on ADempiere 3.60LTS. All the sql scripts in the folder …migration-historic\360lts-i1.0a\postgresql are supposed to be run to upgrade to first version of iDempiere, version i1.0a. I've tried it but it runs into a lot of errors. Most of them can probably be ignored though and the cause could be that they've been executed earlier to implement some specific feature. I've tried to run through them using the script using the command like this on a linux server: "./migrate_postgresql.sh 360lts-i1.0a | psql -v ON_ERROR_STOP=1 -h localhost -p 5432 -U adempiere -d adempiere 2>&1 | tee 360lts-i1.0a.TEST.ls".

Database information:

url: localhost
port: 5432
username: *****
password: *****

The database is a copy of my current ADempiere 3.60LTS and it's upgraded to PostgreSQL v16. So, you can't harm anything. I can easily make a new copy for you if you want to rerun something.

I've installed "Command line tools", i.e psql commands and added C:\Program Files\PostgreSQL\16\bin to path.

So, this is what I want you to do:

1. Analyze all the SQL scripts in the folder 360lts-i1.0a\postgresql
2. Compare all commands, especially "INSERT " and check if they would fail.
3. Create a separate subfolder, e.g. "fixed" and put the scripts there where non-working SQL commands, e.g an INSERT is commented out. In other words fixed versions of the existing SQL scripts. If possible also with a comment to why it's failing.
4. After you've analyzed the scripts, you can run each one of them, in the style of the migrate_postgresql.sh script as I explained earlier. I don't think you need the script though since they can be run directly with psql commands. In other words, do it.

Is this doable? Is there anything else you need?

 ---------------- end of instructions -----------------

The first answer I got was this this below. 

---------

This is a substantial but doable challenge. Let me break it down:

1. Analyze all SQL scripts in the migration folder
2. Pre-validate them against your actual database to find what would fail
3. Create fixed versions with problematic statements commented out
4. Run the fixed scripts against your database

Let me start by understanding the scope - how many scripts there are, what the migration shell script does, and the structure of the SQL files.

------------------

Not sure what else I should comment. But feel free to ask me questions